I have a local table which includes a number of columns. Some of these colums are full, some empty They consist of metadata on other tables such as:
SCHEMA TABLE_NAME DRIVER_TABLE JOIN_COND_1 JOIN_COND_2
Here's an example of what some dummy data may look like:
SCHEMA TABLE_NAME DRIVER_TABLE JOIN_COND_1 JOIN_COND_2 BOB TRUCKS NULL NULL NULL BOB VANS USER.XXX A.ID=B.ID NULL BOB CARS USER.XXX A.ID=B.ID B.END_DTE >= '01-DEC-2018'
The reason the table exists is to create a means to count rows in another database.
I need to come up with a means by which I can obtain output like the following using the table:
SCHEMA TABLE_NAME COUNT BOB TRUCKS 878908 BOB VANS 7899 BOB CARS 876
The metadata table will be the source of the query, but the query should be in one block of code. I'm not sure where to begin.
The driver table is a list of id's to limit the count and is only to be used if it's there.
So, the first row, where no "driver table" is listed, just a NULL would be simply:
Select SCHEMA, TABLE_NAME, count(*) COUNT from METADATA;
The second query would have to use the driver table and join:
Select SCHEMA, TABLE_NAME, count(*) COUNT from METADATA A, USER.XXX B WHERE A.ID=B.ID;
And the third query would be:
Select SCHEMA, TABLE_NAME, count(*) COUNT from METADATA A, USER.XXX B WHERE A.ID=B.ID AND B.END_DTE >= '01-DEC-2018';
So, all of this sql is to be built from what is in the metadata columns.
I had thought of some sort of group of loops based to get the columns into variables, and then form an if, then, else logic.
DECLARE l_sql_1 VARCHAR2(100); l_sql_2 VARCHAR2(100); l_sql_3 VARCHAR2(100); l_sql_4 VARCHAR2(100); BEGIN l_sql_1 := 'SELECT SCHEMA||'.'||TABLE_NAME FROM METADATA'; l_sql_2 := 'SELECT DRIVER_TABLE FROM METADATA'; l_sql_3 := 'SELECT JOIN_COND_1 FROM METADATA'; l_sql_4 := 'SELECT JOIN_COND_2 FROM METADATA';
Does anyone have any ideas on how I might accomplish this please?