SQL Multiple IF statements based on conditions-CodePudding

What would be the best method in Oracle SQL to calculate values and store them based on the values of existing columns and other conditions.

For example:

If column 1 has a value of =>3 then the result should default to the value of the column.

However, if column 1 has a value of <3 and column 2 is populated with a value of 1 and column 3, column 4 and column 5 all have values then the value should be defaulted to 2

if column 1 has a value < 3 and column 2 is populated with a value of 1 and column 4, column 5 have values but column 3 doesn't then it's defaulted to a value of 2

if column 1 has a value < 3 and column 2 is populated with a value of 1 and column 3 and column 4 have values but column 5 doesn't then it's defaulted to a value of 1 ... etc for all scenarios for columns 3,4,5 being populated

if column 1 has a value < 3 and column 2 is populated with a value of 2 and column 3, column 4 and column 5 all have values then the value should be defaulted to 3 etc etc for all scenarios.

Just want to know the easiest way to go about formulating it as there's numerous conditions (19 conditions to be exact)

CodePudding user response：

Aside from your general discretion, I would use a case when and put your first critieria first, then keep going with subsequent next priority conditions.

-- column1 is >= 3, use it as first.
case when column1 >= 3
          then column1
-- if it gets this far, then column1 is LESS than 3
     when column1 < 3
      AND column2 = 1
      AND column3 = column4 
      AND column3 = column5
     then column2

    when column1 < 3
      AND column2 = 1
      etc
    then ...
    else ... last choice
end

Keep WHEN conditions in the priority that they should be processed.

CodePudding user response：

I can't tell what some of your conditions are ("etc etc"), but some of them (like 2 and 3) are identical. I doubt you have 19 conditions.

You'll need to know which conditions have precedence.

Here's the basic code structure for a query based on what you have provided.

select
  case
    when col1 >= 3 then col1
    else 
      case
        when col2 = 1 and not null in (col4, col5) then 2
        when col2 = 1 and not null in (col3, col4) and col5 is null then 1
        when col2 = 2 and not null in (col3, col4, col5) then 3
        else null -- col2 not in (1,2)
      end
  end

Storing them would be something like:

insert into my_table (mycolumn)

select
  case
    when col1 >= 3 then col1
    else
      case
        when col2 = 1 and not null in (col4, col5) then 2
        when col2 = 1 and not null in (col3, col4) and col5 is null then 1
        when col2 = 2 and not null in (col3, col4, col5) then 3
        else null -- col2 not in (1,2)
      end
  end

CodePudding user response：

Be carefull using CASE expressions as it works sequentialy and exits at first TRUE result. So, the ordering of the conditions is crucial.

WITH
    tbl (C1, C2, C3, C4, C5) AS
        (
            Select 1, 1, 3, 4, 5 From Dual Union All
            Select 1, 1, 3, 3, 5 From Dual Union All
            Select 1, 1, 3, 3, Null From Dual Union All
            Select 2, 2, 3, 3, 5 From Dual Union All
            Select 2, 2, 3, Null, Null From Dual Union All
            Select 2, 2, Null, Null, Null From Dual Union All
            Select 4, 2, 3, 3, 5 From Dual
        )
Select t.*,
    CASE 
        WHEN C1 >= 3 THEN C1
    ELSE
        CASE 
            WHEN C2 = 1 And     C3 Is Not Null And      C4 Is Not Null And      C5 Is Not Null THEN 2
            WHEN C2 = 1 And     C3 Is Null And          C4 Is Not Null And      C5 Is Not Null THEN 2
            WHEN C2 = 1 And     C3 Is Not Null And      C4 Is Not Null And      C5 Is Null THEN 1
            WHEN C2 = 1 And     C3 Is Not Null And      C4 Is Null And          C5 Is Null THEN 1
            --
            WHEN C2 = 2 And     C3 Is Not Null And      C4 Is Not Null And      C5 Is Not Null THEN 3
            WHEN C2 = 2 And     C3 Is Null And          C4 Is Not Null And      C5 Is Not Null THEN 3
            WHEN C2 = 2 And     C3 Is Not Null And      C4 Is Not Null And      C5 Is Null THEN 2
            WHEN C2 = 2 And     C3 Is Not Null And      C4 Is Null And         C5 Is Null THEN 2
        ELSE 
            -1
        END
    END "RESULT"
From tbl t
Order By 1

R e s u l t :
        C1         C2         C3         C4         C5     RESULT
---------- ---------- ---------- ---------- ---------- ----------
         1          1          3          4          5          2 
         1          1          3          3          5          2 
         1          1          3          3                     1 
         2          2          3          3          5          3 
         2          2          3                                2 
         2          2                                          -1 
         4          2          3          3          5          4

Result -1 means that there was no matching condition