join data from different dbs

[cznex]

Hi guys

I need code in joining tables, here is the conditions I need to check

(If S.prod _id !=1
If S.prod_nm=x then join x1 where S.prod_id = x1.prod_id and S.dt= x1.dt)
And
(if s.prod_nm=y then join x2where S.prod_id=x2.prod_id and s.dt=x2.dt)

S= source data
X1 = db1.tb1
X2 = db2.tb2

And the result I get I need to insert into Z field in the temp table

Thanks guys in advance

[Fumigator]

Someone may has a magic solution for this but I really don't think one query can access data from two databases.

You can probably solve the challenge by selecting data from one database into an array (or file if we're talking millions of rows) and creating a temporary table in the other database, populating it, then using that temporary table in the join query.

[cznex]

SELECT
,CASE
WHEN S.prod_nm='x' and S.prod _id <> 1and S.prod_id = x1.prod_id and S.dt= x1.dt THEN x1.cd
WHEN S.prod_nm='y' and S.prod_id = x2.prod_id and S.dt= x2.dt THEN x2.cd
ELSE ' '
FROM db1.tb1 x1, db2.tb2 x2, source s;

Hi guys

Any sujections on this code

Thanks