| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
11-23-11, 09:52
|
|
Registered User
|
|
Join Date: Sep 2011
Posts: 107
|
|
|
query returns latest date row
|
|
Hi;
The below query gives the latest date row..My requirement is like below
Code:
SELECT CD_PLT
, PRE_NAME
,IN_DT
,OUT_DT_FLAG
FROM (
SELECT T1.CD_PLT
, T1.PRE_NAME
, T2.IN_DT
,case when(T2.IN_DT > '2012-12-31')
then 'y'
else 'N'
end OUT_DT_FLAG
, ROW_NUMBER()
OVER(PARTITION BY T1.CD_PLT, T1.PRE_NAME
ORDER BY T2.IN_DT DESC) AS rn
FROM TABLE1 T1
LEFT OUTER JOIN
TABLE2 T2
ON
T1.CD_PLT=T2.CD_PLT
T1.PRE_NAME=T2.PRE_NAME
) s
WHERE rn = 1
;
The below query gives the latest date row..My requirement is like below
If the T2.IN_DT has two records like less than and greater than of 2012-12-31 means,
we have to display the less than IN_DT record, at the same time the OUT_DT_FLAG will be setting
the value 'Y' else 'N'
If the T2.IN_DT has only greater date means,the T2.IN_DT will be setting empty and the OUT_DT_FLAG will be setting 'Y'
Input Table
Code:
CD_PLT PRE_NAME IN_DT
----------- ------------ --------------
ABACC 6065 2013-01-13
ABACC 6065 2011-01-11
ABACC 6077 2011-01-12
ABACC 6077 2012-01-10
ABACC 6000 2011-10-09
ABACC 6000 2010-11-10
ABACC 6000 2009-11-11
BBBBB 7777 2015-02-22
BBBBB 7777 2014-01-21
MY QUERY RETURNS LIKE BELOW
Code:
CD_PLT PRE_NAME IN_DT OUT_DT_FLAG
----------- ------------ -------------- ---------------
ABACC 6065 2013-01-13 Y
ABACC 6077 2012-01-12 Y
ABACC 6000 2011-10-09 N
BBBBB 7777 2015-02-22 Y
EXPECTED RESULT
Code:
CD_PLT PRE_NAME IN_DT OUT_DT_FLAG
----------- ------------ -------------- ---------------
ABACC 6065 2011-01-11 Y
ABACC 6077 2011-01-12 Y
ABACC 6000 2011-10-09 N
BBBBB 7777 Y
Please help
|
|
11-23-11, 10:57
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Please write basic information by referencing Must Read before posting.
For example:
Quote:
|
1) Every question posted must include your DB2 Version, fixpack and Edition + your Operating System(including version info) + info on any third party software you use.
|
I thought that your descriptions were incomplete and inconsistent.
For example:
1) Although there were TABLE1 and TABLE2 in your code,
you showed sample data of "Input Table".
2)
Quote:
If the T2.IN_DT has two records like less than and greater than of 2012-12-31 means,
we have to display the less than IN_DT record, ...
If the T2.IN_DT has only greater date means,the T2.IN_DT will be setting empty ...
|
These two rows are both less than 2012-12-31, but you showed only 2011-01-12 in EXPECTED RESULT.
Code:
ABACC 6077 2011-01-12
ABACC 6077 2012-01-10
No description for the condition two(or more) rows are both less than 2012-12-31.
No description the reason to exclude 2012-01-10.
|
|
11-24-11, 04:52
|
|
Registered User
|
|
Join Date: Sep 2011
Posts: 107
|
|
|
|
Hi;
Db2Version is
DB2 SQL PRECOMPILER VERSION 9 REL. 1.0
1.Input Table..I was just taking the sample records of after JOINS
2.Apology for that...I could have missed to type the reason
Code:
ABACC 6077 2011-01-12
ABACC 6077 2012-01-10-->lessthan of 2012-12-31 & latest
for the above two records ...Need to select the latest record and should return to the Expected Result...
To fetch one latest IN_DT record for each CD_PLT,PRE_NAME,I was using the DESC IN_DT and ROW_NUMBER() code
Table1
Code:
CD_PLT PRE_NAME
----------- ------------
ABACC 6065
ABACC 6065
ABACC 6077
ABACC 6077
ABACC 6000
ABACC 6000
ABACC 6000
BBBBB 7777
BBBBB 7777
TABLE2
Code:
CD_PLT PRE_NAME IN_DT
----------- ------------ --------------
ABACC 6065 2013-01-13
ABACC 6065 2011-01-11 -->lessthan of 2012-12-31 & latest
ABACC 6077 2012-01-10 -->lessthan of 2012-12-31 & latest
ABACC 6077 2011-01-12
ABACC 6000 2011-10-09 -->lessthan of 2012-12-31 & latest
ABACC 6000 2010-11-10
ABACC 6000 2009-11-11
BBBBB 7777 2015-02-22
BBBBB 7777 2014-01-21
EXPECTED RESULT SET
Code:
CD_PLT PRE_NAME IN_DT OUT_DT_FLAG
----------- ------------ -------------- ---------------
ABACC 6065 2011-01-11 Y
ABACC 6077 2011-01-12 N
ABACC 6000 2011-10-09 N
BBBBB 7777 Y
Please help;
|
|
11-24-11, 07:56
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Note 1: IN_DT of second row of EXPECTED RESULT SET was 2011-01-12.
But, comment in TABLE2 showed
Code:
ABACC 6077 2012-01-10 -->lessthan of 2012-12-31 & latest
I took 2012-01-10
Note 2: rnum is used to make order of result rows same as your EXPECTED RESULT SET.
But, it is not guaranteed.
Example 1:
Code:
------------------------------ Commands Entered ------------------------------
WITH
Table1(CD_PLT , PRE_NAME) AS (
VALUES
( 'ABACC' , 6065 )
, ( 'ABACC' , 6065 )
, ( 'ABACC' , 6077 )
, ( 'ABACC' , 6077 )
, ( 'ABACC' , 6000 )
, ( 'ABACC' , 6000 )
, ( 'ABACC' , 6000 )
, ( 'BBBBB' , 7777 )
, ( 'BBBBB' , 7777 )
)
, Table2(CD_PLT , PRE_NAME , IN_DT) AS (
VALUES
( 'ABACC' , 6065 , '2013-01-13' )
, ( 'ABACC' , 6065 , '2011-01-11' )
, ( 'ABACC' , 6077 , '2012-01-10' )
, ( 'ABACC' , 6077 , '2011-01-12' )
, ( 'ABACC' , 6000 , '2011-10-09' )
, ( 'ABACC' , 6000 , '2010-11-10' )
, ( 'ABACC' , 6000 , '2009-11-11' )
, ( 'BBBBB' , 7777 , '2015-02-22' )
, ( 'BBBBB' , 7777 , '2014-01-21' )
)
SELECT cd_plt
, pre_name
, in_dt
, CASE
WHEN in_dt IS NULL THEN 'Y'
WHEN rnum2 = 1 THEN 'N'
ELSE 'Y'
END AS out_dt_flag
FROM (SELECT t.*
, ROW_NUMBER()
OVER( PARTITION BY cd_plt , pre_name
ORDER BY in_dt DESC NULLS LAST
) rnum1
, ROW_NUMBER()
OVER( PARTITION BY cd_plt , pre_name
ORDER BY in_dt DESC NULLS FIRST
) rnum2
FROM (SELECT t1.cd_plt
, t1.pre_name
, CASE
WHEN t2.in_dt <= '2012-12-31' THEN
t2.in_dt
END AS in_dt
, rnum
FROM table1 t1
LEFT OUTER JOIN
(SELECT t2.*
, ROW_NUMBER() OVER() AS rnum
FROM table2 t2
) t2
ON t2.cd_plt = t1.cd_plt
AND t2.pre_name = t1.pre_name
) t
) s
WHERE rnum1 = 1
ORDER BY
rnum
;
------------------------------------------------------------------------------
CD_PLT PRE_NAME IN_DT OUT_DT_FLAG
------ ----------- ---------- -----------
ABACC 6065 2011-01-11 Y
ABACC 6077 2012-01-10 N
ABACC 6000 2011-10-09 N
BBBBB 7777 - Y
4 record(s) selected.
|
|
11-24-11, 09:56
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
The result of your join contains redundant rows(two or three in your sample data).
So, it is better to add some more condition to ON clause
or remove redundant rows from table1.
(I guessed table1(and table2?) might be result of a subquery or a view,
because duplicated rows exists.)
You may want to change the query to produce table1 to remove the redundant rows.
Here is the result of a query removing outer most WHERE clause from Example 1.
Example 2:
Code:
------------------------------ Commands Entered ------------------------------
/* same as example 1 except commented out WHERE clause */
) s
-- WHERE rnum1 = 1
ORDER BY
rnum
;
------------------------------------------------------------------------------
CD_PLT PRE_NAME IN_DT OUT_DT_FLAG
------ ----------- ---------- -----------
ABACC 6065 - Y
ABACC 6065 - Y
ABACC 6065 2011-01-11 Y
ABACC 6065 2011-01-11 Y
ABACC 6077 2012-01-10 N
ABACC 6077 2012-01-10 Y
ABACC 6077 2011-01-12 Y
ABACC 6077 2011-01-12 Y
ABACC 6000 2011-10-09 N
ABACC 6000 2011-10-09 Y
ABACC 6000 2011-10-09 Y
ABACC 6000 2010-11-10 Y
ABACC 6000 2010-11-10 Y
ABACC 6000 2010-11-10 Y
ABACC 6000 2009-11-11 Y
ABACC 6000 2009-11-11 Y
ABACC 6000 2009-11-11 Y
BBBBB 7777 - Y
BBBBB 7777 - Y
BBBBB 7777 - Y
BBBBB 7777 - Y
21 record(s) selected.
|
|
11-24-11, 10:04
|
|
Registered User
|
|
Join Date: Sep 2011
Posts: 107
|
|
Great Tonkuma...thanks...working fine...
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
query returns latest date row
