PokerGuru
New User
Joined: 23 Jul 2010 Posts: 33 Location: Europe
|
|
|
|
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 |
|
chandan.inst
Active User
Joined: 03 Nov 2005 Posts: 275 Location: Mumbai
|
|
|
|
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 |
|