Tech Off Thread

2 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Stored Procedure Help

Back to Forum: Tech Off
  • User profile image
    VBJB

    I have two tables with a one-to-many relationship. The table that has the "many" relationship (Job_Operation) has a field (Inside_Oper) that is a bit type . The query:

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

    will return

    0
    1
    0
    0

    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.

    Examples:
    1
    0
    0
    returns 1

    0
    0
    returns 0

    0
    1
    returns 1

  • User profile image
    Maurits

    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

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.