CASE NOT equal

I have the following statement...
CASE
WHEN SRV.srv_package <> PkgtoSRV.Package_SRV
THEN 'Not Equal'
ELSE
'Equal'
END
The value for SRV.srv_package is NULL and the value for
PkgtoSRV.Package_SRV is 2006-05-05. So why does this query return...Equal?
When they are clearly NOT equal. Am I failing to use CASE incorrectly
here? Can I NOT use <>?
Any help would be GREATLY appreciated...
wnfisbaNo, nulls do not come under anything. So, usually nulls are not predictable.
add this to the first line and try
SET ANSI_NULLS OFF
and try it..
if it still doesn't work
then do this... hope this helps.
CASE
WHEN isnull(SRV.srv_package,0) <> isnull(PkgtoSRV.Package_SRV,0)
THEN 'Not Equal'
ELSE
'Equal'
END
"wnfisba" wrote:

> I have the following statement...
> CASE
> WHEN SRV.srv_package <> PkgtoSRV.Package_SRV
> THEN 'Not Equal'
> ELSE
> 'Equal'
> END
> The value for SRV.srv_package is NULL and the value for
> PkgtoSRV.Package_SRV is 2006-05-05. So why does this query return...Equal?
'
> When they are clearly NOT equal. Am I failing to use CASE incorrectly
> here? Can I NOT use <>?
> Any help would be GREATLY appreciated...
> wnfisba|||NULL is unknown. Repeating something I posted yesterday:
<snip>
Nothing will ever = NULL, since the definition of NULL is unknown.
Think about it this way, if I have a form with a field that says "gender"
and I forget to check either male or female, can you say with any certainty
that I am:
(a) male?
(b) female?
(c) not male?
(d) not female?
Further, can you say with any certainty that someone else, who also forgot
to specify their gender, is:
(a) the same gender as me?
(b) the opposite gender from me?
(c) not the same gender as me?
(d) not the opposite gender from me?
</snip>
So, in order to do this comparison, you either need to account for NULLs in
the ELSE, or use COALESCE to allow bogus values into the comparison.
CASE
WHEN SRV.srv_package <> PkgtoSRV.Package_SRV
THEN 'Not Equal'
WHEN SRV.srv_package = PkgtoSRV.Package_SRV
THEN 'Equal'
ELSE 'Unknown - one or both are NULL'
END
-- assuming -1 is not a possible value
-- though, I don't even have an idea what data type you are using
CASE WHEN COALESCE(SRV.srv_package, -1) = COALESCE(PkgtoSRV.Package_SRV, -1)
THEN 'Equal'
ELSE 'Not Equal'
END
"wnfisba" <wnfisba@.discussions.microsoft.com> wrote in message
news:E25E0D4E-0113-401A-A07C-FB7B86C7DC96@.microsoft.com...
>I have the following statement...
> CASE
> WHEN SRV.srv_package <> PkgtoSRV.Package_SRV
> THEN 'Not Equal'
> ELSE
> 'Equal'
> END
> The value for SRV.srv_package is NULL and the value for
> PkgtoSRV.Package_SRV is 2006-05-05. So why does this query
> return...Equal?
> When they are clearly NOT equal. Am I failing to use CASE incorrectly
> here? Can I NOT use <>?
> Any help would be GREATLY appreciated...
> wnfisba

CASE in Select

I am trying to get something lik this working:
select .....
where
...
and user_id =
case @.userid
when <> 0 then @.userid
end
I need to conditionally use user_id = @.userid
when @.userid <> 0
Thankswww.sommarskog.se/dyn-search.html
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Mark Goldin" <mgoldin@.ufandd.com> wrote in message
news:%23clOHZ5PFHA.2000@.TK2MSFTNGP15.phx.gbl...
> I am trying to get something lik this working:
> select .....
> where
> ...
> and user_id =
> case @.userid
> when <> 0 then @.userid
> end
> I need to conditionally use user_id = @.userid
> when @.userid <> 0
> Thanks
>sql

Case in inner join

Hi All,
Is it possible to use Case statement inside Joins?
Say,
Select ........ from TableA A
inner join TableB B on
Case
When @.filter <> ''
Then B.ID = A.ID
and B.ID in (Select id from temptable where Name = @.filter)
Else B.ID = A.ID
End
inner join TableC C on C.ID = A.ID
where....................................
Thanks in advance...
KuttyTry this
Run this on Pubs, change the 1 = 1 to false to test the fall through option
where you just join on A.id = b.id.
select * from titles a
inner join titleauthor b
on a.[title_id] = case when (1 = 1) then ('PS3333') else b.title_id end
and a.title_id = case when (1 = 1) then (b.title_id) end
This is how your code can be changed.
select * from TableA a
inner join TableB b
on B.ID = case when (@.filter <> '') then (@.filter) else (A.ID) end
and B.ID = case when (@.filter <> '') then (A.ID) end
Don't know if this will work.
Hope it's what you are looking for.
"Pradeep Kutty" wrote:

