Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 i;
Inner Join(ACC_TYPE)
Load
*
Resident
DBS;
Drop Table DBS;
Celambarasan
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
Hi Jonathan,
the only change you made is table name moved to SELECT statement... not working.
Regards,
Przemek
What's the error your are getting?
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 i;
Inner Join(ACC_TYPE)
Load
*
Resident
DBS;
Drop Table DBS;
Celambarasan