Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
there's a configurational database that stores all database names that I need to select. All these databases have identical structure. Within these databases there's a table (Settings) with a dim (Dimension) upon which I need to decide whether I will select or not table Data within these databases. The table Settings doesn't have any dimension to join it with table Data. Please see the below code and suggest what should I do to select only the dbs where Dimension = 1.
Table_1:
SELECT DB_Name FROM DB_Conf;
Table_2:
LET A = NoOfRows('Table_1');
FOR i=0 TO $(A)-1
LET B = peek('DB_Name',$(i),'DZ_Bazy');
LET C = peek('DB_Name',$(i),'DZ_Bazy')&'.CDN.Settings';
SELECT
'$(B)' AS DB_Name,
Dimension
FROM $(C);
NEXT
Table_3:
LET A = NoOfRows('Table_1');
FOR i=0 TO $(A)-1
LET B = peek('DB_Name',$(i),'DZ_Bazy');
LET C = peek('DB_Name',$(i),'DZ_Bazy')&'.CDN.Data';
SELECT
'$(B)' AS DB_Name,
*
FROM $(C);
NEXT
Thanks,
Przemek
Hi Przemek,
what if you do something like this:
Table_1:
SELECT DB_Name FROM DB_Conf;
Table_2:
LET A = NoOfRows('Table_1');
FOR i=0 TO $(A)-1
LET B = peek('DB_Name',$(i),'DZ_Bazy');
LET C = peek('DB_Name',$(i),'DZ_Bazy')&'.CDN.Settings';
SELECT
'$(B)' AS DB_Name,
Dimension
FROM $(C);
NEXT
left join (Table1)
load *
resident Table2;
drop Table2;
Table_3:
LET A = NoOfRows('Table_1');
FOR i=0 TO $(A)-1
LET B = peek('DB_Name',$(i),'DZ_Bazy');
LET FlgDimension = peek('Dimension',$(i),'DZ_Bazy');
if($(FlgDimension) = 1) then
LET C = peek('DB_Name',$(i),'DZ_Bazy')&'.CDN.Data';
SELECT
'$(B)' AS DB_Name,
*
FROM $(C);
end if
NEXT
Hi Fernando,
seems like a good path but the script doesn't read the below bit - the FOR...NEXT loop goes only for s only for if($(FlgDimension) = 1). Any ideas what to change?
Omitted bit:
LET C = peek('DB_Name',$(i),'DZ_Bazy')&'.CDN.Data';
SELECT
'$(B)' AS DB_Name,
*
FROM $(C);
Regards,
Przemek
Hi Przemek,
I did not understand you.
In your original post, you said you needed to 'select only the DBs where Dimension = 1'. And in your last post, you said that 'the for..next loop goes only for if(...)'. The idea is that it will only run the selectto the DB when Dimension = 1. Isn't that what you need?
Regards,
Fernando
I need to select DBs where Dimension = 1 to know from which DBs Data will be taken from. Script for Table_3 is supposed to select the Data from the given DBs but it omits the bit:
LET C = peek('DB_Name',$(i),'DZ_Bazy')&'.CDN.Data';
SELECT
'$(B)' AS DB_Name,
*
FROM $(C);
Hi,
wht if you modify it to:
Table_1:
SELECT DB_Name FROM DB_Conf;
Table_2:
LET A = NoOfRows('Table_1');
FOR i=0 TO $(A)-1
LET B = peek('DB_Name',$(i),'DZ_Bazy');
LET C = peek('DB_Name',$(i),'DZ_Bazy')&'.CDN.Settings';
SELECT
'$(B)' AS DB_Name,
Dimension
FROM $(C);
NEXT
inner join (Table1)
load *
resident Table2 where Dimension = 1;
drop Table2;
Table_3:
LET A = NoOfRows('Table_1');
FOR i=0 TO $(A)-1
LET B = peek('DB_Name',$(i),'DZ_Bazy');
LET C = peek('DB_Name',$(i),'DZ_Bazy')&'.CDN.Data';
SELECT
'$(B)' AS DB_Name,
*
FROM $(C);
NEXT
The trick is that you first prepare the Table_1, with the Inner Join to filter only the DBs you need.
Hi Fernando,
that's a very nice trick! The problem is that Dimension is not recognised when put in this place. I've tried to split the inner join part to "LOAD DB_Name, Dimension Where Dimension = 1" before Table_3 and "Inner Join (DZ_Bazy) LOAD * Resident DZ_Bazy; Drop Table Table_2;" after Table_3 but it keeps selecting both DBs...
Any idea now?