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?
Solved! Go to 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
Thanks.
Looks like I need to do some more work converting it from the original in Oracle.