PeteEngineer
asked on
Neater SQL Code
I am doing the following :
Is there any better way to do this my eliminating the case statements?
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Unless you will explain what you wanted to do, it will be very hard to assist you.
ASKER
i fixed as below :
select * from Customer where FirstName = ISNULL('ja',FirstName)
select * from Customer where FirstName = ISNULL('ja',FirstName)
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')
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')
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
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
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)
@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?
I don't think he is looking for isnull(Firstname,'ja') since in his query this would be WHERE Firstname=ISNULL(Firstname
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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%'
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I don't really get what you want to do since ISNULL('Ja','') will always return 'Ja'
Furthermore CASE WHEN 'jaisonshereen@gmail.com1'
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...