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
 
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: 6968
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: 10326
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 HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Writing a file using online program grvtomar PL/I & Assembler 3 Fri Jun 30, 2017 1:06 pm
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am

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