Conditionally use CASE...WHEN - Oracle SQL

Question

I have two tables like so:

tblOrders: OrderNo (pk), CurrentStepNo (fk)

tblSteps: StepNo (pk), OrderNo (fk), StepName, StepType, StepStart, StepStop

tblOrders contains tons of information about our sales orders, while tblSteps contains tons of information regarding the proper sequential steps it takes to build the material we are selling.

I am trying to construct a query that follows this logic:

"For all orders, select the current step name from the step table. If the Step Type is equal to 'XO', then select the most recently completed (where StepStop is not null) regular step (where StepStop is equal to 'YY')"

I have the following query:

SELECT
    tblOrders.*,
    tblSteps.StepName    
FROM
    tblOrders
        INNER JOIN tblSteps 
                ON tblOrders.OrderNo = tblSteps.OrderNo
               AND tblOrders.CurrentStepNo = tblSteps.StepNo

Which successfully returns to me the current step name for an in-process order. What I need to achieve is, when the tblOrders.CurrentStepNo is of type 'XO', to find the MAX(tblSteps.StepStop) WHERE tblSteps.StepType = 'YY'. However, I am having trouble putting that logic into my already working query.

Note: I am sorry for the lack of sample data in this example. I would normally post but cannot in this instance. This is also not a homework question.

I have reviewed these references:

Case in Select Statement

https://blogs.msdn.microsoft.com/craigfr/2006/08/23/subqueries-in-case-expressions/

But no luck so far.

I have tried this:

SELECT
    tblOrders.*,
    CASE
        WHEN tblSteps.StepType = 'XO' THEN (-- Some logic here)
        ELSE tblSteps.StepName
        END AS StepName    
 FROM
    tblOrders
        INNER JOIN tblSteps 
                ON tblOrders.OrderNo = tblSteps.OrderNo
               AND tblOrders.CurrentStepNo = tblSteps.StepNo

But am struggling to properly formulate the logic

score 2 · Accepted Answer · answered Sep 30 '19 at 20:15

2

Join all steps, rank them with ROW_NUMBER, and stay with the best ranked:

select *
from
(
  select
    o.*,
    s.*,
    row_number() over 
      (partition by o.orderno
       order by case when s.steptype <> 'XO' and s.stepno = o.currentstepno then 1
                     when s.steptype <> 'YY' then 2
                     else 3 end, s.stepstop desc nulls last) as rn
  from tblorders o
  join tblsteps s on s.orderno = o.orderno
) ranked
where rn = 1
order by orderno;

answered Sep 30 '19 at 20:15

Thorsten Kettner

89,309
7
49
73

I know I have not posted example data to test. How does this work when the `StepType` is not XO? I am confused as to how this solution accomplishes the requirements of above post, can you explain? – artemis Sep 30 '19 at 20:30
Look at the join. I join all its steps to an order. Then I number these steps per order (with `row_number() over (partition by o.orderno`). If there is a step with a steptype <> 'XO' matching the currentstepno it gets #1. Next come steptype 'YY' rows. I number these sorted by stepstop descending. So the highest one gets the next number (which is #1 or #2). At last I keep only #1s. – Thorsten Kettner Sep 30 '19 at 20:38

Conditionally use CASE...WHEN - Oracle SQL

1 Answers1