Re: Stored Procedure Help

Maurits — Mon, 17 Apr 2006 15:57:29 GMT

VBJB wrote:

I
I want a query that will return a single value, 0 if all values are zero and a 1 if any of the values are a 1.

Sounds like a job for MAX:

SELECT     MAX(Job_Operation.Inside_Oper)
FROM         Job LEFT OUTER JOIN
                      Job)_Operation ON Job.Job = Job_Operation.Job

This will fail if Inside_Oper is a bit type [which, as you say, it is] because you can't take a MAX of a bit.

But there's an easy fix:

SELECT     MAX(CAST(Job_Operation.Inside_Oper as int))
FROM         Job LEFT OUTER JOIN
                      Job)_Operation ON Job.Job = Job_Operation.Job

If you want to consider NULLs as 0 or 1, you can do

SELECT     ISNULL(MAX(CAST(Job_Operation.Inside_Oper as int)), 0 /* or 1 */)
FROM         Job LEFT OUTER JOIN
                      Job)_Operation ON Job.Job = Job_Operation.Job

Comment Feed for Stored Procedure Help (TechOff on Channel 9)

Re: Stored Procedure Help