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
 

 

SQL Help Needed..

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: SQL Help Needed..
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    Post subject:
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    Post subject:
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    Post subject:
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    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 Help Needed with View Direct shailesh_do CA Products 2 Wed Jul 13, 2016 10:39 am
No new posts Information needed on using the COBAN... venugopal.dondla COBOL Programming 5 Thu Oct 08, 2015 3:34 pm
No new posts Help needed in Conditional statement ... vinu78 DB2 5 Tue Sep 15, 2015 10:14 am
No new posts Help needed to locate the changes wh... thesumitk TSO/ISPF 6 Fri Jun 12, 2015 6:21 pm
No new posts Sort help needed guptae DFSORT/ICETOOL 5 Tue Jul 01, 2014 11:34 am


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