Home » SQL & PL/SQL » SQL & PL/SQL » query with rank function
query with rank function [message #330823] Tue, 01 July 2008 07:47 Go to next message
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 #330830 is a reply to message #330823] Tue, 01 July 2008 07:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
'01-JAN-2005' is not date it is a string.

Regards
Michel
Re: query with rank function [message #330968 is a reply to message #330830] Tue, 01 July 2008 23:38 Go to previous messageGo to next message
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
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 #331160 is a reply to message #330994] Wed, 02 July 2008 07:49 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
You have other DATEs represented as strings all throughout this query. Please remedy first.
Re: query with rank function [message #331296 is a reply to message #331160] Wed, 02 July 2008 23:27 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 Go to previous message
Frank
Messages: 7880
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 <
Previous Topic: Bulk collect for 8i
Next Topic: Index
Goto Forum:
  


Current Time: Mon Dec 05 08:41:52 CST 2016

Total time taken to generate the page: 0.06411 seconds