Hi,I would like to know how to perform an inner join between Table A (oracle) and Table B (SQL server) on the data painter window so that I can define the output of this joined query to a column of an empty table registered in data pool.@seqlov(0,@sqllist(OracleSourceConnection,SELECT *FROM Table A AAINNER JOIN (@sqllist(SQLSERVERCONNECTION,Select * from dbo.TABLE B)@) BB ON AA.COLUMN_X = BB.COLUMN_Y)@,~COLUMN_NAME~)@but the above doesn't work when I validate on Data painter window. What modification do i need to make to above that I can join those 2 tables?Thanks,
@seqlov(0,@sqllist(DB2Connectionprofile,Select ~COLUMN_NAME~ from db2Table AA where AA.COLUMN_X in (@seqlov(0,@sqllist(SQLSERVERConnectionprofile,Select distinct BB.COLUMN_Y from SQLSERVERtable BB)@)@))@)@This query works/gets validated in data painter but it did not give me performance as expected. You can give it a try!
Thank you Rajkumar! Appreciate your response. Actually this is exactly the workaround solution which I have implemented. However I wanted to know if there is a better approach since this solution is having lot of intermediate steps involved. I wanted to reduce the overall processing time by being able to join all the tables from different databases (Oracle & DB2) in a single query. If CA TDM doesn't support joining tables from different databases in a single query, then I assume we have to just go with the workaround solution.