User defined join in Informatica with DB2

0
votes

We are trying to replace source qualifier override with user defined joins and source filter.

for below user defined join in Informatica source qualifier:

{A INNER JOIN B ON a.dept_id= b.dept_id
b.load_date between 20170712174712000000 and 20170904152656000000
LEFT OUTER JOIN C ON a.emp_id =  c.emp_id}

for this I'm getting SQL query as

FROM A,B,C WHERE {A INNER JOIN B ON A.dept_id = B.dept_id
AND b.load_date between 20170712174712000000 and 20170904152656000000
LEFT OUTER JOIN C
ON  C ON a.emp_id =  c.emp_id}

I have tried replacing INNER JOIN in override query with NORMAL JOIN, as I saw it somewhere that informatica translates normal to inner join. The source database is DB2.

2
db2informaticainformatica-powercenter

2 Answers

1
votes

I don't know anything about informatica, but the resulting SQL syntax that you listed in the question is not valid for DB2. The biggest problem is that you have the JOIN in the WHERE clause rather than the FROM clause. Not real sure how to fix that in informatica though. Appropriate DB2 syntax though would be something like this:

FROM a
  INNER JOIN b ON a.dept_id = b.dept_id
  LEFT OUTER JOIN c ON a.emp_id = c.emp_id
WHERE
  b.load_date BETWEEN 20170712174712000000 and 20170904152656000000

This assumes that b.load_date is not a timestamp field. If you are using a timestamp field, the format for the timestamps should be '2017-07-12 17:47:12.000000'

0
votes

The T-SQL should be used in FROM clause not in the WHERE clause. Hence use:

FROM A INNER JOIN B ON a.dept_id = b.dept_id
       LEFT OUTER JOIN C ON a.emp_id = c.emp_id