identifying columns in all the tables with a specific value

gs79
gs79 used Ask the Experts™
on
oracle 11g

I have many tables that ends with FCT (lets take two tables ABC_FCT, XYZ_FCT) owned by my_schema.


All the tables have the following 4 columns predominatly: REG_ID, USR_ID, CAT_ID, SPR_ID

Now I need a generic sql which gives count of the above 4 ids where the value is -1 and print it as follows:

Tab_name    Col_name    Cnt
ABC_FCT       reg_id          10
ABC_FCT       usr_id           5
ABC_FCT       cat_id           6
ABC_FCT       spr_id           11
XYZ_FCT       reg_id          10
XYZ_FCT       usr_id           5
XYZ_FCT       cat_id           6
XYZ_FCT       spr_id          11
..
..
..
..
Can this be done with generic dynamic sql instead of calculating for every table..

Please let me know if you need any more information..

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
>>where the value is -1
I didn't understand this bit, how will a count of rows result in -1 ???
is the requirement fully stated?

Author

Commented:
sorry i think i was not clear

select count(*) from abc_fct where reg_id = -1
select count(*) from abc_fct where usr_id = -1

this way I want to dynamically find the counts for all the tables ending with %FCT for the 4 columns (reg_id, usr_id,cat_id, spr_id

Is there a way to do this with dynamic sql..

thanks
I came up with a 2 step process

Create a function that can run dynamic SQL like below...

CREATE OR REPLACE FUNCTION my_get_count (p_table VARCHAR2, p_column VARCHAR2) 
    RETURN NUMBER IS
    query_str VARCHAR2(1000);
    num_count NUMBER;
BEGIN
    query_str := 'SELECT COUNT(*) FROM ' || p_table
        || ' WHERE ' || p_column || ' = -1';
        
    EXECUTE IMMEDIATE query_str
        INTO num_count;
        
    RETURN num_count;
END;

Open in new window



... Then use this function in a select statement.
select 
 x.table_name
,X.COLUMN_NAME
,my_get_count (x.table_name, X.COLUMN_NAME)
from all_tab_columns X
where table_name like '%FCT'
--and owner = '' -- you may need this to limit to your schema only
and X.COLUMN_NAME in ('REG_ID', 'USR_ID','CAT_ID', 'SPR_ID')

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
I think you need to ensure all the relevant fields are present, hence I suggest use of user_tab_columns for this. This selection logic can be borrowed for other approaches of course. Try the following
BEGIN
    FOR i IN (
                SELECT
                TABLE_NAME
                FROM (
                      SELECT
                      DISTINCT TABLE_NAME
                      , COLUMN_NAME
                      , row_number() over (partition BY TABLE_NAME ORDER BY COLUMN_NAME) num_of
                      FROM user_tab_columns
                      WHERE COLUMN_NAME = 'REG_ID'
                      OR COLUMN_NAME = 'USR_ID'
                      OR COLUMN_NAME = 'CAT_ID'
                      OR COLUMN_NAME = 'SPR_ID'
                     )
                WHERE num_of = 4
              )
    LOOP
        EXECUTE IMMEDIATE 'select count(*) from ' || i.TABLE_NAME || ' where reg_id = -1';
    END LOOP;
END;

Open in new window

edit: oh, if you have views and don't want those included then annd this inner join to user_tab_columns
inner join user_tables ut on utc.table_name = ut.table_name

-- think this will exclude views (been a while)
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
with the suggested inner join...
BEGIN
    FOR i IN (
                SELECT
                TABLE_NAME
                FROM (
                      SELECT
                      DISTINCT utc.TABLE_NAME
                      , utc.COLUMN_NAME
                      , row_number() over (partition BY utc.TABLE_NAME ORDER BY utc.COLUMN_NAME) num_of
                      FROM user_tab_columns utc
                      INNER JOIN user_tables ut ON utc.TABLE_NAME = ut.TABLE_NAME
                      WHERE COLUMN_NAME = 'REG_ID'
                      OR COLUMN_NAME = 'USR_ID'
                      OR COLUMN_NAME = 'CAT_ID'
                      OR COLUMN_NAME = 'SPR_ID'
                     )
                WHERE num_of = 4
              )
    LOOP
        EXECUTE IMMEDIATE 'select count(*) from ' || i.TABLE_NAME || ' where reg_id = -1';
    END LOOP;
END;

Open in new window

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
humble apologies, 3rd and final attempt. This should output the table_name, which you probably need:
BEGIN
    FOR i IN (
                SELECT
                TABLE_NAME
                FROM (
                      SELECT
                      DISTINCT utc.TABLE_NAME
                      , utc.COLUMN_NAME
                      , row_number() over (partition BY utc.TABLE_NAME ORDER BY utc.COLUMN_NAME) num_of
                      FROM user_tab_columns utc
                      INNER JOIN user_table ut ON utc.TABLE_NAME = ut.TABLE_NAME
                      WHERE utc.COLUMN_NAME = 'REG_ID'
                      OR utc.COLUMN_NAME = 'USR_ID'
                      OR utc.COLUMN_NAME = 'CAT_ID'
                      OR utc.COLUMN_NAME = 'SPR_ID'
                     )
                WHERE num_of = 4
              )
    LOOP
        EXECUTE IMMEDIATE 'select ''' || i.TABLE_NAME ||''' as table_name, count(*) as count_of from ' || i.TABLE_NAME || ' where reg_id = -1';
    END LOOP;
END;

Open in new window

Most Valuable Expert 2011
Top Expert 2012
Commented:
SELECT table_name,
       column_name,
       TO_NUMBER(
           EXTRACTVALUE(
               DBMS_XMLGEN.getxmltype(
                   'select count(*) X from ' || table_name || ' where ' || column_name || ' = -1'
               ),
               '/ROWSET/ROW/X'
           )
       )
           COUNT
  FROM all_tab_cols
 WHERE owner = 'MY_SCHEMA'
   AND table_name LIKE '%FCT'
   AND column_name IN ('REG_ID', 'USR_ID', 'CAT_ID', 'SPR_ID')
ORDER BY table_name, column_name;
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
ah, I see I have it wrong, the count is per field - duh! Oh well.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
hats off to you sdstuber!!

I offer up a variant inspired by xmlgen:
SELECT
  TABLE_NAME
, TO_NUMBER(
    EXTRACTVALUE(
      DBMS_XMLGEN.getxmltype(
        'select count(distinct REG_ID || USR_ID || CAT_ID || SPR_ID) X from ' || table_name || ' where REG_ID = -1'
      ),
      '/ROWSET/ROW/X'
    )
  )
  as DISTINCT_COUNT_OF
FROM (
  SELECT
  DISTINCT utc.TABLE_NAME
  , utc.COLUMN_NAME
  , row_number() over (partition BY utc.TABLE_NAME ORDER BY utc.COLUMN_NAME) num_of
  FROM user_tab_columns utc
  INNER JOIN user_tables ut ON utc.TABLE_NAME = ut.TABLE_NAME
  WHERE utc.COLUMN_NAME = 'REG_ID'
  OR utc.COLUMN_NAME = 'USR_ID'
  OR utc.COLUMN_NAME = 'CAT_ID'
  OR utc.COLUMN_NAME = 'SPR_ID'
)
WHERE num_of = 4

Open in new window

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
woo hooo, this xmlgen is too enticing - apologies, but one last one. For an output like:
TABLE_NAME DISTINCT4 REG_ID USR_ID CAT_ID SPR_ID
SELECT
  TABLE_NAME
, TO_NUMBER(
    EXTRACTVALUE(
      DBMS_XMLGEN.getxmltype(
        'select count(distinct REG_ID || USR_ID || CAT_ID || SPR_ID) DISTINCT4 from ' || table_name 
      ),
      '/ROWSET/ROW/DISTINCT4'
    )
  )
  as DISTINCT_COUNT_OF
, TO_NUMBER(
     EXTRACTVALUE(
        DBMS_XMLGEN.getxmltype(
             'select count(*) REG_ID from ' || table_name || ' where REG_ID = -1'
         ),
         '/ROWSET/ROW/REG_ID'
     )
  )
  AS REG_ID
, TO_NUMBER(
     EXTRACTVALUE(
        DBMS_XMLGEN.getxmltype(
             'select count(*) USR_ID from ' || table_name || ' where USR_ID = -1'
         ),
         '/ROWSET/ROW/USR_ID'
     )
  )
  AS USR_ID
, TO_NUMBER(
     EXTRACTVALUE(
        DBMS_XMLGEN.getxmltype(
             'select count(*) CAT_ID from ' || table_name || ' where CAT_ID = -1'
         ),
         '/ROWSET/ROW/CAT_ID'
     )
  )
  AS CAT_ID
, TO_NUMBER(
     EXTRACTVALUE(
        DBMS_XMLGEN.getxmltype(
             'select count(*) SPR_ID from ' || table_name || ' where SPR_ID = -1'
         ),
         '/ROWSET/ROW/SPR_ID'
     )
  )
  AS SPR_ID
FROM (
      SELECT
      DISTINCT utc.TABLE_NAME
      , utc.COLUMN_NAME
      , row_number() over (partition BY utc.TABLE_NAME ORDER BY utc.COLUMN_NAME) num_of
      FROM user_tab_columns utc
      INNER JOIN user_tables ut ON utc.TABLE_NAME = ut.TABLE_NAME
      WHERE utc.COLUMN_NAME = 'REG_ID'
      OR utc.COLUMN_NAME = 'USR_ID'
      OR utc.COLUMN_NAME = 'CAT_ID'
      OR utc.COLUMN_NAME = 'SPR_ID'
)
WHERE num_of = 4

Open in new window

no points pl.

Author

Commented:
I tried all the solutions and all the solution works fine. Just curious which would be faster..calling function or using xmlgen type..

Please advice..

Thanks
I personally don't know. I have never used the DBMS_XMLGEN.getxmltype() function. I would try a sample if data and test both methods to see which is faster.

Without testing, I would guess the DBMS_XMLGEN.getxmltype() function because it is precompiled.

In my experience, some efficiency techniques work fine for most situations, but there is always a database design or query that breaks the normal rules of efficient coding.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
execute immediate is a bit 'kludgy' really, you prepare a string that is to be a query, then execute each query - so that approach is performing a lot of individual queries.

My guess is the DBMS_XMLGEN will outshine the (more old fashioned) execute immediate.
I don't get many opportunities to use that function so I don't think of it as a first choice - but will attempt to do so in the future.

the DBMS_XMLGEN is still performing quite a number of individual queries then parsing each to access a specific node in the xml output, but those XML functions are quite fast.

Perhaps you could do explain plans as a way to assess the relative merits?
Most Valuable Expert 2011
Top Expert 2012

Commented:
DBMS_XMLGEN is expensive,  for one, you do still need to parse and execute (effectively doing execute immediate anyway) plus the results are turned into xml, then the xml is parsed to pull out the values you really need.

However, a "run sql everywhere" type action is inherently an expensive operation so the added weight of dbms_xmlgen is probably negligible; then again, the added weight of looping in your own pl/sql is probably negligible as well.

I use dbms_xmlgen simply because a sql result set is generally easier to work with.
I do not use it for efficiency.

If you must get this information quickly, then time them, use autotrace or other session statistic measures and determine for yourself on your real data which is most efficient.

Author

Commented:
@ianmills

Is there a way to get the total count as well in same function call.. I tried passing the primary key column..and PK indicator as a parameter to function and modified the function as below to get a total couunt if the pased column is PK..But since many of the tables do not have a PK defined, for some of the tables the total count came blank..

CREATE OR REPLACE FUNCTION my_get_count (p_table VARCHAR2, p_column VARCHAR2, PK_IND)
    RETURN NUMBER IS
    query_str VARCHAR2(1000);
    num_count NUMBER;
BEGIN
if PK_IND = 'NP' then
    query_str := 'SELECT COUNT(*) FROM ' || p_table
        || ' WHERE ' || p_column || ' = -1';
elsif PK_IND = 'P'
   query_str := 'SELECT COUNT(*) FROM ' || p_table
 end if;
       
    EXECUTE IMMEDIATE query_str
        INTO num_count;
       
    RETURN num_count;
END;

I joined with all_constraints and all_constraint_cols to assign a PK_IND for a column..

Please let me know if you have any thoughts

Thanks
Most Valuable Expert 2011
Top Expert 2012

Commented:
to get a total count, how about something like this...


select table_name, column_name, cnt,  sum(cnt) over() total_count from
(SELECT table_name,
       column_name,
       TO_NUMBER(
           EXTRACTVALUE(
               DBMS_XMLGEN.getxmltype(
                   'select count(*) X from ' || table_name || ' where ' || column_name || ' = -1'
               ),
               '/ROWSET/ROW/X'
           )
       )
           cnt
  FROM all_tab_cols
 WHERE owner = 'MY_SCHEMA'
   AND table_name LIKE '%FCT'
   AND column_name IN ('REG_ID', 'USR_ID', 'CAT_ID', 'SPR_ID')
ORDER BY table_name, column_name)

Author

Commented:
@sdstuber

Thank you for your response. I think i was not clear. I am not looking for total of the columns with -1. I want to get the total count of the whole table as shown below

Tab_name    Col_name    Cnt
ABC_FCT       total             5500
ABC_FCT       reg_id          10
ABC_FCT       usr_id           5
ABC_FCT       cat_id           6
ABC_FCT       spr_id           11
XYZ_FCT       total              6000
XYZ_FCT       reg_id          10
XYZ_FCT       usr_id           5
XYZ_FCT       cat_id           6
XYZ_FCT       spr_id          11

Though total is not the name of a column in the table, i want to display the 'total' under column name so that I can pivot this.

Hence I passed the PK of the table to the function so that I could get the total count. Though I am successful getting what I want in the above format, some of the tables do not have PK and hence total is blank..

On the other note I will run both methods, calling function and xmlgen to measure the performance..

thanks
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
>>some of the tables do not have PK and hence total is blank..
just count(*) if all you want is total rows, no need to specify a field.

Author

Commented:
I figured this out..Thanks for the great answers..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial