Error handling or Exception handling in Netezza

Question

I'm running a netezza sql process as part of a shell script and in one of the sql codes, I want it to raise an ERROR or exception if the number of rows from 2 different tables don't match.

SQL Code:

/*  The following 2 tables should return the same number of rows to make sure the process is correct */

select              count(*) 
from                (
                select distinct col1, col2,col3 
                from table_a
                where  week > 0 and rec >= 1
                ) as x ;


select              count(*) 
from                (
                select distinct col1, col2, col3
                from table_b
                ) as y ;

How do I compare the 2 row counts and raise an exception/ERROR in the netezza SQL process, so that it exits the process, if the 2 row counts aren't equal ?

score 1 · Answer 1 · answered Nov 20 '13 at 00:02

i'm guessing the best solution here is to do it in the script.
i.e store the result of count(*) in variables, then compare them. nzsql has command line options to only return the result data of a single query.

If it must be done in plain SQL, a horribly, horrible kludge that will work is to use divide-by-zero. It's ugly but I've used it before when testing stuff. off the top of my head:

with 
subq_x as select count(*) c1 .... ,
subq_y as select count(*) c2 ...
select (case when (subq_x.c1 != subq_y.c1) then 1/0 else 1 end) counts_match;

Did I mention this is ugly ?

Yep. Do it in the shell script instead. SQL is for executing queries, not control flow. — N West, Nov 21 '13 at 16:35

score 1 · Accepted Answer · answered Dec 03 '13 at 12:19

I agree a script is the best option. However you could still do the check in your SQL itself by using a cross join

Select a.*
from Next_Step_table a cross join
(select case when y.y_cnt is null then 'No Match' else 'Match' end as match
from (select count(*) as x_cnt
from  ( select distinct col1, col2,col3 
        from table_a
        where  week > 0 and rec >= 1
       )) x left outer join
(select count(*) as y_cnt
from  (select distinct col1, col2, col3
       from table_b
       )) y  on x.x_cnt=y.y_cnt) match_tbl
where match_tbl.match='Match'

Error handling or Exception handling in Netezza

2 Answers2