IF left, Length, Right Statement using INDB formula tool

[Evoz24]

Hello I am trying to run this statement in a  INDB Formula tool but keep receiving the following error, does anyone have any suggestions on what I may need to correct?

IF LEFT(["SN"],3)="CAT" AND Length(["SN]")=17 THEN RIGHT(["SN"],8) ELSE ["SN"] ENDIF

Error: Formula In-DB (167): Error SQLPrepare: SQL compilation error:syntax error line 4 at position 4,044 unexpected '('.

Thank you.

[Luke_C]

Hi @Evoz24 

Formulas need to be written in the syntax that corresponds to the database. The Alteryx syntax won't work in a SQL database.

[CarliE · Accepted solution]

Hi @Evoz24 ,

what you would want to do is a SQL CASE statement for this 

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN condition3 THEN result3
    ELSE result
END

Thanks

Carli

[Evoz24]

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN condition3 THEN result3
    ELSE result
END

Worked great but also used

SELECT IFF(SN LIKE 'CAT%' AND LENGTH(SN) = 17, RIGHT(SN,8), SN)

Thank you for the responses!