View previous topic :: View next topic
|
Author |
Message |
Rajkumar_n
New User
Joined: 07 Dec 2006 Posts: 51 Location: Bloomington
|
|
|
|
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
Thanks and Regards,
Rajkumar Nerella |
|
Back to top |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008 Posts: 283 Location: chennai
|
|
|
|
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 |
|
|
km_abdullah
New User
Joined: 03 Nov 2008 Posts: 60
|
|
|
|
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 |
|
|
km_abdullah
New User
Joined: 03 Nov 2008 Posts: 60
|
|
|
|
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 |
|
|
Rajkumar_n
New User
Joined: 07 Dec 2006 Posts: 51 Location: Bloomington
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
manikant pathak
New User
Joined: 09 May 2005 Posts: 37 Location: bangalore
|
|
|
|
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 |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008 Posts: 283 Location: chennai
|
|
|
|
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 |
|
|
manikant pathak
New User
Joined: 09 May 2005 Posts: 37 Location: bangalore
|
|
|
|
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 |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008 Posts: 283 Location: chennai
|
|
|
|
Quote: |
This will hit the DB2 only once and will be effective also. |
For sure if it works fine for TS. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
May not get the desired results if the app_no is not in all 4 tables. . . |
|
Back to top |
|
|
Rajkumar_n
New User
Joined: 07 Dec 2006 Posts: 51 Location: Bloomington
|
|
|
|
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 |
|
|
|