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
 

 

DB2 MaxRexx query returning extra records

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

New User


Joined: 23 Jul 2010
Posts: 33
Location: Europe

PostPosted: Wed Feb 22, 2012 6:29 pm    Post subject: DB2 MaxRexx query returning extra records
Reply with quote

The following query retrieves the records below:
Code:

SELECT DISTINCT(A.REQUEST_ID),
A.TAPE_SERIES,
A.DEPEND_REQUEST_ID,
A.DEPEND_REASON_CD,
A.REQUEST_DEPEND_CD,
A.IDIRECT_REQUEST_ID,
B.REQUEST_LEVEL_CD,
C.REQUEST_LEVEL_CD,
E.STAR_PRODUCT_NM,
E.STAR_PROBLEM_NBR
FROM PTCUTPRD.CUT_RECUT_DEPEND   A,
PTCUTPRD.CUT_REQUEST_STATUS B,
PTCUTPRD.CUT_REQUEST_STATUS C,
PTCUTPRD.CUT_APAR_TAPE      D 
LEFT OUTER JOIN PTCUTPRD.CUT_APAR_MSTRINFO E
ON E.APAR_ID = D.APAR_ID
WHERE  A.TAPE_SERIES = B.TAPE_SERIES
AND A.TAPE_SERIES = D.TAPE_SERIES   
AND D.APAR_ID     = E.APAR_ID   
AND A.REQUEST_ID  = B.REQUEST_ID   
AND A.REQUEST_ID  = D.REQUEST_ID   
AND A.TAPE_SERIES = C.TAPE_SERIES   
AND A.DEPEND_REQUEST_ID = C.REQUEST_ID   
AND A.TAPE_SERIES ='D7Y120'   
AND B.REQUEST_LEVEL_CD IN ('DEV','INT','QA','QIN')   
AND C.REQUEST_LEVEL_CD IN ('DEV','INT','QA','QIN')


12 ROWS ARE RETRIEVED
But I only want to retrieve one of each of the following 2 records in bold. Both are being retrieved because of the A.DEPEND_REASON_CD clause in the SQL but I need this to read in these values. But I don't need them for displaying on a report, but obviously there appears to be dupes then on the report.

I could do an inner join of the same SQL without the A.DEPEND_REASON_CD specified but this is very messy. Any better way anyone?

rows retrieved are:
REQUEST_ID TAPE_SERIES DEPEND_REQUEST_ID DEPEND_REASON_CD

R00006 D7Y120 R00019 B
R00006 D7Y120 R00019 P

R00014 D7Y120 R00027 I

R00019 D7Y120 R00006 B
R00019 D7Y120 R00006 P

R00024 D7Y120 R00025 P
R00024 D7Y120 R00027 P
R00025 D7Y120 R00024 P
R00025 D7Y120 R00027 P
R00027 D7Y120 R00014 I
R00027 D7Y120 R00024 P
R00027 D7Y120 R00025 P
Back to top
View user's profile Send private message

chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Wed Feb 22, 2012 8:07 pm    Post subject:
Reply with quote

Hi,

Do you want any particular value for A.DEPEND_REASON_CD or any value will do?

If any value is fine with you and you are using DB2V9 then change the replace A.DEPEND_REASON_CD in select clause with following query

Code:
A.DEPEND_REQUEST_ID,
(SELECT A1.DEPEND_REASON_CD
     FROM PTCUTPRD.CUT_RECUT_DEPEND   A1
   WHERE A.REQUEST_ID  = A1.REQUEST_ID
FETCH FIRST 1 ROWS ONLY),
A.REQUEST_DEPEND_CD
Kindly let us know if it works for you

Regards,
Chandan
Back to top
View user's profile Send private message
PokerGuru

New User


Joined: 23 Jul 2010
Posts: 33
Location: Europe

PostPosted: Wed Feb 22, 2012 8:22 pm    Post subject:
Reply with quote

That worked.

Thank you
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
No new posts Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am


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