Join query failing in Informatica SQL transformation

I have a SQL query to which am passing parameters from expression transformation:

Eg:

SELECT A.id
FROM table1 A, table2 B
WHERE A.id = B.id


This works fine and am able to get the output.

Now when I modify this sql to :

select DR.id from(select A.id from table1 A, table2 B where A.id = B.id)DR
left outer join table3 C on Dr.id = c.col1


Then it throws ODL Error -

ERROR TRANSF_1_1_1_1 pmsql_50065 [ERROR] ODL error:
FnName: Bind Parameter -- [Informatica][ODBC PWX Driver] PWX-00264 DBAPI Error Initial "Describe" CONVERSE failed to location ,;DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD LEFT. TOKEN , FROM;
INTO WAS EXPECTED;DSNT418I SQLSTATE = 42601 ;DSNT415I SQLERRP = DSNHPARS ;DSNT416I SQLERRD = 2 0 0 -1 129 506 ; Database driver error...
parameter binding failed.

The sql query when executed from sql assistant works fine. So could you suggest as how to write query in Informatica SQL transformation.

Thanks!

1 Answer
1

there is no column in your sub-query DR name 'col1' so make it 1st then use it

select DR.col1
from
(select A.id as col1 from table1 A join table2 B on
A.id = B.id
) DR
left outer join table3 C on Dr.col1 = c.col1


Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.