netezza - Do a row count in all tables in a DB where columnA=1 and columnB=DEV -

i need row count on netezza system 2 columns have values

i have

select tablename, reltuples  _v_table  objtype = 'table' , tablename 'my%stuff'  

this show me table names , row counts

but need add clause it. columna= 1 , columnb = abc,

every table has these 2 column names in it.

thanks

craig

if want via sql, can accomplish stored procedure. here's stored procedure think you're asking for.

my assumption want count rows in tables match column values specified, reltuples in _v_tables won't of use that. sample stored procedure code assumes each of columns varchar(1000). you'll have alter code if want match other datatypes.

prior creating stored procedure, create reference table so:

create table reftable_sp_row_count (tablename varchar(1000), rowcount bigint) distribute on random; 

then create stored procedure so:

create or replace procedure sp_row_count(varchar(254), varchar(254), varchar(254), varchar(254), varchar(254)) returns reftable(reftable_sp_row_count) execute owner language nzplsql begin_proc declare     ptablepattern alias $1;     pcolonename alias $2;     pcolonevalue alias $3;     pcoltwoname alias $4;     pcoltwovalue alias $5;      vrecord record; begin  vrecord in  execute 'select schema       || ''.''       || tablename tablename _v_table v tablename ''' || upper(ptablepattern) || ''' , exists    (       select attname       _v_relation_column c       c.objid = v.objid       , attname   = ''' || upper(pcolonename) || '''    ) , exists    (       select attname       _v_relation_column c       c.objid = v.objid       , attname   = ''' || upper(pcoltwoname) || '''    )'    loop     execute immediate 'insert ' || reftablename ||     ' select '''  ||  vrecord.tablename || ''' , count(1) ' || vrecord.tablename ||     ' ' || upper(pcolonename) || ' = ''' || pcolonevalue || ''' , ' || upper(pcoltwoname) || ' = ''' || pcoltwovalue || ''' ;';     -- note if change data types given column different type you'll want change ''' || pcolonevalue || ''' ' || pcolonevalue || '  appropriate    end loop;  return reftable; end; end_proc; 

here sample output.

testdb.admin(admin)=> select * table_1 order cola, colb;  cola | colb ------+------  abc  | blue  abc  | blue  abc  | blue  abc  | red  abc  | red (5 rows)  testdb.admin(admin)=> select * table_2 order cola, colb;  cola |  colb ------+--------  abc  | blue  abc  | blue  xyz  | blue  xyz  | blue  xyz  | yellow (5 rows)  testdb.admin(admin)=> call sp_row_count('table_%', 'cola', 'abc', 'colb','blue');    tablename   | rowcount ---------------+----------  admin.table_1 |        3  admin.table_2 |        2 (2 rows)