October 27, 2008 at 10:28 am
I set a parameter to decide whether to include zero sales or not.
Select * From table
Where Case
When @NoSales = ‘No’ Then ( Total <> 0.00 OR Quantity <>0)
When @NoSales=’Yes’ Then ‘ ‘
End
It’s giving me error as I can’t compare values in Case statement.
I tried in different way, but it’s not giving any rows when @NoSales is Yes. It works fine when @NoSales is No.
Select * From table
Where Case
When @NoSales = ‘No’ Then
Case When ( Total <> 0.00 OR Quantity <>0) Then 1
Else 0
When @NoSales=’Yes’ Then ‘ ‘
End <> 0
Is there any better way to write?
October 27, 2008 at 10:41 am
Shree (10/27/2008)
I set a parameter to decide whether to include zero sales or not.Select * From table
Where Case
When @NoSales = ‘No’ Then ( Total <> 0.00 OR Quantity <>0)
When @NoSales=’Yes’ Then ‘ ‘
End
It’s giving me error as I can’t compare values in Case statement.
I tried in different way, but it’s not giving any rows when @NoSales is Yes. It works fine when @NoSales is No.
Select * From table
Where Case
When @NoSales = ‘No’ Then
Case When ( Total <> 0.00 OR Quantity <>0) Then 1
Else 0
When @NoSales=’Yes’ Then ‘ ‘
End <> 0
Is there any better way to write?
It's Monday, and I can't seem to get my head around your question, so if you could fill in the blanks in the following with the query you are trying to run (no case statement) would help:
if @NoSales = 'No'
begin
-- your query here
end
if @NoSales = 'Yes'
begin
-- your query here
end
😎
October 27, 2008 at 10:53 am
You're missing the way this works.
WHERE sales > (case when @sales = 'no' then 1 else 0)
THe CASE statement returns a value. That value gets slipped into your WHERE clause. You can't say where one of a case statement results. It's not a macro expansion. You use a normal where clause
where a = b
Now you can substitute a WHERE clause to return a value in place of a or b.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 27, 2008 at 10:54 am
Can you post table definition (as a create statement), some sample data (as insert statements) and your desired results please.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2008 at 11:28 am
It worked for me when i changed the last part.
Select * From table
Where Case
When @NoSales = ‘No’ Then
Case When ( Total <> 0.00 OR Quantity <>0) Then 1
Else 0
When @NoSales=’Yes’ Then 1
End <> 0
Thank you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply