CODEWITHSUNDEEP
sqloracle-databaseplsql
Scouse_Bob
Scouse_Bob

Reputation: 546

Building a query from columns in a table

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?

Upvotes: 2

Views: 82

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

You can use dynamic sql as

SQL> set serveroutput on;
SQL> declare
 v_sql    varchar2(4000);
 v_schema varchar2(40);
 v_table  varchar2(40); 
 v_count  pls_integer;
begin
  dbms_output.put_line('schema  table   count');                       
  dbms_output.put_line('------  -----   -----');
 for c in ( select * from metadata )
 loop
   v_schema := c.schema;
   v_table  := c.table_name;
   v_sql := 'select count(*) from '||c.table_name||' a ';
  if c.driver_table is not null then
   v_sql := v_sql ||' join '||c.driver_table||' b on a.id=b.id ';
    if c.join_cond_2 is not null then
     v_sql := v_sql ||' where '||c.join_cond_2;     
    end if;   
  end if;
  execute immediate v_sql into v_count;
  dbms_output.put_line(v_schema||'     '||v_table||'    '||v_count);
 end loop;   
end;

Upvotes: 2

Mark Moretto
Mark Moretto

Reputation: 2348

If you're using Transact-SQL, then you can probably tap into the system views.

SELECT SCH.[name] AS [SCHEMA]
, ST.[name] AS [TABLE_NAME]
, SUM(SP.[rows]) AS [ROW_COUNT]
FROM sys.tables AS ST
INNER JOIN sys.partitions AS SP ON ST.object_id = SP.object_id
INNER JOIN sys.schemas AS SCH ON SCH.schema_id = ST.schema_id
AND SP.[index_id] IN (0, 1)
--WHERE ST.[name] LIKE '<your table name>' + '%' -- Optional
GROUP BY SCH.[name], ST.[name]

Upvotes: -1

Related Questions