> Hi All,
> Is it possible to use Case statement inside Joins?
> Say,
> Select ........ from TableA A
> inner join TableB B on
> Case
> When @.filter <> ''
> Then B.ID = A.ID
> and B.ID in (Select id from temptable where Name = @.filter
)
> Else B.ID = A.ID
> End
> inner join TableC C on C.ID = A.ID
> where....................................
>
> Thanks in advance...
> Kutty
>
>|||Hi
Is it still open ? Did not you resolve the problem?
Use pubs
SELECT a.au_lname, a.au_fname, a.address,
t.title, t.type
FROM authors a INNER JOIN
titleauthor ta ON ta.au_id = a.au_id INNER JOIN
titles t ON t.title_id = ta.title_id
INNER JOIN publishers p on t.pub_id =
CASE WHEN t.type = 'Business' THEN p.pub_id ELSE null END
INNER JOIN stores s on s.stor_id =
CASE WHEN t.type = 'Popular_comp' THEN t.title_id ELSE null END
"Pradeep Kutty" <pradeepk@.healthasyst.com> wrote in message
news:%23Hq1W3XSFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> Is it possible to use Case statement inside Joins?
> Say,
> Select ........ from TableA A
> inner join TableB B on
> Case
> When @.filter <> ''
> Then B.ID = A.ID
> and B.ID in (Select id from temptable where Name =
@.filter)
> Else B.ID = A.ID
> End
> inner join TableC C on C.ID = A.ID
> where....................................
>
> Thanks in advance...
> Kutty
>|||There is no CASE statement in SQL. There is a CASE **expression**.
Expressions return values. Since SQL is a declarative language, your
guess at the syntax is fundamentally wrong; you still think you are
writing procedural code.|||--CELKO-- wrote:
> There is no CASE statement in SQL. There is a CASE **expression**.
> Expressions return values. Since SQL is a declarative language, your
> guess at the syntax is fundamentally wrong; you still think you are
> writing procedural code.
And, Joe, you still think yer Codd's gift to earth.
Lighten up, crotchety! Get laid, or somethin.

CASE in a WHERE clause?

How can I do this?

Select somedata

From sometable

Where somecolumn = somevalue

and

CASE WHEN someparam = 1 THEN somecolumn2 <> somevalue2 ELSE somecolumn2 like '%'

Or this?

Select somedata

From sometable

Where somecolumn = somevalue

IF someparam = 1 AND somecolumn2 <> somevalue2

The error I am getting is Incorrect syntax near '<' using the first statement. Using the second statement I get an 'Error near IF' error or something like that.

Thanks for any help!

Hi !

As you see you can't do it that way, but you probably could use derived table syntax:

select dT.*, case when dt.newcol1>42 then '42t' else cast(dt.newcol2 as varchar(10)) end newcolX

from

( select t.*, case when t.col1='a' then 'A' else '' end newcol1,

case t.col2 when 2 then col3+col4 else col5 end newcol2

-- you can also use nested case...

from sometable t

) dt

where dt.newcol2 like 'text';

Best regards

Bjorn

P.S. there is no meaning in select-stmt, I'm just showing syntax...

|||

1.

Not sure this is the best way but you could generate your sql dynamically as a varchar then use EXEC to run the varchar

2.

Again, not sure this is correct but try:

.....WHERE (somecolumn = somevalue AND someparam = 1 AND somecolumn2 <> somevalue2)

OR

someparam <> 1

Perhaps even a union query could work for number 2

like I said they probably aren't 100% correct but hopefully point you in the right direction

|||hi,

i'm not sure what your requirements really are.. hope you can gets something from this example

declare @.ab varchar(100)

set @.ab = 1

select *
from (
select 'the quick' as a, 'brown fox' as b union all
select 'jumps over' as a, 'tha lazy dog' as b
) ab
where b = 'brown fox'
and a <> (case when @.ab = 1 then 'the quick' else '%' end)

set @.ab = 2

select *
from (
select 'the quick' as a, 'brown fox' as b union all
select 'jumps over' as a, 'tha lazy dog' as b
) ab
where b = 'brown fox'
and a <> (case when @.ab = 1 then 'the quick' else '%' end)

again, it really depends on the conditions that you need to evaluate|||

Use the following query..

Code Snippet

Select

somedata

From

sometable

Where

somecolumn = somevalue

And

(

(someparam = 1 And somecolumn2 <> somevalue2)

Or

(someparam <> 1 And somecolumn2 like '%')

)

|||

Thank you!!

|||

sarolabelle,

A "case" expression returns a value, so you need to compare the value returned by the "case" expression to something, in order to have a valid logic expression.

Select

somedata

From

sometable

Where

somecolumn = somevalue

and

CASE

WHEN someparam = 1 and somecolumn2 <> somevalue2 then 1

WHEN someparam != 1 and somecolumn2 like '%' then 1

else 0

end = 1

go

AMB

|||

Try this one

-- Case in Where

Code Snippet

Select * From EMP

Where (Country = 'India')

AND

(CASE

WHEN Salary < 5000 then 1

WHEN Age > 18 then 1

END = 1)

-- Case & where (individual)

Code Snippet

SELECT Name, Salary,

CASE

WHEN Salary < 5000 THEN 'No Tax'

WHEN Salary > 5000 THEN 'High Tax'

WHEN Salary = 5000 THEN 'Low Tax'

ELSE 'Invalid Data'

END

AS Tax

FROM EMP

WHERE country = 'USA'

for more details logon www.sqltree.com (under Conditional Processing folder)

Thanks

|||SQLTree -> It is not answering the question. Here the requirement is filtering the data.