Re: looping through queries

[Peter Brawley]

With multiple complicated Where requirements, it's often preferable to separate the logic from syntax complexities by formulating and debugging each subclause separately, saving the results to temp vars, then combining them as required to debug the overall logic, eg ...

select count(*) into @n
from test_table 
where click='yes' and prod in('x', 'y', 'z');

select count(*) into @d
from test_table 
where click='no' and prod in ('x', 'y', 'z');

select case 
         when @d=0 then "No 'no' clicks"
         when @n/@d < 0.1 then "More than 10 times yes's than no's"
         else "Fewer than 10 times yes's than no's"
       end;

Once such logic is proved correct, decide whether it's worthwhile to try to get rid of the intermediate variables @n, @d.