CASE statement usage?

I am trying to do this inside a stored procedure: Select list of ids which will use conditions, When a id is in another list of ids which retrieved from a table and limited by an dynamically chosen WHERE condition using CASE statement.

I do realize I can not use CASE statement because after keyword THEN, it must be a value, can not be a condition statement.

My code having syntax error are:

SELECT ...

FROM ...

WHERE ...

AND lav.ListingAttributeId IN (
SELECT listingAttributeId
FROM @.TempListingAttributeValuesTable
WHERE
CASE comparision
WHEN 'Between' THEN
lav.Value BETWEEN CAST(attributeValue1 AS FLOAT) AND CAST(attributeValue2 AS FLOAT)
WHEN '=' THEN
lav.Value = CAST(attributeValue1 AS FLOAT)
WHEN '>' THEN
lav.Value > CAST(attributeValue1 AS FLOAT)
WHEN '<' THEN
lav.Value < CAST(attributeValue1 AS FLOAT)
WHEN '>=' THEN
lav.Value >= CAST(attributeValue1 AS FLOAT)
WHEN '<=' THEN
lav.Value <= CAST(attributeValue1 AS FLOAT)
END
)

Is there any other way I can select the search condition instead of using CASE?

Thank you.

WHERE
CASE
WHEN comparision = 'Between' AND lav.Value BETWEEN CAST(attributeValue1 AS FLOAT) AND CAST(attributeValue2 AS FLOAT) THEN 1
WHEN comparision='=' AND lav.Value = CAST(attributeValue1 AS FLOAT) THEN 1
WHEN comparision='>' AND
lav.Value > CAST(attributeValue1 AS FLOAT) THEN 1
WHEN comparision = '<' AND lav.Value < CAST(attributeValue1 AS FLOAT) THEN 1
WHEN comparision='>=' AND lav.Value >= CAST(attributeValue1 AS FLOAT) THEN 1
WHEN comparison ='<=' AND lav.Value <= CAST(attributeValue1 AS FLOAT) THEN 1
ELSE 0
END = 1

|||Thank you. It solved my question.

I am wondering the possibility of building WHERE condition dynamically?

I think it is impossible, but my mate told me I could do it in other ways, but it needs to restructure the query.

Anyone got idea?

Use the code I posted as an example, is it possible if I want to do something like:
SELECT *
FROM TableName
WHERE condition1 or condition 2 or condition 3 etc.

The number of condition is not fixed.

Thank you.
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.

carriage return inside a field of text data type?

how can i insert a carriage return when i update the field?

say i want to put the following inside a field:
firstline
secondline

how can i update/insert a column to have a return carriage inside it?
UPDATE table SET column = 'firstline secondline'

the reason i want this is because when using a program (Solomon, by microsoft, purchasing software) to grab a field out of the database and when it displays that field in the programs textbox, i want it to be displayed on two separate lines

i tried doing
UPDATE table SET column = 'firstline' + char(13) 'secondline'

but when in the solomon program, it displays an ascii character between firstline and secondline like: firstline||secondline

thankstry char(10) instead
or the combination of the two characters|||ive actually tried them both :(

edit: just tried using char(13) + char(10) and it works! thanks!|||you welcome ;)