Test Data Manager

Expand all | Collapse all

Join tables from different databases on data painter

Jump to Best Answer
  • 1.  Join tables from different databases on data painter

    Posted 06-18-2020 02:39 AM

    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



  • 2.  RE: Join tables from different databases on data painter

    Posted 06-19-2020 01:35 AM
    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!




  • 3.  RE: Join tables from different databases on data painter

    Posted 06-19-2020 10:13 AM
    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



  • 4.  RE: Join tables from different databases on data painter
    Best Answer

    Posted 06-23-2020 11:09 AM
    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
    ------------------------------



  • 5.  RE: Join tables from different databases on data painter

    Posted 06-23-2020 12:18 PM

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