|
View previous topic :: View next topic
|
| Author |
Message |
vickey_dw
New User
Joined: 10 Jun 2015
Posts: 51
Location: India
|
|
|
|
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 |
|
 |
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10888
Location: italy
|
|
|
|
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 |
|
|
vickey_dw
New User
Joined: 10 Jun 2015
Posts: 51
Location: India
|
|
|
|
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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2454
Location: Hampshire, UK
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10888
Location: italy
|
|
|
|
| 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 |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
| 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:
- 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 |
"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 |
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 |
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> |
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 |
|
|
vickey_dw
New User
Joined: 10 Jun 2015
Posts: 51
Location: India
|
|
|
|
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 |
|
|
vickey_dw
New User
Joined: 10 Jun 2015
Posts: 51
Location: India
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| You need to gives us sample input and output required with proper understanding of requirements. |
|
| Back to top |
|
|
|
|
