Start a new topic
Answered

IF Statement in SemQL for SQL Server

Is the IF function available in SemQL for SQL Server?  I now the CASE statement is and that is what I've used in the past.  I'm having to replicate a complicated If statement from excel into semarchy.  Using an If statement would simplify the syntax over a CASE statement.  The CASE statement isn't documented as a function so I was wondering if the IF statement was also available but not documented.  If so, what is the syntax?


Thanks,

Matt


Best Answer

Hello Matthew,


IF statement is not present but you can use CASE WHEN instead of IF which helps you to achieve your use case, also CASE function is present in the documentation.

For sample-


CASE
    WHEN condition_1 THEN result_1
    ...
    WHEN condition_n THEN result_n
    [ELSE default_result]
END

Answer

Hello Matthew,


IF statement is not present but you can use CASE WHEN instead of IF which helps you to achieve your use case, also CASE function is present in the documentation.

For sample-


CASE
    WHEN condition_1 THEN result_1
    ...
    WHEN condition_n THEN result_n
    [ELSE default_result]
END

My use case is that we are replacing a Google Sheet that is used to request a material creation. One of the fields in the sheet is "Status".  The formula on that cell is pretty complex and I need to replicate it in Semarchy.  Below is the example formula.  A CASE statement would be extremely complex.  Would this be a good example to use a SQL HOOK?


=IF(OR(L2<>"",M2<>"",N2<>"",O2<>"",P2<>"",Q2<>"",R2<>"",S2<>"",T2<>"",U2<>"",V2<>"",W2<>"",X2<>"",Y2<>"",Z2<>""),IF(OR(AND(OR(M2="Label",M2="Shrink Band",M2="Other Material",M2="Sticker"),L2<>"",O2<>"",P2<>"",Q2<>"",R2<>"",S2<>""),AND(OR(M2="FG SKU",M2="WIP SKU"),L2<>"",O2<>"",P2<>"",Q2<>"",R2<>"",S2<>"",W2<>"",X2<>"")),IF(S2="New","Complete",IF(T2<>"","Complete","Incomplete")),"Incomplete"),"")


P

hello Matthew,

here would be the SQL Server compatible formula. I don't feel it is that much complex but hopefully this works anyways.

CASE 
  WHEN 
    L2 <> '' OR M2 <> '' OR N2 <> '' OR O2 <> '' OR P2 <> '' OR Q2 <> '' OR 
    R2 <> '' OR S2 <> '' OR T2 <> '' OR U2 <> '' OR V2 <> '' OR W2 <> '' OR 
    X2 <> '' OR Y2 <> '' OR Z2 <> ''
  THEN
    CASE 
      WHEN 
        (
          (M2 IN ('Label', 'Shrink Band', 'Other Material', 'Sticker') AND 
           L2 <> '' AND O2 <> '' AND P2 <> '' AND Q2 <> '' AND R2 <> '' AND S2 <> '')
          OR
          (M2 IN ('FG SKU', 'WIP SKU') AND 
           L2 <> '' AND O2 <> '' AND P2 <> '' AND Q2 <> '' AND R2 <> '' AND S2 <> '' AND W2 <> '' AND X2 <> '')
        )
      THEN
        CASE 
          WHEN S2 = 'New' THEN 'Complete'
          WHEN T2 <> '' THEN 'Complete'
          ELSE 'Incomplete'
        END
      ELSE 'Incomplete'
    END
  ELSE ''
END

  Stéphanie.

Login to post a comment