In-DB formulas

[Matthew]

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

[apathetichell]

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...

[AkimasaKajitani]

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.

[cmcclellan]

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.