IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Need help for a query


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Rajkumar_n

New User


Joined: 07 Dec 2006
Posts: 51
Location: Bloomington

PostPosted: Mon Feb 02, 2009 10:59 pm
Reply with quote

Hi,

I have a requirement like the following

Table-1

App_no crte_dt load_tm .... additional columns


Table-2
App_no crte_dt load_tm ..... additional columns

Table-3
App_no crte_dt load_tm ..... additional columns

Table-4
App_no crte_dt load_tm ..... additional columns

Now my requirement is

I need to find the latest date LOAD_TM from all four tables for a particular application and apply some calculations.

Can any one tell me a simple query by using Join or some other functions for this requirement.

I am poor in SQL..Need to improve my self icon_cry.gif

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

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Tue Feb 03, 2009 10:47 am
Reply with quote

Do you need to find latest date in each of the tables or all tables put together. The latter though does not seem logical. Please state the requirement more clearly so that people here can help you.[/code]
Back to top
View user's profile Send private message
km_abdullah

New User


Joined: 03 Nov 2008
Posts: 60

PostPosted: Tue Feb 03, 2009 11:31 am
Reply with quote

If you want latest date from each of the table seperately though, the following query would help.

Code:
SELECT MAX(T1.load_tm), MAX(T2.load_tm), MAX(T3.load_tm), MAX(T4.load_tm) FROM Table1 A, Table2 B, Table3 C, Table4 D;


Assuming that load_tm is declared as DATE datatype.
Back to top
View user's profile Send private message
km_abdullah

New User


Joined: 03 Nov 2008
Posts: 60

PostPosted: Tue Feb 03, 2009 11:36 am
Reply with quote

Sorry, small correction with the table reference variable.

SELECT MAX(T1.load_tm), MAX(T2.load_tm), MAX(T3.load_tm), MAX(T4.load_tm) FROM Table1 T1, Table2 T2, Table3 T3, Table4 T4;
Back to top
View user's profile Send private message
Rajkumar_n

New User


Joined: 07 Dec 2006
Posts: 51
Location: Bloomington

PostPosted: Tue Feb 03, 2009 6:37 pm
Reply with quote

Hi Sorry for the confusion caused.

Table looks like the following


TABLE 1

Appl_no CRTE_DT LOAD_TM ..... Additional columns

123456 01/25/09 2009-02-01....

TABLE 2

Appl_no CRTE_DT LOAD_TM ..... Additional columns

123456 01/25/09 2009-01-31....

TABLE 3

Appl_no CRTE_DT LOAD_TM ..... Additional columns

123456 01/25/09 2009-01-30....

TABLE 1

Appl_no CRTE_DT LOAD_TM ..... Additional columns

123456 01/25/09 2009-02-02....

My requirement is to get the latest for an application based on the latest date of column LOAD_TM and the output should be

APPL_NO LOAD_TM (We can have the column name as LATEST_DAY)

123456 2009-02-02

I need to have this result after checking all four tables for the appl_no '123456' and get the latest date as 2009-02-02.

Please let me know if you have any questions.

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

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Tue Feb 03, 2009 10:20 pm
Reply with quote

Hello,

An easy to get what you want is to write a bit of code. . .

Read table 1 and store the value.

Read table 2, compare and if the table 2 value is > than the stored value, store the value.

Read table 3, compare and if the table 3 value is > than the stored value, store the value.

Read table 4, compare and if the table 4 value is > than the stored value, store the value.

Whatever is now in the stored value is the "latest". . .
Back to top
View user's profile Send private message
manikant pathak

New User


Joined: 09 May 2005
Posts: 37
Location: bangalore

PostPosted: Wed Feb 04, 2009 3:02 pm
Reply with quote

Hello,

You can achieve the obejective by writing two queries instead of 1

The first query will look like:

Query#1:

SELECT A.LOAD_TM, B.LOAD_TM, C.LOAD_TM, D.LOAD_TM
INTO :WS-LOAD-TM-1
,:WS-LOAD-TM-2
,:WS-LOAD-TM-3
,:WS-LOAD-TM-4
FROM
TAB1 A
,TAB2 B
,TAB3 C
,TAB4 D
WHERE A.APP_NO = :WS-APP-NO
AND A.APP_NO = B.APP_NO
AND A.APP_NO = C.APP_NO
AND A.APP_NO - D.APP_NO.

Query#2:

SELECT MAX (:WS-LOAD-TM-1, :WS-LOAD-TM-2, :WS-LOAD-TM-3, :WS-LOAD-TM-4) FROM <TABLE-NAME> FETCH FIRST ONLY. The table name can be any existing table.


Thanks and Regards
Manikant

Note: I have written this post after testing the 2nd query in SPUFI.
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Wed Feb 04, 2009 4:39 pm
Reply with quote

Quote:
SELECT MAX (:WS-LOAD-TM-1, :WS-LOAD-TM-2, :WS-LOAD-TM-3, :WS-LOAD-TM-4) FROM <TABLE-NAME> FETCH FIRST ONLY. The table name can be any existing table.


Not a good practise. Does not make sense to hit the DB2 again for something that could be easily and effeciently done using cobol.
Back to top
View user's profile Send private message
manikant pathak

New User


Joined: 09 May 2005
Posts: 37
Location: bangalore

PostPosted: Wed Feb 04, 2009 5:24 pm
Reply with quote

Hi Bharath,

Well in that case we can write the query as

SELECT MAX (A.LOAD_TM, B.LOAD_TM, C.LOAD_TM, D.LOAD_TM)
INTO :WS-LATEST-DATE
FROM
TAB1 A
,TAB2 B
,TAB3 C
,TAB4 D
WHERE A.APP_NO = :WS-APP-NO
AND A.APP_NO = B.APP_NO
AND A.APP_NO = C.APP_NO
AND A.APP_NO = D.APP_NO.

This will hit the DB2 only once and will be effective also.

Regards
Manikant
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Wed Feb 04, 2009 7:57 pm
Reply with quote

Quote:
This will hit the DB2 only once and will be effective also.

For sure if it works fine for TS. icon_cool.gif
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Feb 04, 2009 11:46 pm
Reply with quote

Hello,

May not get the desired results if the app_no is not in all 4 tables. . .
Back to top
View user's profile Send private message
Rajkumar_n

New User


Joined: 07 Dec 2006
Posts: 51
Location: Bloomington

PostPosted: Thu Feb 05, 2009 8:30 pm
Reply with quote

Hi,

Yes we may not get desired result because lot of other factors shown below.

1. There is no synchronization between all four tables

(i.e TABLE 1,TABLE 2,TABLE 3 and TABLE 4 will not have same number of records)

2. There are chances for an application to have two or more records in some of tables

3. If an application is available in three tables there are lot of chances for that application not to be available in the fourth table

Thanks to Manikant and Bharath for sharing threir thoughts. Manikants query is good but we dont the application number so that we can use it in the query.

I think we need to go for a code as suggested by 'dick scherrer'
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Query on edit primary command CLIST & REXX 5
No new posts Query on edit primary command CLIST & REXX 1
No new posts Issue with EXEC CICS QUERY SECURITY c... CICS 6
Search our Forums:

Back to Top