[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Add case when to row_number over partition clause - ORACLE 11g

Posted on 2012-08-24
7
Medium Priority
?
1,567 Views
Last Modified: 2012-08-28
I realize I've posted a similar question and the solution worked great.  However, there's another component that was added to input.

The solution was:  select title, status
  from ( select title, status, row_number() over (partition by p1_id order by nvl(complet_date, sysdate) desc) rn
   from your_input_table
  ) sq
where sq.rn = 1

My question is how to I add to the row_number clause the following  
order by case dept = current department then 1 case select count(1) from input where dept = current = 0 then 1 else 2,nvl(complete_date, sysdate) desc)

See the input and results below:

INPUT:
P_ID   DEPT                  TITLE    COMPLETE_DATE    STATUS
1        CURRENT           TASK1    01/01/2011             COMPLETED
1        CURRENT            TASK1                                  IN_PROGRESS

P1_ID   DEPT                 TITLE    COMPLETE_DATE   STATUS
1          CURRENT           TASK2                                  ASSIGN


PI_ID   DEPT                   TITLE    COMPLETE_DATE      STATUS
 1        CURRENT             TASK3     01/01/2012             CLOSE-OUT
 1        CURRENT             TASK3     07/23/2012             COMPLETED


PI_ID   DEPT                   TITLE    COMPLETE_DATE      STATUS
 1        CURRENT             TASK4     01/01/2012             ACTIVE
 1        PREVIOUS            TASK4     07/23/2012             CHECKED_OUT

PI_ID   DEPT                   TITLE    COMPLETE_DATE      STATUS
 1        PREVIOUS            TASK5     01/01/2012             EXPIRED
 1        PREVIOUS            TASK5     07/23/2012             ACTIVE

RESULT:
TASKS1     IN_PROGRESS
TASK2       ASSIGN
TASK3      COMPLETED
TASK4       ACTIVE
TASK5       ACTIVE
0
Comment
Question by:cookiejar
  • 5
  • 2
7 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 38330060
I'm not entirely sure what your case was supposed to say
but this produces the requested results.
I didn't put an order by on the final results, but if you need them sorted by title
simply add

order by title

to the end
ee.txt
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38330093
I think I figured it out.  you're trying to sort the dept values so CURRENT comes before PREVIOUS by assigning each a number and you're checking to see there are more than one dept for each title to force current to 1 and previous to 2

No need for all that complication

ORDER BY dept ASC  is sufficient to do that for you.

If you only have current the order by does nothing
if you only have previous the order by does nothing
if you have both current and previous, current is ordered first

after evaluating that, the date is then used as in the original query
0
 

Author Comment

by:cookiejar
ID: 38330282
I am sorry, I used current and previous as pseudcode, shoudn't have been a value in the input.  


Personnel Record shows the current dept.

P_ID   DEPT        
  1      01034

Personnel Training Record
 PI_ID   DEPT                   TITLE     COMPLETE_DATE      STATUS
 1         01034                   TASK4     01/01/2012               ACTIVE
 1         02341                   TASK4     07/23/2012              CHECKED_OUT

PI_ID   DEPT                   TITLE    COMPLETE_DATE      STATUS
 1        02341                   TASK5     01/01/2012             EXPIRED
 1        02341                   TASK5     07/23/2012             ACTIVE

In example 1 for TASK4, there is  personnel current assigned department (01034) set that record to 1, 02341 to 2

In example 2  TASK5, no current assigned deparment exists, only the previous, set both to 1

I have the current department value stored in variable in the stored procedure

I would like to pass it into the row_number clause into the sql statement.  Maybe you have a better solution.

select title, status
  from ( select title, status, row_number() over (partition by p1_id order by nvl(complet_date, sysdate) desc) rn
   from your_input_table
  ) sq
where sq.rn = 1
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 38330351
sorry, I misunderstood,  so assuming your "current" department is in a variable called
v_dept

try the attached.

you still don't need to do a count or anything complicated like that,
but numbering them does work.
ee.txt
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38330360
but,  given the data above, the original query should still work because

01034 < 02341

so it will still sort first and produce the expected results
0
 

Author Comment

by:cookiejar
ID: 38330505
but,  given the data above, the original query should still work because

01034 < 02341

so it will still sort first and produce the expected results

The department assignment will not always be numeric may be a combination of numbers and letters.

-----------------------------
CASE WHEN dept = v_dept THEN 1 ELSE 2 END
This will handle if there is a current and previous dept.
How would I handle if the records contain only the previous dept then rn =2?
As a result, no data will be returned  (WHERE sq.rn = 1)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38330521
>>> How would I handle if the records contain only the previous dept

the case will work just fine as is

if it doesn't, post sample data and expected results
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses
Course of the Month19 days, 1 hour left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question