Stored Procedure Problem
Stored Procedure Problem
(OP)
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
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
RE: Stored Procedure Problem
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.
RE: Stored Procedure Problem
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
RE: Stored Procedure Problem
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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?