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

Query to skip Max time-stamp and fetch other


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

New User


Joined: 10 Jun 2015
Posts: 51
Location: India

PostPosted: Sun Jan 28, 2018 1:09 am
Reply with quote

Hi All,

My cobol program is fetching Registration_id based on some conditions.For below condition i need suggestions as i am out of ideas

Table A has Registration_id and Client_No.
Table B has Last_Login_Email,Last_Login_Mobile and Last_Login_OFX timestamp of Registration_id.


Requirement:-
Get the Registration_id which are tied to multiple Client_No.Now out of these Registration_id skip the Registration_id with maximum Last_Login_Email or Last_Login_Mobile or Last_Login_OFX timestamp
and fetch the rest of Registration_id.


I have writtem below query for 1st part of condition but dont know how to write for 2nd part of condition.Please suggest.

Code:
Select X.Registration_id from TableA X,TableA Y Where
       X.Registration_id  <> Y.Registration_id and X.Client_No = Y.Client_No;
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Sun Jan 28, 2018 1:50 am
Reply with quote

please reread your post and HONESTLY tell
if somebody who knows nothing about Your environment will be able to understand what You are asking
Back to top
View user's profile Send private message
vickey_dw

New User


Joined: 10 Jun 2015
Posts: 51
Location: India

PostPosted: Sun Jan 28, 2018 10:57 am
Reply with quote

Hi Enrico

Sorry if my post is confusing.
But which part you did not understand i have given which table has registration_id and which table has time-stamps.

For my scenario i mentioned above i need to skip one registration_id (out of all for which Client_no is same) and fetch others.

For Example there are 5 below registration_id for which client no is 123456

Code:
Registration_id1
Registration_id2
Registration_id3
Registration_id4
Registration_id5


Then out of these above Registration_id's i have to skip the Registration_id
which has maximum Last_Login_Email,Last_Login_Mobile or Last_Login_OFX time-stamp in Table B and fetch other registration_id.

Please let me know for doubts in requirement.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Sun Jan 28, 2018 6:06 pm
Reply with quote

Sample source data and sample output would help.

But why not select the timestamp as well, order your data by timestamp descending and let your program decide whether it needs that data or not.
simpler to code, less strain on db2, quicker to execute.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Sun Jan 28, 2018 6:20 pm
Reply with quote

Quote:
But why not select the timestamp as well,


from the confusing descriptions there are three timestamps in each row

also I would like to see the result of
Code:
Select X.Registration_id from TableA X,TableA Y Where
       X.Registration_id  <> Y.Registration_id and X.Client_No = Y.Client_No;
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1353
Location: Israel

PostPosted: Sun Jan 28, 2018 10:20 pm
Reply with quote

vickey_dw wrote:
For Example there are 5 below registration_id for which client no is 123456

Code:
Registration_id1
Registration_id2
Registration_id3
Registration_id4
Registration_id5


Then out of these above Registration_id's i have to skip the Registration_id
which has maximum Last_Login_Email,Last_Login_Mobile or Last_Login_OFX time-stamp in Table B and fetch other registration_id.

A bit confusing, but let's try to translate:
  1. In your query you'll have to add a condition:
    Code:
    AND dates from current Registration row are all smaller than the highest date in all Registration rows for current client

  2. "dates from current Registration row are all" can easily be translated by:
    Code:
    AND MAX(Last_Login_Email,Last_Login_Mobile,Last_Login_OFX) smaller than highest date in all Registration rows for current client

  3. Of course "smaller than" can be reduced to "<":
    Code:
    AND MAX(Last_Login_Email,Last_Login_Mobile,Last_Login_OFX) < highest date in all Registration rows for current client

  4. The last part is tricky because of the 3 dates. Let's build a separate SELECT for it. This will bring many rows, each with the highest of the 3 dates:
    Code:
    SELECT MAX(Last_Login_Email,Last_Login_Mobile,Last_Login_OFX) FROM TABLEB B WHERE B.Registration_id = <all registrations for client>

  5. And this will bring the highest date from all these rows:
    Code:
    SELECT MAX(MAXDATE) FROM (SELECT MAX(Last_Login_Email,Last_Login_Mobile,Last_Login_OFX) AS MAXDATE FROM TABLEB B WHERE B.Registration_id = <all registrations for client>)

The whole condition has become:
Code:
AND MAX(Last_Login_Email,Last_Login_Mobile,Last_Login_OFX) < SELECT MAX(MAXDATE) FROM (SELECT MAX(Last_Login_Email,Last_Login_Mobile,Last_Login_OFX) AS MAXDATE FROM TABLEB B WHERE B.Registration_id = <all registrations for client>)

Can you finish the sentence and incorporate it into your query ?
Back to top
View user's profile Send private message
vickey_dw

New User


Joined: 10 Jun 2015
Posts: 51
Location: India

PostPosted: Sun Jan 28, 2018 10:39 pm
Reply with quote

Hi All

Time-stamp is present in another table table B(as mentioned in 1st post) and hence i cannot include it in this query.

Code:
Select X.Registration_id from TableA X,TableA Y Where
       X.Registration_id  <> Y.Registration_id and X.Client_No = Y.Client_No;


Output of above query will be Registrations_ids like below for same Client_no
Code:

Registration_id1
Registration_id2
Registration_id3


Now i need to find the latest time-stamp of these Reg-ids from table B and fetch all except Registration_ids with max time stamp.
Below will explain this (i have used date instead of time-stamp to keep it simple)

Code:

                           Last_Login_Email     Last_Login_Mobile    Last_Login_OFX                 
Registration_id1    31-01-2011             31-01-2012             31-01-2013
Registration_id2    31-01-2016             31-01-2015             31-01-2014
Registration_id3    31-01-2018             31-01-2019             31-01-2017

For Registration_id1 max time-stamp is 31-01-2013 (Last_Login_OFX)
For Registration_id2 max time-stamp is 31-01-2016 (Last_Login_Email)
For Registration_id3 max time-stamp is 31-01-2019 (Last_Login_Mobile)



Here maximum times-tamp is for Registration_id3 hence i want to skip Registration_id3 and fetch Registration_id2 and Registration_id1.

Hope i am clear now.
Back to top
View user's profile Send private message
vickey_dw

New User


Joined: 10 Jun 2015
Posts: 51
Location: India

PostPosted: Sun Jan 28, 2018 11:38 pm
Reply with quote

Hi Marso,

Great thanks for your solution!!!

Your post was not visible to me when i post my last post.

I have not quite understand your solution but i will go through it again tomorrow when i will have access to Mainframe and let you know if it solves my problem.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


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

PostPosted: Tue Jan 30, 2018 1:25 am
Reply with quote

You need to gives us sample input and output required with proper understanding of requirements.
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 To get the the current time DFSORT/ICETOOL 13
No new posts RC query -Time column CA Products 3
No new posts C Compile time time stamps Java & MQSeries 10
No new posts Parallelization in CICS to reduce res... CICS 4
No new posts Dynamically pass table name to a sele... DB2 2
Search our Forums:

Back to Top