August 14, 2011 at 9:38 pm
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
AND
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.
August 15, 2011 at 2:08 am
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
August 15, 2011 at 8:26 am
Interesting, I'd never used ALL.
You could wrap in ISNULL to handle NULLs, and return zero's instead (or some other value).
August 15, 2011 at 1:41 pm
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
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/
August 15, 2011 at 5:05 pm
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
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.
August 15, 2011 at 11:07 pm
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
Change is inevitable... Change for the better is not.
August 16, 2011 at 12:28 am
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:.
August 16, 2011 at 12:35 am
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
Change is inevitable... Change for the better is not.
August 16, 2011 at 1:29 am
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)
August 16, 2011 at 1:50 am
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.
August 16, 2011 at 2:33 am
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.
August 16, 2011 at 3:33 am
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)
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply