IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

SQL Help Needed..


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Binaya

New User


Joined: 03 Jul 2007
Posts: 77
Location: Hyderabad

PostPosted: Fri Dec 07, 2007 3:23 pm
Reply with quote

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
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Fri Dec 07, 2007 5:11 pm
Reply with quote

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

New User


Joined: 03 Jul 2007
Posts: 77
Location: Hyderabad

PostPosted: Fri Dec 07, 2007 6:04 pm
Reply with quote

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
View user's profile Send private message
shankar.v

Active User


Joined: 25 Jun 2007
Posts: 196
Location: Bangalore

PostPosted: Fri Dec 07, 2007 6:20 pm
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Mainframe Programmer with CICS Skill... Mainframe Jobs 0
No new posts Help needed to assemble IMS sample co... ABENDS & Debugging 4
No new posts RABBIT HOLE NEEDED - "Live"... All Other Mainframe Topics 0
No new posts Mainframe profiles needed @ Cognizant Mainframe Jobs 0
No new posts COBOL Student learning COBOL File Han... COBOL Programming 3
Search our Forums:

Back to Top