IF left, Length, Right Statement using INDB formula tool

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.

Workflow

Gallery

In Database

In-Database

Accepted answers

CarliE

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

All comments

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

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!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703