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.

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

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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?