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 for 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: Mon Feb 02, 2009 10:59 pm    Post subject: Need help for a query
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject: Reply to: Need help for a query
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

Site Director


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

PostPosted: Tue Feb 03, 2009 10:20 pm    Post subject:
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    Post subject: Reply to: Need help for a query
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    Post subject:
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    Post subject: Reply to: Need help for a query
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    Post subject:
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

Site Director


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

PostPosted: Wed Feb 04, 2009 11:46 pm    Post subject:
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    Post subject: Reply to: Need help for a query
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    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 JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm
No new posts GETMAIN/FREEMAIN query Suja.Sai CICS 9 Tue Jan 31, 2017 12:01 pm


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