on 8/15/02 4:28 PM, Jeff Mason at jeffm.ma.ultranet@r... wrote:

> The first thing you need to do is find the SOB who designed that table and
> shoot him ;-)

Yikes! That'd be ME!  <red face, shuffles feet>

It was done in full knowledge of the "db-transgression" I was enacting. It
was (as it usually is) done in some what of a hurry. At the time I had no
idea I'd be going to a full sql backend. Omnis Studio has its own
proprietary datafile and it was a quick and dirty for that. Now that I'm
moving the app to talk to a more robust backend, the errors my my ways (as
they usually do) are coming back to haunt me.

Nice solution - That should do the trick.

Thanks!

=Alan R.

> 
> If I understand you correctly, you can try something like:
> 
> SELECT col1, col2, accy1key,...accy10key,
> (CASE WHEN accy1key=0 THEN 0 ELSE 1 END +
> CASE WHEN accy2key=0 THEN 0 ELSE 1 END +
> ...
> CASE WHEN accy10key=0 THEN 0 ELSE 1 END) as CountColsWithNonZeroes
> FROM...
> 
> Each case expression adds 1 to a sum if the corresponding column is non
> zero.