I am trying to duplicate an existing workflow using nothing but In-DB tools, but i am having a hard time figuring out how to use the in-DB formula tool
could someone please help me figure out what i'm doing wrong? here is the formula i'm trying to replicate, but i cant get even the most simple concatenations to work..
if
[PNRLocatorID] = Null()
then
[BATCH_ID]+'|'+[IssueDate]+'|'+[Min_FlightDate]+'|'+[POO_Origin]+'|'+[TrueNDOD]+'|'+[AgencyCode]+'|'+[TotalTktCouponCount]
else
[PNRLocatorID]
endif
in-db formulas use SQL so you need to use CONCAT('Batch_ID','IssueDate) etc... Haven't used the IF much in In-Db - maybe an CASE WHEN THEN ELSE END syntax? Or just datastream out and in...
Hi @Matthew ,
There is difference from Formula tool expression and In-DB Formula tool expression.
In In-DB Formula tool, you should follow SQL expression.
For example, I made the table of how to express the field and value.
Field | Value(String) | |
Alteryx | [] : square blanket ex [Field1] | '' or "" : Single or Double quotation ex "apple" , 'apple' |
SQL | "" : Double quotation ex "Field1" | '' : Single quotation ex 'apple' |
This makes Alteryx users very confused.
And In SQL, we can't use IF function. We can use CASE WHEN function instead of it.
I think the following expression will work well.
CASE
WHEN "PNRLocatorID" IS NULL THEN Concat_WS('|',"BATCH_ID","IssueDate","Min_FlightDate","POO_Origin","TrueNDOD","AgencyCode","TotalTktCouponCount")
ELSE "PNRLocatorID"
END
I tried that expression at MS SQL Server. If your Database is not support some of the functions, it may not work.
inDB uses the SQL of the database engine that you're connecting to. I usually login to the database and write and test the formula there before pasting into Alteryx.
You can see from @AkimasaKajitani 's post - that might work and will work on MS SQL, for other engines the syntax might be different.