Home » SQL & PL/SQL » SQL & PL/SQL » query with rank function
| query with rank function [message #330823] |
Tue, 01 July 2008 07:47  |
janardhanam.k
Messages: 29
Registered: April 2007
|
Junior Member |
|
|
SELECT
rec.utrasup_supplement_band_code,
rec.utrasup_srvc_code,
rec.base_styp_code,
rec.from_date,
rec.until_date,
rec.code,
rec.utrasup_supplement_band_amount
FROM (SELECT distinct
utrasup_supplement_band_code,
utrasup_srvc_code,
DECODE(utrbase_styp_code,
'AGA','ALL',
'BBC','ALL',
'WAU','ALL',
'WAW','ALL',
'BBF','ALL',
'CHB',DECODE(utrasup_srvc_code,
'3SLT',utrbase_styp_code,
'ALL' ),
'LB', DECODE(utrasup_srvc_code,
'2SPT','ALL',
utrbase_styp_code),
'LBX', DECODE(utrasup_srvc_code,
'2SPT','ALL',
utrbase_styp_code),
utrbase_styp_code) AS base_styp_code,
'GCN' as code,
utrasup_supplement_band_amount,
CASE WHEN TRUNC(utrasup_effective_from_date) < '01-JAN-2005' THEN
to_char(TO_DATE('01-JAN-2005','DD-MON-YYYY'),'DD/MM/YYYY')
WHEN TRUNC(utrasup_effective_from_date) = '04-AUG-2005' and utrasup_supplement_band_code = 'O' THEN
to_char(TO_DATE('01-JAN-2005','DD-MON-YYYY'),'DD/MM/YYYY')
when trunc(utrasup_effective_to_date) > '31-DEC-2010' then
to_char(to_date('31-DEC-2099','DD-MON-YYYY'), 'DD/MM/YYYY')
ELSE
to_char(TRUNC(utrasup_effective_from_date),'DD/MM/YYYY')
END AS from_date,
CASE WHEN TRUNC(utrasup_effective_to_date) < TRUNC(utrbase_effective_to_date) THEN
to_char(TRUNC(utrasup_effective_to_date),'DD/MM/YYYY')
WHEN TRUNC(utrasup_effective_to_date) >= '31-DEC-2010' then
to_char(to_date('31-DEC-2099','DD-MON-YYYY'), 'DD/MM/YYYY')
ELSE
to_char(TRUNC(utrbase_effective_to_date),'DD/MM/YYYY')
END AS until_date,
RANK() OVER (PARTITION BY utrasup_srvc_code,
utrbase_styp_code,
utrasup_supplement_band_amount,
TRUNC(utrasup_effective_from_date)
ORDER BY
DECODE(trunc(utrasup_effective_to_Date), to_date('31-DEC-2010','DD-MON-YYYY'),
TRUNC(utrbase_effective_to_date), TRUNC(utrasup_effective_to_date)) DESC,
utrbase.rowid,
utrasup.rowid) rank
FROM uimsmgr.utrbase,
uimsmgr.utrasup
WHERE utrbase_srvc_code = utrasup_srvc_code
AND trunc(utrbase_effective_to_date) > '01-JAN-2005'
AND utrbase_price_method_ren = '02'
AND trunc(utrasup_effective_to_date) > '01-JAN-2005'
AND utrasup_effective_from_date < LEAST(utrasup_effective_to_date, utrbase_effective_to_date)
AND utrbase_styp_code NOT IN ('FRB','WAC','FRC')) rec
WHERE rank = 1
order by
rec.utrasup_supplement_band_code,
rec.utrasup_srvc_code,
rec.from_date,
rec.until_date,
rec.code,
rec.utrasup_supplement_band_amount,
rec.base_styp_code ;
Above query fetches 20 records with until_date in the year 2005.But when I change the condition in the case (WHEN TRUNC(utrasup_effective_to_date) >= '31-DEC-2010' ) of until_date to TRUNC(utrasup_effective_to_date) > '31-DEC-2010' then I get 23 records with until_date in the year 2005.
The change in condition should only affect the dates of 2010 year but why the 2005 year dates are getting affected?
These are the 3 records which are getting affected :
A,3SLT,CHB,01/01/2005,25/08/2005,GCN,0
B,3SLT,CHB,01/01/2005,25/08/2005,GCN,6
Z,3SLT,CHB,01/01/2005,25/08/2005,GCN,0
Can any one help.
|
|
|
|
|
|
| Re: query with rank function [message #330968 is a reply to message #330830] |
Tue, 01 July 2008 23:38  |
janardhanam.k
Messages: 29
Registered: April 2007
|
Junior Member |
|
|
Thanks for your reply michel.
The condition where the problem is occuring is :
WHEN TRUNC(utrasup_effective_to_date) >= '31-DEC-2010' then
to_char(to_date('31-DEC-2099','DD-MON-YYYY'), 'DD/MM/YYYY')
ELSE
to_char(TRUNC(utrbase_effective_to_date),'DD/MM/YYYY')
END AS until_date,
utrasup_effective_to_date is of type date in utrasup table.In the condition only the date is compared and based on the condition the value of until_date is set.Also the until_date value in the 3 records is 25/08/2005.Please help.
|
|
|
|
| Re: query with rank function [message #330975 is a reply to message #330830] |
Tue, 01 July 2008 23:55 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
| Michel Cadot wrote on Tue, 01 July 2008 14:55 | '01-JAN-2005' is not date it is a string.
|
You are comparing utrasup_effective_to_date with string '31-DEC-2010'. So it is implicitely converted to string and compared using string comparison rules, where e.g. '31-DEC-2010' < '31-JAN-2008' (as 'D' < 'J').
Just to summarize it:
'01-JAN-2005' is not date.
to_date( '01-JAN-2005', 'DD-MON-YYYY' ) is date.
|
|
|
|
| Re: query with rank function [message #330994 is a reply to message #330975] |
Wed, 02 July 2008 01:33 |
janardhanam.k
Messages: 29
Registered: April 2007
|
Junior Member |
|
|
I changed the condition as below :
WHEN TRUNC(utrasup_effective_to_date) >= to_date('31-DEC-2010','DD-MON-YYYY') then
to_char(to_date('31-DEC-2099','DD-MON-YYYY'), 'DD/MM/YYYY')
ELSE
to_char(TRUNC(utrbase_effective_to_date),'DD/MM/YYYY')
END AS until_date
and checked the record count for until_date (with dates in year 2005) ,it comes to be 23.
When i changed the condition as below:
WHEN TRUNC(utrasup_effective_to_date) > to_date('31-DEC-2010','DD-MON-YYYY') then
to_char(to_date('31-DEC-2099','DD-MON-YYYY'), 'DD/MM/YYYY')
ELSE
to_char(TRUNC(utrbase_effective_to_date),'DD/MM/YYYY')
END AS until_date
and checked the record count for until_date (with dates in year 2005), it comes to be 20.
The following records are getting converted as 31-DEC-2099 :
A,3SLT,CHB,01/01/2005,25/08/2005,GCN,0
B,3SLT,CHB,01/01/2005,25/08/2005,GCN,6
Z,3SLT,CHB,01/01/2005,25/08/2005,GCN,0
which should not happen.I hope,now the comparison is date based.
|
|
|
|
|
|
| Re: query with rank function [message #331296 is a reply to message #331160] |
Wed, 02 July 2008 23:27 |
janardhanam.k
Messages: 29
Registered: April 2007
|
Junior Member |
|
|
I changed the other dates as well.The query is given below:
SELECT
rec.utrasup_supplement_band_code,
rec.utrasup_srvc_code,
rec.base_styp_code,
rec.from_date,
rec.until_date,
rec.code,
rec.utrasup_supplement_band_amount
FROM (SELECT distinct
utrasup_supplement_band_code,
utrasup_srvc_code,
DECODE(utrbase_styp_code,
'AGA','ALL',
'BBC','ALL',
'WAU','ALL',
'WAW','ALL',
'BBF','ALL',
'CHB',DECODE(utrasup_srvc_code,
'3SLT',utrbase_styp_code,
'ALL' ),
'LB', DECODE(utrasup_srvc_code,
'2SPT','ALL',
utrbase_styp_code),
'LBX', DECODE(utrasup_srvc_code,
'2SPT','ALL',
utrbase_styp_code),
utrbase_styp_code) AS base_styp_code,
'GCN' as code,
utrasup_supplement_band_amount,
CASE WHEN TRUNC(utrasup_effective_from_date) < to_date('01-JAN-2005','DD-MON-YYYY') THEN
to_char(TO_DATE('01-JAN-2005','DD-MON-YYYY'),'DD/MM/YYYY')
WHEN TRUNC(utrasup_effective_from_date) = to_date('04-AUG-2005','DD-MON-YYYY') and utrasup_supplement_band_code = 'O' THEN
to_char(TO_DATE('01-JAN-2005','DD-MON-YYYY'),'DD/MM/YYYY')
when trunc(utrasup_effective_to_date) > to_date('31-DEC-2010','DD-MON-YYYY') then
to_char(to_date('31-DEC-2099','DD-MON-YYYY'), 'DD/MM/YYYY')
ELSE
to_char(TRUNC(utrasup_effective_from_date),'DD/MM/YYYY')
END AS from_date,
CASE WHEN TRUNC(utrasup_effective_to_date) < TRUNC(utrbase_effective_to_date) THEN
to_char(TRUNC(utrasup_effective_to_date),'DD/MM/YYYY')
WHEN TRUNC(utrasup_effective_to_date) > to_date('31-DEC-2010','DD-MON-YYYY') then
to_char(to_date('31-DEC-2099','DD-MON-YYYY'), 'DD/MM/YYYY')
ELSE
to_char(TRUNC(utrbase_effective_to_date),'DD/MM/YYYY')
END AS until_date,
RANK() OVER (PARTITION BY utrasup_srvc_code,
utrbase_styp_code,
utrasup_supplement_band_amount,
TRUNC(utrasup_effective_from_date)
ORDER BY
DECODE(trunc(utrasup_effective_to_Date), to_date('31-DEC-2010','DD-MON-YYYY'),
TRUNC(utrbase_effective_to_date), TRUNC(utrasup_effective_to_date)) DESC,
utrbase.rowid,
utrasup.rowid) rank
FROM uimsmgr.utrbase,
uimsmgr.utrasup
WHERE utrbase_srvc_code = utrasup_srvc_code
AND trunc(utrbase_effective_to_date) > '01-JAN-2005'
AND utrbase_price_method_ren = '02'
AND trunc(utrasup_effective_to_date) > '01-JAN-2005'
AND utrasup_effective_from_date < LEAST(utrasup_effective_to_date, utrbase_effective_to_date)
AND utrbase_styp_code NOT IN ('FRB','WAC','FRC')) rec
WHERE rank = 1
order by
rec.utrasup_supplement_band_code,
rec.utrasup_srvc_code,
rec.from_date,
rec.until_date,
rec.code,
rec.utrasup_supplement_band_amount,
rec.base_styp_code
Even then the same problem persists.
|
|
|
|
| Re: query with rank function [message #331312 is a reply to message #331296] |
Wed, 02 July 2008 23:52 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
You still compare dates to strings: AND trunc(utrbase_effective_to_date) > '01-JAN-2005'
AND utrbase_price_method_ren = '02'
AND trunc(utrasup_effective_to_date) > '01-JAN-2005'
And what's the use of this?to_char(TO_DATE('01-JAN-2005','DD-MON-YYYY'),'DD/MM/YYYY')
Why not use '01/01/2005' ?
Or is the 01-JAN-2005 not so hard-coded as you show us here, and is the query not the actual beast you are executing?
|
|
|
|
| Re: query with rank function [message #331321 is a reply to message #331312] |
Thu, 03 July 2008 00:22 |
janardhanam.k
Messages: 29
Registered: April 2007
|
Junior Member |
|
|
I changed the query as below:
SELECT
rec.utrasup_supplement_band_code,
rec.utrasup_srvc_code,
rec.base_styp_code,
rec.from_date,
rec.until_date,
rec.code,
rec.utrasup_supplement_band_amount
FROM (SELECT distinct
utrasup_supplement_band_code,
utrasup_srvc_code,
DECODE(utrbase_styp_code,
'AGA','ALL',
'BBC','ALL',
'WAU','ALL',
'WAW','ALL',
'BBF','ALL',
'CHB',DECODE(utrasup_srvc_code,
'3SLT',utrbase_styp_code,
'ALL' ),
'LB', DECODE(utrasup_srvc_code,
'2SPT','ALL',
utrbase_styp_code),
'LBX', DECODE(utrasup_srvc_code,
'2SPT','ALL',
utrbase_styp_code),
utrbase_styp_code) AS base_styp_code,
'GCN' as code,
utrasup_supplement_band_amount,
CASE WHEN TRUNC(utrasup_effective_from_date) < to_date('01-JAN-2005','DD-MON-YYYY') THEN
'01/01/2005'
WHEN TRUNC(utrasup_effective_from_date) = to_date('04-AUG-2005','DD-MON-YYYY') and utrasup_supplement_band_code = 'O' THEN
'01/01/2005'
when trunc(utrasup_effective_to_date) > to_date('31-DEC-2010','DD-MON-YYYY') then
'31/12/2099'
ELSE
to_char(TRUNC(utrasup_effective_from_date),'DD/MM/YYYY')
END AS from_date,
CASE WHEN TRUNC(utrasup_effective_to_date) < TRUNC(utrbase_effective_to_date) THEN
to_char(TRUNC(utrasup_effective_to_date),'DD/MM/YYYY')
WHEN TRUNC(utrasup_effective_to_date) > to_date('31-DEC-2010','DD-MON-YYYY') then
'31/12/2099'
ELSE
to_char(TRUNC(utrbase_effective_to_date),'DD/MM/YYYY')
END AS until_date,
RANK() OVER (PARTITION BY utrasup_srvc_code,
utrbase_styp_code,
utrasup_supplement_band_amount,
TRUNC(utrasup_effective_from_date)
ORDER BY
DECODE(trunc(utrasup_effective_to_Date), to_date('31-DEC-2010','DD-MON-YYYY'),
TRUNC(utrbase_effective_to_date), TRUNC(utrasup_effective_to_date)) DESC,
utrbase.rowid,
utrasup.rowid) rank
FROM uimsmgr.utrbase,
uimsmgr.utrasup
WHERE utrbase_srvc_code = utrasup_srvc_code
AND trunc(utrbase_effective_to_date) > to_date('01-JAN-2005','DD-MON-YYYY')
AND utrbase_price_method_ren = '02'
AND trunc(utrasup_effective_to_date) > to_date('01-JAN-2005','DD-MON-YYYY')
AND utrasup_effective_from_date < LEAST(utrasup_effective_to_date, utrbase_effective_to_date)
AND utrbase_styp_code NOT IN ('FRB','WAC','FRC')) rec
WHERE rank = 1
order by
rec.utrasup_supplement_band_code,
rec.utrasup_srvc_code,
rec.from_date,
rec.until_date,
rec.code,
rec.utrasup_supplement_band_amount,
rec.base_styp_code
I still face the same problem.
The query that i posted here is the actual query that iam executing.01-JAN-2005 date was hard coded as given by me here...but i changed it to 01/01/2005 in order to incorporate your suggestion.
|
|
|
|
| Re: query with rank function [message #331341 is a reply to message #330994] |
Thu, 03 July 2008 01:26 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| janardhanam.k wrote on Wed, 02 July 2008 08:33 |
The following records are getting converted as 31-DEC-2099 :
A,3SLT,CHB,01/01/2005,25/08/2005,GCN,0
B,3SLT,CHB,01/01/2005,25/08/2005,GCN,6
Z,3SLT,CHB,01/01/2005,25/08/2005,GCN,0
which should not happen.I hope,now the comparison is date based.
|
Unfortunately, you did not tell us (at least, I could not find it) which column is which.
If the first column that contains something similar to a date (i.e. '01/01/2005') is your utrasup_effective_from_date column, then it is correct that it falls through the CASE. After all, the date 01-01-2005 equals 01-01-2005, so it is NOT <
|
|
|
|
Goto Forum:
Current Time: Sun Feb 09 09:59:07 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
