Need an SQL for this requirement

pkmurali · Active User Joined: 15 Dec 2005 Posts: 271

Uniq no Statucode squence no
--------- ----------- -----------------
67487930 A 1
67487930 A 2
67487930 A 3
67487930 A 4
67487930 PO 2
67487930 PO 3

67487931 PO 3
67487931 A 1

I need the PO records for which it should not have Active(A) records.

In the above example for uniq no 6748930 has two PO records for which Uniq no and Sequence no are same as that of corresponding A record , so it should not come in the output file
Where as 67487931 has having one PO record for which uniq no is same but corresponing sequece no is different , so it should come in the output file
Let say Table is xyz , Need sql query for this

pkmurali · Active User Joined: 15 Dec 2005 Posts: 271

Anybody please help on this.

muthuvel · Posted: Fri Feb 08, 2008 5:57 pm

This is just a draft.Please make a try,

Have two cursors Cursor A and Cursor B.

In Cursor A
Select *
from table xyz
where status code ='A'
order by uniqueno,status code,sequence key

In Cursor B
Select *
from table xyz
where status code ='PO'
order by uniqueno,status code,sequence key.

There are two keys key1(uniqueno,sequence no from cursor a) and key2(uniqueno,sequence no from cursor b)

Program Logic
Perform until end-of-cursor a or end-of -cursorb
Open Cursor A,Cursor B
Fetch Cursor A,Cursor B
If key1 = key2
fetch cursor a and fetch cursor b
If key 1 < key2
fetch cursor a
If key 1 > key2
write o/p
fetch cursor b
end-if
end-perform
If eof-cursor a and not eof-cursor b
perform until eof-cursor b
write o/p
fetch cursor b
end-perform

pkmurali · Active User Joined: 15 Dec 2005 Posts: 271

hi muthuvel,

thanks for the suggestion. but i need a sql. here the following sql does the job

SELECT *
FROM AMRWTBT.V1CAMAPU A
WHERE A.AC_PRS_ROW_STA_CD = 'PO'
AND A.ACP_NM_DPD_SQ_NO
NOT IN
(SELECT B.ACP_NM_DPD_SQ_NO
FROM AMRWTBT.V1CAMAPU B
WHERE B.AC_PRS_ROW_STA_CD = 'A'
AND A.AC_UNQ_ID = B.AC_UNQ_ID)
;

dbzTHEdinosauer · Posted: Fri Feb 08, 2008 6:16 pm

pkmurali,
so, why did you ask?

pkmurali · Active User Joined: 15 Dec 2005 Posts: 271

hi dbzTHEdinosauer,

i tried till now; just now i got the idea; that's why i posted tha answer.