• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

Neater SQL Code

I am doing the following :

Is there any better way to do this my eliminating the case statements?

select * from Customer where FirstName like ISNULL('ja','') + '%'  AND [EmailId] LIKE ISNULL('jaisonshereen@gmail.com1','') + CASE when 'jaisonshereen@gmail.com1' =  '' then '%' else ''  end

Open in new window

0
PeteEngineer
Asked:
PeteEngineer
  • 4
  • 3
  • 3
  • +3
5 Solutions
 
Dale BurrellDirectorCommented:
[EmailId] LIKE ISNULL('jaisonshereen@gmail.com1','') + CASE when 'jaisonshereen@gmail.com1' =  '' then '%' else ''  end doesn't make sense to me?

ISNULL('jaisonshereen@gmail.com1','') is fixed so is always 'jaisonshereen@gmail.com1' and never ''?

and

CASE when 'jaisonshereen@gmail.com1' =  '' then '%' else ''  end again is always fixed, so always '%' and never ''

unless I'm missing the point?
0
 
clampsCommented:
Erm...
I don't really get what you want to do since ISNULL('Ja','') will always return 'Ja'
Furthermore CASE WHEN 'jaisonshereen@gmail.com1' = '' THEN '%' ELSE '' END will always be '' since 'jaisonshereen@gmail.com1' is never the same as ''
So you could as well just do:
SELECT *
FROM Customer
WHERE FirstName LIKE 'ja%'
AND Emaild LIKE 'jaisonshereen@gmail.com1'

But I think you had something else in mind...
0
 
TempDBACommented:
Can you explain the use-case of your statement and what you want to do? The whole use of isnull and case statement here in the provided statement seems to be a dummy one with no proper use.
Unless you will explain what you wanted to do, it will be very hard to assist you.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
PeteEngineerAuthor Commented:
i fixed as below :


select * from Customer where FirstName = ISNULL('ja',FirstName)
0
 
TempDBACommented:
isnull('ja','Firstname') will always give you Firstname = 'ja'.
Why you want to add any other check there.. It is like checking
1=1

I think what you want to do is
isnull(Firstname,'ja')
0
 
clampsCommented:
That doesn't work as well...
It is exactly the same as:
SELECT * FROM Customer WHERE FirstName='ja'

ISNULL returns the second value if the first value is null.
'ja' is not null in any imaginable case so isnull always returns 'ja'

If you describe what you actually want to select I will provide you with the proper query.

Cheers
0
 
ralmadaCommented:
maybe you're looking for this. So if either of the variables @name or @email are null, those filters will be ignored in the query.
declare @name varchar(20)
declare @email varchar(20)

set @name = 'ja'
set @email = 'jaisonshereen@gmail.com1'


select * 
from Customer 
where 	(@name is null or FirstName = @name)
	AND 
	(@email is null or [EmailId] = @email)

Open in new window

0
 
clampsCommented:
@TempDBA
I don't think he is looking for isnull(Firstname,'ja') since in his query this would be WHERE Firstname=ISNULL(Firstname,'ja') and that would make no sense at all.
When Firstname is not NULL the equation is always true and if it's null then it's NULL='ja' and NULL equates to everything so the equation would be true again...

@PeteEngineer:
Can you try to describe what you have in mind?
0
 
TempDBACommented:
@clamps,
           Thanks buddy for pointing it out. I didn't think on the where clause part as my concentration was mainly on the use case of isnull function. Thanks again.

@peteEngineer,
            Can you please share something more. We are eagerly waiting to learn something from your case...
0
 
chrismcCommented:
Hi Pete,

Here's the most condensed form I can think of.
"COALESCE" is the same as "ISNULL" but can have more than 2 values and is no slower so I use it instead of ISNULL as a matter of course.
So you want the ISNULL/COALESCE around the the value your checking NOT the literal value you are checking against.
You seem to be making that mistake throughout.

Hope that helps.


Select *
	From Customer
	Where Coalesce(FirstName, '') Like 'ja%'
		And Coalesce(EmailId, '') Like 'jaisonshereen@gmail.com1%'

Open in new window

0
 
clampsCommented:
Hi chrismc,

COALESCE is a really nice thing to use, but in the case you described the following query is easier to understand 6 months after you wrote it (at least for me) and it's the same:

But again, we need Pete to tell us, if that's what he's looking for or explain to us, what he wants to accomplish...

Cheers.
SELECT *
FROM Customer
WHERE FirstName LIKE 'ja%' AND NOT FirstName IS NULL
AND EmailId LIKE 'jaisonshereen@gmail.com1%' AND NOT EmailId IS NULL

Open in new window

0
 
chrismcCommented:
Hi Clamps,

I see where you are coming from.
But if you have an expression with one side = null, it makes the expression false which would falsify the whole thing.
Again your point that the code might be easier for an occasional coder is still valid with this but it may be even simpler without the null check.

I also think the Coalesce/IsNull is more efficient than the "NOT FirstName IS NULL" bit but it's probably negligible in this case.

Cheers
Chris

0
 
ralmadaCommented:
>>COALESCE is a really nice thing to use, but in the case you described the following query is easier to understand 6 months after you wrote it (at least for me) and it's the same:<<

It's not the same, using COALESCE as indicated by chrismc is slower because it forces a table scan on the FirstName column. As a rule of thumb, you should try to avoid using a function on a column included the WHERE clause.

I still think my comment http:#a36955974 is what the asker was trying to achieve.

0
 
chrismcCommented:
Fair point ralmada and your code is certainly an answer, I was just trying to make it as compact or "Neat" as possible.

As a point of interest, to me at least, would it not have to scan for the Is Null check too.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 3
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now