OraFAQ Forum: SQL & PL/SQL » SQL Query Case and Count

Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query Case and Count (Oracle )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL Query Case and Count [message #638738]

Fri, 19 June 2015 07:18

elements92
Messages: 2
Registered: June 2015

Junior Member

Hello,

i need your help...

This Version is works fine:

select (case count(distinct land)
when 29 then "0"
when 30 then "1"
when 31 then "2"
else "3"
end) as status
from anst
where land like "___%"

But I need this Version with < or >, and i don´t find the problem...

select (case
when count(distinct land)>31 then "2"
when count(distinct land)>30 then "1"
when count(distinct land)>29 then "0"
else "3"
end) as status
from anst
where land like "___%"

Can somebody help me,
Thx

Report message to a moderator

Re: SQL Query Case and Count [message #638739 is a reply to message #638738]

Fri, 19 June 2015 07:32

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Report message to a moderator

Re: SQL Query Case and Count [message #638740 is a reply to message #638739]

Fri, 19 June 2015 08:02

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Well it'd help if you told us what was wrong with the result you did get.
That said, 31 is greater than 30 so this:

select (case
 when count(distinct land)>31 then "2"
 when count(distinct land)>30 then "1"
 when count(distinct land)>29 then "0"
 else "3" 
 end) as status
 from anst
 where land like "___%"

is the same as this:

select (case
 when count(distinct land)>31 then "2"
 when count(distinct land)=30 then "1"
 when count(distinct land)=29 then "0"
 else "3" 
 end) as status
 from anst
 where land like "___%"

Report message to a moderator

Re: SQL Query Case and Count [message #638741 is a reply to message #638738]

Fri, 19 June 2015 08:07

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

The 2 expressions are not equivalent.
For instance, in the first 32 gives 3 (last branch: else) when in the second one it gives 2 because it is greater than 31 (first branch).

Report message to a moderator

Re: SQL Query Case and Count [message #638805 is a reply to message #638741]

Mon, 22 June 2015 00:47

elements92
Messages: 2
Registered: June 2015

Junior Member

Hello,
thanks for the replies.

I know that they are not equivalent and they should not...

I need the second select statement, but I keep getting the error:
A syntax error has occurred

I can not find the error, I do not know how I can convert the first statement that I not only filter
WHEN 29
When 30
but when <30
or when >=30

How do I do that?

Thx

Report message to a moderator

Re: SQL Query Case and Count [message #638807 is a reply to message #638805]

Mon, 22 June 2015 01:25

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

It is not clear what you want, it is not clear what error you have, it is not clear why you post the first query if you don't care about it.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Report message to a moderator

Re: SQL Query Case and Count [message #638816 is a reply to message #638807]

Mon, 22 June 2015 05:11

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

If you do
CASE [expression] WHEN [value] THEN [value]
then you can only do equality checks.

If you do
CASE WHEN [expression] THEN [value]
then you can do any check you like.

So you can't do this:

select (case count(distinct land)
 when < 29 then "0"

but you can do this:

select (case when count(distinct land) < 29 then "0"

Report message to a moderator

Previous Topic:	date format from string
Next Topic:	Design to Validate Staging records (merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 18:45:19 CDT 2024