CODEWITHSUNDEEP
sqloracle-databasecaseoracle12c
Random guy
Random guy

Reputation: 923

Error in order clause of partition by when using multiple case statement

I want to use multiple case statement inside order by clause in partition by statement. I have many columns so ,I am only posting the required one.

I have table customers which has:

Select
   name,
   ROW_NUMBER() OVER(PARTITON BY lastname, rollno 
ORDER BY
   CASE
      WHEN
         NVL(gender, address) IS NULL 
      then
         a.effdate desc 
      else
         CASE
            WHEN
               NVL(a.postoffc, a.mon) <= file.effdate 
            then
               file.effdate 
            else
               a.postoffc 
         END
         desc, NVL(l4.covcode, a.pass) 
   end
)
rn 
from
   customers a;

If,i remove these case statement then my join with other tables and query is working fine.So,there is no problem in join statement or any other logic.The problem I get is when i used multiple case statement.I think my syntax is mistake.Please tell me how can solve this error.I need this case statement logic as mandatory.

Upvotes: 0

Views: 348

Answers (1)

GMB
GMB

Reputation: 222482

You need to close the second CASE block before you declare the other ordering criteria. Also, you have an unwanted DESC within the expression, that should be placed after it.

ORDER BY
    CASE
        WHEN NVL(gender, address) IS NULL THEN a.effdate
        ELSE CASE
            WHEN NVL(a.postoffc, a.mon) <= file.effdate THEN file.effdate 
            ELSE a.postoffc 
        END
    END desc,  --> here
    NVL(l4.covcode, a.pass)

But overall, I don't think that you need to nest the case expressions. This should work equally well, and is easier to follow:

ORDER BY
    CASE
        WHEN NVL(gender, address) IS NULL THEN a.effdate
        WHEN NVL(a.postoffc, a.mon) <= file.effdate THEN file.effdate 
        ELSE a.postoffc 
    END desc, 
    NVL(l4.covcode, a.pass)

Upvotes: 2

Related Questions