Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Jan 2007
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    join data from different dbs

    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
    Last edited by cznex; 01-17-2007 at 10:25 PM.

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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.

  • #3
    New to the CF scene
    Join Date
    Jan 2007
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exclamation

    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •