August 8, 2012 at 12:44 pm
Good Afternoon,
I am working on a Case Statement to determine a Warranty Calculation for our products depending on the Product Description. The reason I wish to do this is that we have several products that start with Enduro (Enduro, Enduro Duo, Enduro Duo Mag, etc..)
In my select statement I have:
--Start
Warranty1 = (Case APImport.Description
When like 'Enduro%' then (DateAdd(year,1,APImport.DateShipped)) End)
--End
Each time I try to Execute the query I get:
"Incorrect syntax near the keyword 'like'."
Am I missing something? I seem to be following what I have been able to dig up online, but it just isn't working. If I remove the 'like' and put in a fully qualified name 'Enduro+ Printer' then it will give me results. I just don't want to have to write a line for each model of printer. We have over 50 types.
Thanks for any help or suggestions.
August 8, 2012 at 12:46 pm
it's syntax;
it's like this:
Warranty1 = (
Case
When APImport.Description like 'Enduro%'
then (DateAdd(year,1,APImport.DateShipped))
ELSE NULL
End)
Lowell
August 8, 2012 at 12:54 pm
Thanks Lowell that worked great!
I knew it had to be something simple. I am still new at SQL, but I seem to be catching on pretty quickly. I have mostly worked in Access in the past, so the language is still tripping me up. Guess I got to used to having visual queries. Time to break some bad habits!
August 8, 2012 at 12:55 pm
Warranty1 = (
Case
When APImport.Description like 'Enduro%'
then (DateAdd(year,1,APImport.DateShipped))
ELSE NULL
End)
is there a significant performance difference between the above snippet and something like
Warranty1 = (
Case
When left(APImport.Description,6) = 'Enduro'
then (DateAdd(year,1,APImport.DateShipped))
ELSE NULL
End)
as the latter is what I was going to suggest.
August 8, 2012 at 1:05 pm
Donalith there would be some performance hit, yes:
if there is an index on APImport.Description, then the LIKE can use the index to find the value(s) that match.
by getting a substring on the columnvalue , the query is no longer SARG-able , so all rows have to be converted prior to being checked, so it'd be a full table scan.
if there's no index on APImport.Description, it'd be a full scan anyway,I believe, but statistics can help the performance on that a little bit.
edit
doh; since the CASE statement is not part of the WHERE , but part of the data returned, it's not going to make any difference, i think; it would be an in memory manipulation after all the data was returned.
Lowell
August 8, 2012 at 1:10 pm
lol.. That's why I asked, Lowell. If it's done in memory I was wondering if the internal mechanics of string comparison using LIKE would out-perform the substring-string comparison or are the internal mechanics actually creating a substring anyway to do the comparison and are we saving performance by explicitly stating it?
Don..
August 8, 2012 at 1:27 pm
Donalith (8/8/2012)
lol.. That's why I asked, Lowell. If it's done in memory I was wondering if the internal mechanics of string comparison using LIKE would out-perform the substring-string comparison or are the internal mechanics actually creating a substring anyway to do the comparison and are we saving performance by explicitly stating it?Don..
well this test harness is a little contrived, but it shows that there's no difference performance wise between the two case statements;
one million row temp table,and the CASE statmeent is finding strings that start with 'BA' from a varchar of a GUID;
95% of the work is the scan based on my date filter. if you look at the actual execution plan, the CompulteScalar impact is identical in the two
CREATE TABLE #Temp (ID int identity(1,1) not null primary key,
Warranty1 varchar(30),
othercolumns varchar(50),
DateShipped datetime
)
insert into #Temp
SELECT
top 1000000
LEFT(NewId(),30),
NEWID(),
DATEADD(dd, -1 * (ABS(CHECKSUM(NewId())) % 10) + 1,getdate())
from sys.columns c1 cross join sys.columns c2 cross join sys.columns c3
SELECT Warranty1 = (
Case
When left(Warranty1,2) = 'BA'
then (DateAdd(year,1,DateShipped))
ELSE NULL
End),*
FROM
#Temp
WHERE DateShipped > getdate() -5
SELECT Warranty1 = (
Case
When Warranty1 LIKE 'BA%'
then (DateAdd(year,1,DateShipped))
ELSE NULL
End),*
FROM
#Temp
WHERE DateShipped > getdate() -5
Lowell
August 8, 2012 at 1:56 pm
Thanks, Lowell!
I was too lazy to test it out myself. Just wondered if anyone knew off the top of their head.. lol
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply