In-DB Errors (Help)

[MPCNA]

Can't figure out how to run the filters and formulas needed In-DB.

Errors: 

Filter Tool - ORA-00933 Command not properly ended

Formula Tool - ORA-00972 Identifier is too long

[Inactive User]

Keep in mind the formula for In-DB (and filter) need to use the source DB syntax. It cannot be the same as in a standard Alteryx workflow. If this is Oracle, look up the Oracle syntax for the formula you are trying to do and apply it that way.

[MPCNA]

Can you better explain or give an example of what you mean by Syntax in this situation?

[Inactive User]

Alteryx:

[Date] = '1999-12-31'

Oracle:

"Date" = '1999-12-31'

They are different and depending the function the syntax can be vastly different.

[jrgo · Accepted solution]

@MPCNA,

Date conditions to Oracle using InDB can be a pain in the @$$ sometimes. Depending on what the default date format is set up on the server may require you to define your date string.

For your filter, try using this statement

"End" >= TO_DATE('2017-01-01','YYYY-MM-DD')

For your formula, there's four issues that I can see. 1st, you're attempting to modify an existing field which is already defined as a NUMERIC (Double) field type. Your output, however, would create a string. 2nd, to concatenate, it's not the same as Alteryx by simply using a + sign. 3rd, you'll need to CAST "Cross Reference Code" to a varchar in order to concatenate. This would have also returned an error in Alteryx. 4th, in SQL, it's more common (maybe required?) to use a CASE statement.

Below is what I believe should work... note that you should have this CREATE a new fields and set the type to one of the STRING types

CASE 
WHEN LENGTH("Cross Reference Code") = 3 THEN CONCAT('00',CAST("Cross Reference Code" AS varchar(30))
WHEN LENGTH("Cross Reference Code") = 4 THEN CONCAT('0',CAST("Cross Reference Code" AS varchar(30))
ELSE CAST("Cross Reference Code" AS varchar(30))
END

Hope this helps!

Jimmy