4 Replies Latest reply: Mar 19, 2012 5:41 AM by Celambarasan Adhimulam RSS

    Inner Join (tricky one)

    Przemysław Wojda

      Hi All,

       

      I need to inner join two tables: DBS and ACC_TYPE where ACC_TYPE gets data from databases that are listed in DBS. Please take a look at the below code and suggest.

       

      Thank you,

      Przemek

       

       

      DBS:
      SELECT DB_Name FROM Configuration;
      
      
      
      ACC_TYPE:
      LET LiczbaBaz = NoOfRows('DBS');
      FOR i=0 TO $(LiczbaBaz)-1
      LET DB_Name = Peek('DB_Name', $(i), 'DBS');
      SELECT
      '$(DB_Name)' AS DB_Name, 
      CASE WHEN Dim_1 = 1 THEN 'ER' WHEN Dim_1 = 2 THEN 'KP' WHEN Dim_1 = 3 THEN 'KH' END AS AccTypeName
      FROM CDN_$(DB_Name).CDN.Company WHERE Dim_1 = 3;
      NEXT
      
      
        • Re: Inner Join (tricky one)
          Jonathan Dienst

          Przemek

           

          This should work:


          DBS:
          SELECT DB_Name FROM Configuration;
          
          
          LET LiczbaBaz = NoOfRows('DBS');
          FOR i=0 TO $(LiczbaBaz)-1
                    LET DB_Name = Peek('DB_Name', $(i), 'DBS');
          
                    ACC_TYPE:
                    SELECT
                    '$(DB_Name)' AS DB_Name, 
                    CASE WHEN Dim_1 = 1 THEN 'ER' WHEN Dim_1 = 2 THEN 'KP' WHEN Dim_1 = 3 THEN 'KH' END AS AccTypeName
                    FROM CDN_$(DB_Name).CDN.Company WHERE Dim_1 = 3;
          NEXT
          
          

           

          Regards

          Jonathan