I have a Client Address (CAD) Table with columns :
Client_no, Ad_type_cd,Str1,Str2,St_cd,Pin_cd ..... Suppose I have the AD_type values as 'HMM','OTH','RES' and so on ..
NOTE : Client number is not unique. For one client number I can get all the address type rows ....
I have to sort the rows with the first preference as hmm for a Client .Even If hmm is not found then which ever comes first should be taken and that particular row shud be updated (multiple columns but not client_no, Ad_type )
Select Client_no, Str1,Str2 , (Case Ad_type When 'HMM' 0 else 1 End) as Sort1 from CAD Where client _no = Ws:client_no Order by Sort1
Fetch First Row only ;
If Sqlcode = 0
I am not sure how to write the update para for the row selected in the above statement.
Please Suggest me If I can do the same with in a Single Query ( may be with subquery or Where Exists .. What ever )
Thanks in Advance .....
in the assumption that Client_no,ad_type is a unique key :
update CAD A
where A.Client_no = :Ws-client-no
and A.Ad_type in
Select B.ad_type from CAD B Where B.client _no = A.Client_no
Order by Case When B.Ad_type = 'HMM' then 0 else 1 End
Fetch First Row only