Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Need to merge a Querry

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

Active User


Joined: 20 Sep 2008
Posts: 102
Location: Bangalore

PostPosted: Fri Apr 03, 2009 6:09 pm    Post subject: Need to merge a Querry
Reply with quote

Hi all,

I am having a querry like beow,
Code:


SELECT MIN(UPDT_TS)                             
FROM Table
WHERE Field_1 = '1111111111'
and Field_2 = '03'
AND UPDT_TS >= (SELECT MAX(UPDT_TS)             
                FROM Table           
                WHERE Field_1 = '1111111111'
                and Field_2 != '03' )       
WITH UR;


The above Querry will fetch the UPDT_TS value when the Field_2 has been last time changed.

But in some cases the inner querry can result with null output.

At that time if i tried runnning the above querry i m getting - as an output.

So whenever the inner querry's output is null, The querry should be like below,

Code:

SELECT MIN(UPDT_TS)                             
FROM Table
WHERE Field_1 = '1111111111'
and Field_2 = '03'


So copuld you guys please help me to have a querry which can perform both the things in a single Select statement.

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

ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Fri Apr 03, 2009 6:58 pm    Post subject:
Reply with quote

Code:


SELECT MIN(UPDT_TS)                             
FROM Table
WHERE Field_1 = '1111111111'
and Field_2 = '03'
AND ( UPDT_TS >= (SELECT MAX(UPDT_TS)             
                FROM Table           
                WHERE Field_1 = '1111111111'
                and Field_2 != '03' )     OR 1=1 )



Back to top
View user's profile Send private message
Niki

Active User


Joined: 20 Sep 2008
Posts: 102
Location: Bangalore

PostPosted: Mon Apr 06, 2009 10:30 am    Post subject:
Reply with quote

Hi Ashimer,

Thanks for your Reply.

But by using the above query provided by u i m getting the output when the inner querry is null but at the mean time it is avoiding the inner query itself.

My actual requirement is like below,
Input - 1

TABLE
Code:

Field-1      field2   UPDT-TS
1111111111   03   1993-06-28-18.55.08.419262
1111111111   03   1993-06-07-19.35.54.394090
1111111111   03   1993-06-07-14.52.43.580583
1111111111   03   1993-06-05-06.34.08.427870


So when all the column in the field2 =03 for a particular field-1= 11111111111 Then the o/p Should be the minimum of UPDT-TS. I.e 1993-06-05-06.34.08.427870

When the input is like below,
TABLE
Code:

Field-1      field2   UPDT-TS
1111111111   03   1993-06-05-06.34.08.427870
1111111111   01   1993-06-07-14.52.43.580583
1111111111   02   1993-06-07-19.35.54.394090
1111111111   03   1993-06-28-18.55.08.419262
1111111111   03   2009-03-19-01.41.21.877979


I should get the output as
1993-06-28-18.55.08.419262
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Apr 06, 2009 2:14 pm    Post subject:
Reply with quote

Niki ,,your requirement is clear now ...

try this ...

Code:


SELECT MIN(UPDT_TS)                             
FROM Table
WHERE Field_1 = '1111111111'
and Field_2 = '03'
AND UPDT_TS >= (SELECT COALESCE(MAX(UPDT_TS) , '')
                FROM Table           
                WHERE Field_1 = '1111111111'
                and Field_2 != '03' )   

Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Apr 06, 2009 2:20 pm    Post subject:
Reply with quote

as your check is on timestamp field please use this coalesce marker.. .

Code:

UPDT_TS >=

(SELECT COALESCE(MAX(UPDT_TS),'0001-00-00-00.00.00.000000')
.......
......

Back to top
View user's profile Send private message
Niki

Active User


Joined: 20 Sep 2008
Posts: 102
Location: Bangalore

PostPosted: Mon Apr 06, 2009 2:43 pm    Post subject:
Reply with quote

Hi Ashimer,

Thanks for your concern regarding my issue.

I used the function COALESCE and its working fine.

Thank you very much.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Apr 06, 2009 2:44 pm    Post subject:
Reply with quote

You are welcome icon_smile.gif
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
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts To Merge mutliple records into a sing... anandgbe DFSORT/ICETOOL 6 Wed Feb 22, 2017 8:49 am
No new posts SQL Merge error -254 Robin Sulsona DB2 8 Wed Jun 01, 2016 8:28 pm
No new posts SAS MERGE to DFSORT Musab Ahmed DFSORT/ICETOOL 6 Mon Feb 01, 2016 5:44 pm
No new posts Merge two files having two trailer re... R.Manivannan DFSORT/ICETOOL 7 Wed Nov 25, 2015 9:18 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us