Stored Procedure Problem

[umapathi72]

Hi All Experts
I need solution for 2 problems

1) I have to figure out the latest record from the table, i.e. If a service number is having multiple records with same category and code, but in different dates. I need to access latest record. (If the records are in JAN/FEB/MAR, then I need March record. I tried it with MAX, but not getting latest record) . Please help me

2) I need to write a stored procedure which Inserts/Updates multiple tables, in multiple conditions i.e. (Based on clients business unit,customer,service,bill,billhistory etc).Help is appreciated

 

[olded]

First, I hope you realize this is the Informix 4GL forum. If your proposed stored proc isn't called by 4GL, you probably are better off in the Informix Dynamic Server forum.

As far as your first requirement is concerned, MAX what? date? If it is a date date type, then, no, you may not get the right record if more than one record exists for a given date. You probably want to use a datetime data type.

For your second question, your requirements are way too general. Your best bet is to post your work and tell what your specific problem is.

 

[umapathi72]

Hi olded,
here is my problems in breif
I think I am into correct forum,

1) SELECT a.service_type,
a.desired_due_date,
a.client_no,
a.service_no,
a.req_part_id,
a.domain_1,
a.bbcarelevel_id,
c.request_id

INTO v_service_type,
v_desired_due_date,
v_client_no,
v_service_no,
v_req_part_id,
v_domain_1,
v_bbcarelevel_id,
v_radius_req_id
FROM
reqbtbb a,
reqradius c

WHERE a.provision_type = 'MGK'
AND a.req_status = 'C'
AND a.mac_expiry_date <= TODAY
AND c.action_type = 'XT'
AND c.client_no = a.client_no
AND c.service_no = a.service_no
AND a.create_dtim = (SELECT MAX(create_dtim) FROM reqbtbb d WHERE d.service_no = a.service_no AND d.client_no = a.client_no )

this query is not returning latest record


2) For second question
Select a.business_type,c.brun_no
From client a, clntbill b,billhist c, clntpopt d, clntrchg e

where a.client_no = b.client_no
and a.client_no = c.client_no
and a.client_no = d.client_no
and a.client_no = e.client_no

and a.business_unit='RESI'
and a.code_12='PO'
and a.business_type='P'
and b.email=''
and d.pay_option='CH'
and a.datetime_modified = e.datetime_modified

based on the above condition, I need to update CLNTRCHG table, with new value,.. for this I have been told to write stored procedure which contains TEMPARARY tables in Informix,with the help of temp tables, I need to updated the data /...............
I am not that good at stored procedures...,,,


Please help me

Thanks in advance

Umapathi

 

[PHV]

this query is not returning latest record
Which row is returned ?
I'd try this:
...
AND a.create_dtim = (SELECT MAX(create_dtim) FROM reqbtbb d WHERE d.service_no = a.service_no AND d.client_no = a.client_no
AND d.provision_type = 'MGK' AND d.req_status = 'C' AND d.mac_expiry_date <= TODAY)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886