Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
SQL to retrieve latest Eff date and then

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

Active User


Joined: 02 Oct 2008
Posts: 159
Location: India

PostPosted: Tue Apr 30, 2019 3:45 pm    Post subject: SQL to retrieve latest Eff date and then
Reply with quote

Hi Team,

Can you please provide me SQL query idea for the following requirement

- Identify the latest Effective date for a given ID number from Table_ A
- Then check in the same table to extract row based on this same ID number with latest Effective date and FLAG_CODE 'A'

The method I have adopted is

SELECT MAX(EFF_DT) INTO :EFF-DT FROM TABLE_A WHERE ID_NUM = :DCLGEN-ID-NUM

On successful retrieval of a row, the below mentioned SQL query is executed

SELECT * FROM TABLE_A WHERE ID_NUM = :DCLGEN-ID-NUM
AND EFF_DT = :EFF-DT
AND FLAG_CODE = 'A'

Can you please help me with single SQL query for achieving this requirement

Thanks
Vinu
Back to top
View user's profile Send private message

expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8782
Location: Welsh Wales

PostPosted: Tue Apr 30, 2019 3:56 pm    Post subject:
Reply with quote

Have you taken a look at sub queries. I have used then once or twice before.
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 159
Location: India

PostPosted: Tue Apr 30, 2019 4:34 pm    Post subject:
Reply with quote

Hi Expat,

I did the subquery like below

SELECT * FROM TABLE_A
WHERE ID_NUM = :DCLGEN-ID-NUM
AND FLAG_CODE = 'A'
AND EFF_DT = (SELECT MAX(EFF_DT) INTO :EFF-DT FROM TABLE_A
WHERE ID_NUM = :DCLGEN-ID-NUM)

I am not sure whether this is correct.

Thanks
Vinu
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8782
Location: Welsh Wales

PostPosted: Tue Apr 30, 2019 4:37 pm    Post subject:
Reply with quote

Swift example ...................... which works for me using SAS icon_biggrin.gif

It gets the MAX Round for each ID and then selects records accordingly, so hopefully not too difficult for you to adapt using your own names.
Code:

   Create table Work.Wombat as
      Select   Main.*
      From   
            Dataset.Name      as Main
         ,
            (
               Select ID
                  ,   Max(Round)      as Mround
               From
                     Dataset.Name
               Group by
                     ID
                     )            as Sub
            Where   Main.ID = Sub.ID
            and     Main.Round   = Sub.Mround;
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2151
Location: NY,USA

PostPosted: Tue Apr 30, 2019 5:36 pm    Post subject:
Reply with quote

You need correlated query or look for EXISTS.
You need double check , if FLAG_CODE is required to consider while finding the max date else the results are wrong.
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 159
Location: India

PostPosted: Tue Apr 30, 2019 5:55 pm    Post subject:
Reply with quote

Hi All,

Only ID number is required while considering latest eff date.
I have tried testing below SQL query and it seems to be working

SELECT * FROM TABLE_A
WHERE ID_NUM = :DCLGEN-ID-NUM
AND FLAG_CODE = 'A'
AND EFF_DT = (SELECT MAX(EFF_DT) INTO :EFF-DT FROM TABLE_A
WHERE ID_NUM = :DCLGEN-ID-NUM)


This query first gets me latest Eff date for the given ID num, then with that latest eff date it checks whether for the same ID number and Flag _code is 'A'

Whether we are seeing any loop holes here.


Thanks
Vinu
Back to top
View user's profile Send private message
sergeyken

Active User


Joined: 29 Apr 2008
Posts: 440
Location: Maryland

PostPosted: Wed May 01, 2019 1:08 am    Post subject:
Reply with quote

vinu78 wrote:
Hi Expat,

I did the subquery like below
Code:

SELECT * FROM TABLE_A
WHERE ID_NUM = :DCLGEN-ID-NUM
    AND FLAG_CODE = 'A'
    AND EFF_DT = (SELECT MAX(EFF_DT) INTO :EFF-DT FROM TABLE_A
                            WHERE ID_NUM = :DCLGEN-ID-NUM)

I am not sure whether this is correct.

Thanks
Vinu

There is no need trying to save the result of SELECT MAX into any variable in your subquery. Its value is used as result of the subquery itself (as if it was a function call!).

I guess, this idea must be obvious even without reading SQL manuals, isn't it?

P.S.
Use the code tags in your examples. Otherwise 50% of readers would ignore your questions.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2151
Location: NY,USA

PostPosted: Wed May 01, 2019 9:00 am    Post subject:
Reply with quote

Code:
SELECT A.* FROM TABLE_A A
WHERE A.ID_NUM = :DCLGEN-ID-NUM
AND A.FLAG_CODE = 'A'
AND A EFF_DT = (SELECT MAX(B.EFF_DT) FROM TABLE_A B
                        WHERE A.ID_NUM =B.ID_NUM)
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 159
Location: India

PostPosted: Wed May 01, 2019 1:12 pm    Post subject:
Reply with quote

Thanks very much. This works.

Regards
Vinu
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 how to add date & timestamp to th... dharmaraok DFSORT/ICETOOL 5 Thu Apr 18, 2019 2:45 pm
No new posts HEADER1 with Date arithmetic Martin-2000 DFSORT/ICETOOL 4 Thu Feb 21, 2019 3:24 pm
No new posts Relational operator compare date rang... maxsubrat CLIST & REXX 14 Wed Feb 06, 2019 6:19 pm
No new posts REXX identify the last/latest created... sivatechdrive CLIST & REXX 6 Thu Jan 24, 2019 12:15 pm
No new posts Regression testing of date format con... prino CLIST & REXX 0 Fri Jan 11, 2019 5:39 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us