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

SQL to retrieve latest Eff date and then


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

Active User


Joined: 02 Oct 2008
Posts: 179
Location: India

PostPosted: Tue Apr 30, 2019 3:45 pm
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: 8794
Location: Welsh Wales

PostPosted: Tue Apr 30, 2019 3:56 pm
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: 179
Location: India

PostPosted: Tue Apr 30, 2019 4:34 pm
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: 8794
Location: Welsh Wales

PostPosted: Tue Apr 30, 2019 4:37 pm
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

Global Moderator


Joined: 21 Sep 2010
Posts: 2572
Location: NYC,USA

PostPosted: Tue Apr 30, 2019 5:36 pm
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: 179
Location: India

PostPosted: Tue Apr 30, 2019 5:55 pm
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 Member


Joined: 29 Apr 2008
Posts: 932
Location: Maryland

PostPosted: Wed May 01, 2019 1:08 am
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

Global Moderator


Joined: 21 Sep 2010
Posts: 2572
Location: NYC,USA

PostPosted: Wed May 01, 2019 9:00 am
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: 179
Location: India

PostPosted: Wed May 01, 2019 1:12 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Date conversion using SORT SYNCSORT 4
No new posts Date Conversion Y2T to Y2W SYNCSORT 8
No new posts Date format conversion DFSORT/ICETOOL 4
No new posts Hex Value - vb file replace old date ... DFSORT/ICETOOL 14
No new posts Required Date Format in Include Sort ... DFSORT/ICETOOL 6
Search our Forums:

Back to Top