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
------------------------------
Original Message:
Sent: 06-19-2020 10:13 AM
From: Prasad Raghu
Subject: Join tables from different databases on data painter
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
Original Message:
Sent: 06-19-2020 01:35 AM
From: P K
Subject: Join tables from different databases on data painter
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!
Original Message:
Sent: 06-18-2020 12:26 AM
From: Prasad Raghu
Subject: Join tables from different databases on data painter
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