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 help in writing a query

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

New User


Joined: 07 Dec 2006
Posts: 51
Location: Bloomington

PostPosted: Wed Mar 18, 2009 11:25 am    Post subject: Need help in writing a query
Reply with quote

Hi,

Can any one help me in providing a query. Table with example records are as shown below.

Code:
Appl_no       Load_TM       Partic_Cd          Cust Name     

20001234      01-11-2009    P                  Jim Bob

20004322      03-10-2009    P                  Sara Beth

20004322      12-22-2008    S                  Billy Joe

20006587      03-04-2009    P                  Tom Wayne

Here I have a situation where the record can present more than one in table, in this case I have a requirement to take the records latest 'LOAD_TM' and update LOAD_TM with the latest date.

after doing the above the table should like the following

Code:
Appl_no       Load_TM       Partic_Cd          Cust Name     

20001234      01-11-2009    P                  Jim Bob

20004322      03-10-2009    P                  Sara Beth

20004322      03-10-2009    S                  Billy Joe

20006587      03-04-2009    P                  Tom Wayne

There can be two queries one for select and other for update. Can any one suggest me the select query to get the latest LOAD_TM for the record.

Note : Record Key is APPL_NO

Thanks and Regards,
Rajkumar Nerella
Back to top
View user's profile Send private message

Rajkumar_n

New User


Joined: 07 Dec 2006
Posts: 51
Location: Bloomington

PostPosted: Wed Mar 18, 2009 11:32 am    Post subject:
Reply with quote

Repositing the same as the table structure was not shown properly in first case

Hi,

Can any one help me in providing a query. Table with example records are as shown below.
Code:

Appl_no    Load_TM      Partic_Cd    Cust Name

20001234   01-11-2009   P            Jim Bob

20004322   03-10-2009   P            Sara Beth
 
20004322   12-22-2008   S            Billy Joe

20006587   03-04-2009   P            Tom Wayne


Here I have a situation where the record can present more than one in table, in this case I have a requirement to take the records latest 'LOAD_TM' and update LOAD_TM with the latest date.

after doing the above the table should like the following

Code:

Appl_no    Load_TM      Partic_Cd    Cust Name

20001234   01-11-2009   P            Jim Bob

20004322   03-10-2009   P            Sara Beth

20004322   03-10-2009   S            Billy Joe

20006587   03-04-2009   P            Tom Wayne

There can be two queries one for select and other for update. Can any one suggest me the select query to get the latest LOAD_TM for the record.

Note : Record Key is APPL_NO

I HAVE APPL_NO and LOAD_TM IN MY SELECTION CRITERIA.

Thanks and Regards,
Rajkumar Nerella
Back to top
View user's profile Send private message
Succor

New User


Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)

PostPosted: Wed Mar 18, 2009 11:59 am    Post subject: Reply to: Need help in writing a query
Reply with quote

Little Confused icon_sad.gif
As per your example latest 'LOAD_TM' value is 03-10-2009.
And after update...only the the earliest 'LOAD_TM' value i.e. 12-22-2008 was changed to 03-10-2009...rest left untouched.
So, is this what you are looking for .

WTF
Back to top
View user's profile Send private message
Rajkumar_n

New User


Joined: 07 Dec 2006
Posts: 51
Location: Bloomington

PostPosted: Wed Mar 18, 2009 12:13 pm    Post subject:
Reply with quote

YES. there is one record with LOAD-TM AS 03-10-2009 and other record has 12-22-2008 .

Between these two records latest LOAD_TM IS 03-10-2009

We need to update the prior record to the same value 03-10-2009 .


Thanks and Regards,
Rajkumar Nerella
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Mar 18, 2009 2:01 pm    Post subject:
Reply with quote

records and fields relate to files.

db2 tables are constructed of columns and rows.

try to use the correct terminology, please.
Back to top
View user's profile Send private message
Rajkumar_n

New User


Joined: 07 Dec 2006
Posts: 51
Location: Bloomington

PostPosted: Wed Mar 18, 2009 5:15 pm    Post subject:
Reply with quote

HI,

Sorry for the if the post is causing any confusion. I tried to provide space between each column but some how it didnt worked.

The above specified are column names and related data. Specified values are for db2 column names.

Thanks and Regards,
Rajkumar Nerella
Back to top
View user's profile Send private message
maggiebms

New User


Joined: 23 May 2005
Posts: 16

PostPosted: Wed Mar 18, 2009 5:26 pm    Post subject: Reply to: Need help in writing a query
Reply with quote

Hi,

I think the below query can help you.

UPDATE <TABLE> T1 SET T1.Load_TM = (SELECT MAX(Load_TM) FROM
<TABLE> T2 WHERE T2.APPL_NO = T1.APPL_NO AND T2.LOAD_TM > T1.Load_TM))


Regards,
Maggie J
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10210
Location: italy

PostPosted: Wed Mar 18, 2009 5:33 pm    Post subject: Reply to: Need help in writing a query
Reply with quote

to all of You

please learn to use the code tags

it' s quite boring to have to edit your posts to make them readable
by adding the code tags
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 Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm


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