Conditionally use CASE…WHEN – Oracle SQL

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

Answer

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;

Advertisement

Answer