Pushdown_SQL not relaying complete SQL to Teradata

dataservices job design
1

Hello everyone,
Teradata (TD henceforth) is throwing a syntax error when using pushdown_sql( ‘DataStore’,’{$GV_WHERE_STMT}’) in the WHERE tab of a query transform. Reason being, TD is not receiving the complete SQL query from DS. Ex: $GV_WHERE_STMT = ‘MY FILTER’;
Error:
5336 7164 DBS-070404 |Data flow XYZ|Reader MNO
5336 7164 DBS-070404 SQL submitted to ODBC data source resulted in error <[Teradata][ODBC Teradata Driver][Teradata Database] Syntax
5336 7164 DBS-070404 error, expected something like a ‘SUCCEEDS’ keyword or a ‘MEETS’ keyword or a ‘PRECEDES’ keyword or an ‘IN’ keyword or a
5336 7164 DBS-070404 ‘CONTAINS’ keyword between the word ‘M’ and ‘;’. >. The SQL submitted is <INSERT INTO BLAH BLAH where M>.

Whatever you put in the global variable, gets chopped after the FIRST character, meaning this is reproducible, at least in our environment. And my need to use pushdown_SQL is justified by the fact that my company does not prefer using ‘SQL Transform’ box, and the where filter is a IN (’’,’’,’’) clause, and the values need to be changeable. Generated optimized SQL looks fine, of course with the GV. No other SQL query, either from a SQL() function in a script or a SQL Transform box is doing this, just the pushdown.

DS Designer: 14.2.3.549
TD: 14.10.06.06

Your time & help is greatly appreciated.

Thanks,
Pavan

kumarbop (BOB member since 2011-01-18)

2

Did you try back slashing the quotes? What about using a literal instead of a variable?

  • E

eepjr24 :us: (BOB member since 2005-09-16)

3

Tried back-slashing, yes. Literal works fine.
Only pushdown with variable is the problem.

Thanks,
Pavan

kumarbop (BOB member since 2011-01-18)

4

Sorry, I was unclear. Did you try using a string literal inside the pushdown_sql for troubleshooting purposes? If that worked, did you try adding the back slashes inside the variable?

  • E

eepjr24 :us: (BOB member since 2005-09-16)

5

Yes & yes. Replacing variable with a string passes it along to the DB just fine.
You were clear the first time around too :slight_smile:

Thanks,
Pavan

kumarbop (BOB member since 2011-01-18)

6

If it is that easily reproducible it sounds like a bug. Ticket with SAP?

  • E

eepjr24 :us: (BOB member since 2005-09-16)

7

Ticket is in the pipeline. Any temporary workaround would be great though.

Thanks,
Pavan

kumarbop (BOB member since 2011-01-18)