In-DB Nested IF Statement

[AW25]

Hello!

I have a SQL query that's pulling data In-DB and I'm trying to manipulate that data using the Formula In-DB tool. I'm trying to check if the line is a Negative Debit or Positive Credit and if so, multiply by -1. This is what I have but I'm getting a syntax error:

IF [RecordType]=C AND [Sum_Amount]>0

THEN (-1)*[Sum_Amount]

ELSEIF [RecordType]=D AND [Sum_Amount]<0
THEN (-1)*[Sum_Amount]

ELSE [Sum_Amount]

ENDIF

I've been trying to figure this out for longer than I'm willing to admit and I have no clue what to try next. I built the formula above partly based off the solution I found in this link:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Converting-a-value-to-negative-based-o...

[Inactive User · Accepted solution]

The reason you are getting a syntax error is because you are using square brackets instead of quotes. Use the field drop function to insert fields and then it should work. When you use In-DB you need to write the formulas and filters in the syntax of the DB not Alteryx. I am not sure which DB you are using but if you are unsure of the syntax going forward just look them up for that specific DB.

[AW25]

Thanks Ryan. Here is the tweaked expression that worked:

SELECT

CASE

WHEN RecordType = 'C' AND Sum_Amount > 0
THEN (-1)*Amount

WHEN RecordType = 'D' AND Sum_Amount < 0
THEN (-1)*Amount

ELSE Amount

END

What was odd is that when I did use the Insert Fields function, it inserted the variables with quotations like ''RecordType'' which didn't take. But in any case, problem solved!