Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Need an SQL for this requirement

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
pkmurali
Warnings : 1

Active User


Joined: 15 Dec 2005
Posts: 236

PostPosted: Fri Feb 08, 2008 1:26 pm    Post subject: Need an SQL for this requirement
Reply with quote

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
Back to top
View user's profile Send private message

pkmurali
Warnings : 1

Active User


Joined: 15 Dec 2005
Posts: 236

PostPosted: Fri Feb 08, 2008 5:13 pm    Post subject: Reply to: Need an SQL for this requirement
Reply with quote

Anybody please help on this.
Back to top
View user's profile Send private message
muthuvel

Active User


Joined: 29 Nov 2005
Posts: 217
Location: Chennai

PostPosted: Fri Feb 08, 2008 5:57 pm    Post subject: Reply to: Need an SQL for this requirement
Reply with quote

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
Back to top
View user's profile Send private message
pkmurali
Warnings : 1

Active User


Joined: 15 Dec 2005
Posts: 236

PostPosted: Fri Feb 08, 2008 6:13 pm    Post subject: Reply to: Need an SQL for this requirement
Reply with quote

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)
;
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Feb 08, 2008 6:16 pm    Post subject:
Reply with quote

pkmurali,
so, why did you ask?
Back to top
View user's profile Send private message
pkmurali
Warnings : 1

Active User


Joined: 15 Dec 2005
Posts: 236

PostPosted: Fri Feb 08, 2008 6:19 pm    Post subject: Reply to: Need an SQL for this requirement
Reply with quote

hi dbzTHEdinosauer,

i tried till now; just now i got the idea; that's why i posted tha answer.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Mainframe Developer requirement in Ku... capricorn Mainframe Jobs 0 Sun Oct 23, 2016 3:49 pm
No new posts Cards mainframe developer requirement... capricorn Mainframe Jobs 0 Sun Sep 11, 2016 9:57 am
This topic is locked: you cannot edit posts or make replies. Urgent Requirement in CTS Chennai. Mahi_e Mainframe Jobs 0 Wed Feb 17, 2016 3:52 pm
This topic is locked: you cannot edit posts or make replies. IBM SYSTEM PROGRAMMER REQUIREMENT mjadwani2785 Mainframe Jobs 0 Thu Nov 05, 2015 5:41 pm
No new posts CA7 Advance command to check the run ... thesumitk CA Products 1 Thu Sep 25, 2014 4:10 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us