Oracle Analytics Forum

Home› Oracle Analytics and AI› Oracle Analytics and AI Forums› Oracle Analytics Forums› Oracle Analytics Forum

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Nested Statements

Received Response
21
Views
1
Comments
User_XLBIE
User_XLBIE Rank 1 - Community Starter
edited Aug 13, 2024 4:58PM in Oracle Analytics Forum

I have a column with a custom title of "Estimated ship date" but with the formula

CASE WHEN "Order Detail - Oe Order Release"."Revised EST Ship Date" IS NULL THEN "Order Detail - Oe Order Release"."Original EST Ship Date" ELSE "Order Detail - Oe Order Release"."Revised EST Ship Date" END

Is it possible to add a condition that leaves the value as Null if the resulting date is before the current date? I know how to use the >date - 1

My initial thought was to create a new column using another CASE WHEN statement but when I try to insert the column it fills in the whole

CASE WHEN "Order Detail - Oe Order Release"."Revised EST Ship Date" IS NULL THEN "Order Detail - Oe Order Release"."Original EST Ship Date" ELSE "Order Detail - Oe Order Release"."Revised EST Ship Date" END

statement but I get a bad column name error.

If I try to manually enter it just as "Order Detail - Oe Order Release"."Estimated Ship Date" I get the same error.

I feel like there must be an easier way to do this.

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi,

    Embedding multiple CASE WHEN works, if you got an error you should check the logic of what what happening.

    What you could also try is to simplify your first CASE WHEN because it can be replaced by the IFNULL() formula.

    A random guess of what you could try:

    CASE 
  WHEN IFNULL("Order Detail - Oe Order Release"."Revised EST Ship Date", "Order Detail - Oe Order Release"."Original EST Ship Date") >= CURRENT_DATE 
  THEN IFNULL("Order Detail - Oe Order Release"."Revised EST Ship Date", "Order Detail - Oe Order Release"."Original EST Ship Date")
END