a=b=c?

  • Is there any way to compare 3 values in a SQL statement?

    What I currently have is this:

    DECLARE @a int = 123,

    @b-2 int = 123 ,

    @C int = 123

    SELECT CASE

    WHEN

    (@a = @b-2)

    AND

    (@a = @C)

    AND

    (@b = @C)

    then 1

    ELSE 0

    END

    Is there a better way to do this?

  • If @a = @b-2 and @a = @C, then @b-2 = @C by the transitive property.

  • Oh good ol proofs. Discrete math, how I miss thee.

    I need to have the logic in place so that if one is zero, the whole thing still works. Like this:

    DECLARE @a int = 102733019,

    @b-2 int = 102733019 ,

    @C int = 0

    SELECT CASE

    WHEN

    (@a = @b-2 OR (@a=0 OR @b-2 =0))

    AND

    (@a = @C OR (@a=0 OR @C =0))

    AND

    (@b = @C OR (@b=0 OR @C =0))

    then 1

    ELSE 0

    END

    It's for a SSIS package that has 3 child packages. I want to check to make sure all 3 packages have the same InvoiceID. If a package is skipped, the ID will be zero.

  • The ALL operator might be helpfull too (I always wondered when it would be helpfull and this is when 🙂 ):

    declare @a int;

    declare @b-2 int;

    declare @C int;

    select @a = 1, @b-2 = 1, @C = 1;

    select case when @a = all (select @b-2 union all select @C) then 'true' else 'false' end

    select @a = 1, @b-2 = 1, @C = null;

    select case when @a = all (select @b-2 union all select @C) then 'true' else 'false' end

    select @a = 1, @b-2 = 1, @C = 2;

    select case when @a = all (select @b-2 union all select @C) then 'true' else 'false' end

    -- The only one that doesn't work is all null's:

    select @a = null, @b-2 = null, @C = null;

    select case when @a = all (select @b-2 union all select @C) then 'true' else 'false' end



    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]


    If you don't have time to do it right, when will you have time to do it over?

  • Interesting, I'd never used ALL.

    You could wrap in ISNULL to handle NULLs, and return zero's instead (or some other value).

  • The ALL operator is interesting. Seems to make this awfully complicated though. Try this.

    DECLARE @a int = 102733019,

    @b-2 int = 102733019 ,

    @C int = 0

    select case when

    (@a = @b-2 and @b-2 = @C)

    or @a = 0

    or @b-2 = 0

    or @C = 0

    then 1

    else 0

    end

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Thanks Sean, but I'm not sure that would work.

    DECLARE @a int = 123,

    @b-2 int = 0 ,

    @C int =102733019

    select case when

    (@a = @b-2 and @b-2 = @C)

    or @a = 0

    or @b-2 = 0

    or @C = 0

    then 1

    else 0

    end

    If A and C are different and B is zero, it still returns 1.

  • Sean Lange (8/15/2011)


    The ALL operator is interesting. Seems to make this awfully complicated though. Try this.

    DECLARE @a int = 102733019,

    @b-2 int = 102733019 ,

    @C int = 0

    select case when

    (@a = @b-2 and @b-2 = @C)

    or @a = 0

    or @b-2 = 0

    or @C = 0

    then 1

    else 0

    end

    I don't see how that is less complicated than using = all. Below example is shorter than yours and it is easier -at least in my opinion- to see it's intended action "if a equals all of b and c". Especially if even more than 3 values were to be tested, using = all seems less complicated to me. And better yet, you could store all of the result values into a single table and test any number of entries in that table without having to know on beforehand how many values actually will need to be tested.

    DECLARE @a int = 102733019,

    @b-2 int = 102733019 ,

    @C int = 0

    select case when @a = all (select @b-2 union all select @C)

    then 1

    else 0

    end

    And finally, if you like the ALL operator, you may also like to read about the ANY and SOME operators: MSDN link.



    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]


    If you don't have time to do it right, when will you have time to do it over?

  • I believe the following will do the trick according to the updated requirements quite simply.

    DECLARE @a int = 102733019,

    @b-2 int = 102733019 ,

    @C int = 0

    ;

    SELECT CASE

    WHEN (@a = @b-2 AND @b-2 = @C) OR 0 IN (@a, @b-2, @C)

    THEN 1

    ELSE 0

    END

    ;

    As a bit of a side bar, you'll find that the use of ALL takes roughly twice the CPU resource if you ever decide to use it in batch code. The following does not meet the updated requirements for this thread but does demonstrate that ALL (as it is currently being demonstrated in code) is relatively ineffecient compared to traditional methods. The use of the @Bitbucket variable in the following code takes delivery of the result set to the screen out of the picture.

    --=================================================================================================

    -- Create a million rows of test data

    --=================================================================================================

    --===== Identify this section of the run

    PRINT '========== Create the test data ==========';

    --===== Conditionally drop the test table to make reruns in SSMS easier

    IF OBJECT_ID('TempDB..#TestTable','U') IS NOT NULL

    DROP TABLE #TestTable

    ;

    --===== Create and populate the test table on-the-fly

    SELECT TOP 1000000

    RowNum = IDENTITY(INT,1,1),

    A = ABS(CHECKSUM(NEWID()))%10+1,

    B = ABS(CHECKSUM(NEWID()))%10+1,

    C = ABS(CHECKSUM(NEWID()))%10+1

    INTO #TestTable

    FROM sys.all_columns ac1

    CROSS JOIN sys.all_columns ac2

    ;

    --=================================================================================================

    -- Test the "ALL" method

    --=================================================================================================

    --===== Identify this section of the run

    PRINT '========== Uses "ALL" ==========';

    --===== Clear the guns for a test.

    -- DO NOT DO THIS ON A PRODUCTION SERVER!

    DBCC FREEPROCCACHE;

    DBCC DROPCLEANBUFFERS;

    --===== Declare a variable to take the display time out of the picture

    DECLARE @Bitbucket CHAR(5);

    --===== Start the timer

    SET STATISTICS TIME ON;

    --===== Run the code to be measured for performance

    SELECT @Bitbucket = CASE WHEN A = ALL (SELECT B UNION ALL SELECT C) THEN 'True' ELSE 'False' END

    FROM #TestTable

    ;

    --===== Stop the timer

    SET STATISTICS TIME OFF;

    GO

    --=================================================================================================

    -- Test the "classic" method

    --=================================================================================================

    --===== Identify this section of the run

    PRINT '========== Uses "Classic" Method ==========';

    --===== Clear the guns for a test.

    -- DO NOT DO THIS ON A PRODUCTION SERVER!

    DBCC FREEPROCCACHE;

    DBCC DROPCLEANBUFFERS;

    --===== Declare a variable to take the display time out of the picture

    DECLARE @Bitbucket CHAR(5);

    --===== Start the timer

    SET STATISTICS TIME ON;

    --===== Run the code to be measured for performance

    SELECT @Bitbucket = CASE WHEN A = B AND B = C THEN 'True' ELSE 'False' END

    FROM #TestTable

    ;

    --===== Stop the timer

    SET STATISTICS TIME OFF;

    GO

    The results on my laptop machine follow...

    ========== Create the test data ==========

    (1000000 row(s) affected)

    ========== Uses "ALL" ==========

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    SQL Server Execution Times:

    CPU time = 905 ms, elapsed time = 691 ms.

    ========== Uses "Classic" Method ==========

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    SQL Server Execution Times:

    CPU time = 436 ms, elapsed time = 480 ms.

    Yes, I agree that it took a million rows of data to show much of a difference. Still, I won't be using ALL in such a fashion because it is demonstrably twice as resource hungry as conventional methods.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Sean Lange, I hadn't yet seen the additional requirement of skipping 0's when I wrote my reply. That is why yours looked over complicated to me. Sorry about that.

    Of course this additional requirement can also be rather easily implemented using = all. But as Jeff demonstrated, = all performs slightly worse than conventional logic, so I won't even bother putting an example up. Still if you have a need for testing a set of scalars for which you do not always know the exact number of entries in it, the all, any and some operators may still be of use. Nice to see that even the BIG people on this forum still encounter some "new" things. SQL server is cool :cool:.



    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]


    If you don't have time to do it right, when will you have time to do it over?

  • I've used ALL once before... to show the same thing. 😉

    Still, I'm always open to something different... what else could you use ALL for? I ask because I've never found the need to make sure that multiple column values in the same row were all identical.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I was just going for 3 variables but I do appreciate the explanation.

    So if ALL isn't useful. Why still keep it around? MSSQL is released every couple years. You'd think they be watching these sites. (not trying to open up a can of worms or anything)

  • It can be usefull, but only in very specific situations. But then, when people get to those situations, they tend to use alternative solutions since they are not familiar with the operator. i.e. it is likely hardly ever used, and it probably never will get used a lot either. But still it can be handy sometimes.

    And why it is not eliminated in a next release? First, because that would break backward compatibility; the very rare cases that it has been applied in would break if MS did remove it. It is less effort to keep it in. And then again, in some next release it may even perform better, so maybe it will still gain in popularity. We'll see what happens.



    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]


    If you don't have time to do it right, when will you have time to do it over?

  • I see your point but at the same time they have removed other functions that I HAVE used.

    I guess this one can't really be replaced.

    thanks for your insight though.

  • Jeff Moden (8/16/2011)


    what else could you use ALL for? I ask because I've never found the need to make sure that multiple column values in the same row were all identical.

    Jeff, the answer to that question is in your message footer 🙂

    "First step towards the paradigm shift of writing Set Based code:

    Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

    "All", "some" and "any" are most useful when working with values in a column in a set of rows that must all have a particular value, be over/under a particular value, or not any having a particular value. There are alternative methods to using these operators (as there are always alternatives for everything in T-SQL), f.e. using sub queries. However, in situations where readability is more important than raw execution speed, these operators can help for nicely readable and compacter T-SQL code. If only more people knew about these operators.

    I'm not presenting this as the way to do it, there are (better) alternatives. This is just an example to show that these operators may make T-SQL more readable for people less familiar with the language. This represents a small job system where multiple steps may need to be run in parallel for particular jobs. Each job's status depends on whether or not any or all of the job's steps are done and if all are done, their outcome.

    declare @jobs table (

    jobID int not null

    ,primary key( jobID)

    );

    declare @steps table (

    jobID int not null,

    action varchar(10) not null,

    done bit not null default 0,

    result int null

    );

    insert @jobs(jobID)

    select 1

    union all select 2

    union all select 3

    union all select 4;

    insert @steps(jobID, action, done, result)

    select 1, 'process 1', 1, 0

    union all select 1, 'process 2', 1, 0

    union all select 1, 'process 3', 1, -5

    union all select 1, 'process 4', 1, 0

    union all select 2, 'process 1', 1, 0

    union all select 2, 'process 2', 1, 0

    union all select 3, 'process 3', 0, null

    union all select 4, 'process 2', 1, 0

    union all select 4, 'process 3', 0, 0;

    select j.jobID as [job ID]

    ,case

    when 1 = all (select done from @steps stp where stp.jobID = j.jobID)

    then

    case when 0 = all (select result from @steps stp where stp.jobID = j.jobID)

    then 'Done'

    else 'Failed'

    end

    when 1 = any (select done from @steps stp where stp.jobID = j.jobID)

    then 'Busy'

    else 'Waiting'

    end as [job status]

    from @jobs j;

    The output of the final query:

    job ID job status

    ----------- ----------

    1 Failed

    2 Done

    3 Waiting

    4 Busy

    (4 row(s) affected)



    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]


    If you don't have time to do it right, when will you have time to do it over?

Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply