Join tables from different databases on data painter

[Prasad Raghu]

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 AA
INNER 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,

Prasad

[P K]

Hi Prasad,

I have tried something like below:

@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!

[Prasad Raghu]

Hi PK,

Actually I need to join the resultant set of (Select * from dbo.TABLE B) with more than one column. My bad that I did not mention it in the first place. Let me try to rewrite the function -
@seqlov(0,@sqllist(OracleSourceConnection,
SELECT *
FROM Table A AA
INNER JOIN (@sqllist(SQLSERVERCONNECTION,Select * from dbo.TABLE B)@) BB ON AA.COLUMN_X = BB.COLUMN_Y AND AA.COLUMN_Z = BB.COLUMN_J AND AA.COLUMN_S = BB.COLUMN_S)@,~COLUMN_NAME~)@

if I were to join with just one column then yes, i agree we could have used your logic where we can pass it in the where clause. But my requirement is to join with multiple columns. How could we resolve in this case?

Thanks,
Prasad

[Rajkumar Mansuria]

Hi Prasad,

I doubt that is possible and I don't see any function in Data Maker which can get the data from two sources and combine them.

I have a solution but it is preferable only if you have less data say around million. What you can do is as a pre-publish action using javelin flows get the entire table/Only Required fields only depending on size from 2 sources and place it in a single server(can be source/target/repository) in 2 separate tables and then apply the join condition where you have placed the data from both sources.

------------------------------
Thanks,
Rajkumar
------------------------------

[Prasad Raghu]

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.

------------------------------
Thanks,
Prasad
------------------------------