View previous topic :: View next topic
|
Author |
Message |
Binaya
New User
Joined: 03 Jul 2007 Posts: 77 Location: Hyderabad
|
|
|
|
Hi All,
I need some help on a SQL query.
My table structure is
Code: |
PSDO CDS
CUST CDS BK REC EXPY RPT CDS
ID COD SECTR CLS DT DT PRTY
----- ---- ----- --- ---------- ---------- ------
257 BACC C D - 1989-11-14 3
266 AUG5 C D - 2006-11-24 6
425 AUG7 B D - 2000-10-20 4
427 AUG6 B D - 2005-04-12 5
427 BACC B D - 2005-04-07 3
450 AUG6 B D - 2002-10-04 5
450 BACC B D - 2002-10-03 3
|
I have written a query like
SELECT COUNT(CDS_REC_CLS),MAX(RPT_DT)
FROM PDB2.VBSCODM
WHERE PSDO_CUST_ID =450
AND CDS_REC_CLS = 'D'
The out put is :---> 2 2002-10-04
But I also want the CDS-CODE for the latest date (2002-10-04) only to be fetched in this same query which should be AUG6.
I.e My output shd be:----> 2 2002-10-04 AUG6
Regards, |
|
Back to top |
|
|
Srihari Gonugunta
Active User
Joined: 14 Sep 2007 Posts: 295 Location: Singapore
|
|
|
|
Binaya,
SELECT COUNT(CDS_REC_CLS),MAX(RPT_DT),(SELECT CDS-CODE
FROM PDB2.VBSCODM WHERE PSDO_CUST_ID =450
AND CDS_REC_CLS = 'D' AND RPT_DT=(SELECT MAX(RPT_DT) FROM
ROM PDB2.VBSCODM
WHERE PSDO_CUST_ID =450
AND CDS_REC_CLS = 'D')) AS CDS-CODE
FROM PDB2.VBSCODM
WHERE PSDO_CUST_ID =450
AND CDS_REC_CLS = 'D'
I don't think it is an efficient SQL, but this can be used for your requirement I believe.. |
|
Back to top |
|
|
Binaya
New User
Joined: 03 Jul 2007 Posts: 77 Location: Hyderabad
|
|
|
|
Hi Srihari,
The SQL won't work as on the below part WHERE clause can't be applied.
(SELECT CDS-CODE
FROM PDB2.VBSCODM WHERE PSDO_CUST_ID =450
AND CDS_REC_CLS = 'D' AND RPT_DT=(SELECT MAX(RPT_DT) FROM
ROM PDB2.VBSCODM
WHERE PSDO_CUST_ID =450
AND CDS_REC_CLS = 'D')) AS CDS-CODE
However I am getting the separately i.e
SELECT COUNT(CDS_REC_CLS),MAX(RPT_DT)
FROM PDB2.VBSCODM
WHERE PSDO_CUST_ID =450
AND CDS_REC_CLS = 'D'
and
(SELECT CDS-CODE
FROM PDB2.VBSCODM WHERE PSDO_CUST_ID =450
AND CDS_REC_CLS = 'D' AND RPT_DT=(SELECT MAX(RPT_DT) FROM
ROM PDB2.VBSCODM
WHERE PSDO_CUST_ID =450
AND CDS_REC_CLS = 'D')) AS CDS-CODE
Regards, |
|
Back to top |
|
|
shankar.v
Active User
Joined: 25 Jun 2007 Posts: 196 Location: Bangalore
|
|
|
|
Binaya,
Please check with the following query for your requirement.
Code: |
SELECT A.COUNT
,A.MAX
,B.CDS-CODE
FROM
(SELECT COUNT(CDS_REC_CLS) AS COUNT
,MAX(RPT_DT) AS MAX
FROM PDB2.VBSCODM
WHERE PSDO_CUST_ID=450
AND CDS_REC_CLS='D') AS A,
PDB2.VBSCODM B
WHERE B.PSDO_CUST_ID=450
AND B.CDS_REC_CLS='D'
AND A.MAX=B.RPT_DT |
|
|
Back to top |
|
|
|