December 17, 2018 at 12:12 pm
What is the best way to write this query using CASE and not repeating with 25 IF / ELSE.,... Thanks
If (@Location = 01)
Select * from table_1
December 17, 2018 at 12:21 pm
Assuming location is "01", "02", etc., when passed in, then:
Select *
from table_1
Where
ID = @ID
AND Printing = 0
AND Salary = 1
AND (Acct_Num LIKE '___-___-' + @Location + '-___')
AND (Acct_Num NOT LIKE '000-___-__-___')
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
December 17, 2018 at 12:36 pm
It may be worth considering putting a computed LocationCode column on your table. This could be persisted and indexed and would make the above query much simpler.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 17, 2018 at 12:49 pm
Phil Parkin - Monday, December 17, 2018 12:36 PMIt may be worth considering putting a computed LocationCode column on your table. This could be persisted and indexed and would make the above query much simpler.
True. Better yet, follow data normalization and don't store data as it's displayed, but atomically by column, for every column. Acct Num should be stored as 4 columns -- if that's indeed what it really is -- but it can still be displayed combined with dashes. A view can handle that task quite easily.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
December 17, 2018 at 2:10 pm
Thanks for the quick response, as a SQL beginner Scott first respond look fine to me, I don't quite understand the other suggestion
if possible a quick example might allow me to understand it better since you both agree on the second suggestion. I don't have access to the table
as I am just pulling the result for a report. Thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply