SQL Editor syntax and functions

[jason_scarlett]

I want to execute the following SQL code in an Input Data Tool.

select substr(
members, 
instr(';'||members,';',1,seq),
instr(';'||members||';',';',1,seq+1) - instr(';'||members,';',1,seq)-1)  members
from "CData"."ActiveDirectory"."Group",(select level seq from dual connect by level <= 100) seqgen
where instr(';'||members,';',1,seq) > 0

But when I paste it into the SQL Editor I get this error:

Error: Input Data (1): Error SQLPrepare: Malformed SQL Statement: Expected token ')' but found 7 with value '||' instead at position 76.
Statement:select substr(
members, 
instr(';'||members,';',1,seq),
instr(';'||members||';',';',1,seq+1) - instr(';'||members,';',1,seq)-1) members
from "CData"."ActiveDirectory"."Group",(select level seq from dual connect by level <= 100) seqgen
where instr(';'||members,';',1,seq) > 0

Is this an Alteryx limitation or syntax problem or because of the ODBC driver I am using?

When I simplify it to just a simply substr() function I also get an error. Is there a list of supported functions?

[adm510 · Accepted solution]

Does that query work when you run it outside of Alteryx?  From the query, it looks as if you're using a CData connector which uses substring instead of substr.  I'm also not sure if they support any of your other functions like instr.

http://cdn.cdata.com/help/CDC/odbc/pg_sqlstringfunctions.htm

[jason_scarlett]

Thanks.

Looks like I need to do some more work converting it from the original in Oracle.