Problems with CASE in WHERE

Question

Post reply

Problems with CASE in WHERE

smacena

SSC Rookie

Points: 32
More actions
December 4, 2008 at 8:48 am

#203781

Hi, I'm trying use this clause in WHERE:
...
WHERE
CASE :Hist WHEN '2'
THEN (PERSON.DATE is null or PERSON.DATE is not null)
ELSE (PERSON.DATE is null)
END
But it don't work, when i try to execute show a window with this message:
"ORA-00907: Missing right parenthesis"
Where is the erro?
I'm sorry my english, i just learning, don't know yet very well.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

smacena SSC Rookie Points: 32 More actions · Answer 1

I forgot, i'm using PL/SQL Developer and i'm learning ORACLE.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Just so you know, this is a MS SQL Server site. There may not be many people here who will be able to help you with PL/SQL. Most people posting to this forum are trying to port Oracle code to SQL Server.

gints.plivna Ten Centuries Points: 1256 More actions · Answer 3

You are quite strangely using "case" here I'd say that something like:

WHERE hist = 2 and (PERSON.DATE is null or PERSON.DATE is not null)

or (hist <> 2 and PERSON.DATE is null)

is more appropriate

BTW what is :hist - bind variable?

Normally case is used to calculate some result and then one can either ouput it or used in comparison:

SQL> select * from dual

2 where 1 = (case dummy when 'X' then 1 else 2 end);

D

-

X

Gints Plivna
http://www.gplivna.eu

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 4

smacena (12/4/2008)
Hi, I'm trying use this clause in WHERE:
...
WHERE
CASE :Hist WHEN '2'
THEN (PERSON.DATE is null or PERSON.DATE is not null)
ELSE (PERSON.DATE is null)
END
But it don't work, when i try to execute show a window with this message:
"ORA-00907: Missing right parenthesis"
Where is the erro?
I'm sorry my english, i just learning, don't know yet very well.

The syntax of your CASE statement does not makes any sense.

Perhaps you may want to post pseudo-code or even your specs so people can actually help you to put your WHERE clause together.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

smacena SSC Rookie Points: 32 More actions · Answer 5

I'm sorry, i'm learning DataBase and i'm newer and I solved the problem this way

...

WHERE

( :Hist <> 2 or PERSON.DATE IS NULL)

It worked very well, if :Hist was equal 2 get (PERSON.DATE IS NULL or PERSON.DATE IS not NULL) if not then get (PERSON.DATE IS NULL) and :Hist is a parameter to pass values from a variable VB.NET that i used in Reporting Services in Visual Studio 2005

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 6

Cool! That makes sense, glad you solved it. Congrats!

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.