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
 
Query to skip Max time-stamp and fetch other

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

New User


Joined: 10 Jun 2015
Posts: 50
Location: India

PostPosted: Sun Jan 28, 2018 1:09 am    Post subject: Query to skip Max time-stamp and fetch other
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10457
Location: italy

PostPosted: Sun Jan 28, 2018 1:50 am    Post subject: Reply to: Query to skip Max time-stamp and fetch other
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: 50
Location: India

PostPosted: Sun Jan 28, 2018 10:57 am    Post subject:
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: 2002
Location: UK

PostPosted: Sun Jan 28, 2018 6:06 pm    Post subject:
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10457
Location: italy

PostPosted: Sun Jan 28, 2018 6:20 pm    Post subject: Reply to: Query to skip Max time-stamp and fetch other
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: 1275
Location: Israel

PostPosted: Sun Jan 28, 2018 10:20 pm    Post subject: SELECT
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: 50
Location: India

PostPosted: Sun Jan 28, 2018 10:39 pm    Post subject:
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: 50
Location: India

PostPosted: Sun Jan 28, 2018 11:38 pm    Post subject:
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

Senior Member


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

PostPosted: Tue Jan 30, 2018 1:25 am    Post subject:
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    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 syntax check at Bind time is diff... Poha Eater DB2 5 Wed Mar 21, 2018 9:16 pm
No new posts Query to compare 2 values of 1 column... Poha Eater DB2 13 Fri Mar 09, 2018 10:45 am
No new posts How to replace the below query? pkmurali DB2 12 Tue Feb 27, 2018 9:51 pm
No new posts Merging records, but not all the time prino DFSORT/ICETOOL 17 Sat Feb 17, 2018 4:20 pm
No new posts Need to identify top 10 costly or tim... ashek15 DB2 4 Wed Feb 14, 2018 3:20 am

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