Home » SQL & PL/SQL » SQL & PL/SQL » CASE WHEN (ORACLE 10.2)
CASE WHEN [message #421560] Wed, 09 September 2009 04:20 Go to next message
apr@hvl.nl
Messages: 5
Registered: September 2009
Junior Member
I don't understand why the case statement is not running. I get the following error message:

ORA-06592: CASE not found while executing CASE statement.

PL/SQL syntax=
CREATE OR REPLACE function calnrmzbgsAlexTEst
(p_mepid   		   bgsdblib.bgsdmipf.mepid%TYPE,
p_norm		  	   bgsdblib.bgsnrmpf.typenorm%TYPE,
p_waarde		   bgsdblib.bgsmwdpf.waarde%TYPE,
p_datmonster 		   bgsdblib.bgsnrmpf.datumvan%TYPE,
p_volgnummer		   bgsdblib.bgsomwpf.volgnummer%TYPE
)
Return number
IS
	t_waarde 	number;
BEGIN
	t_waarde := 0;
	FOR r_nrm IN (SELECT * FROM  bgsdblib.bgsnrmpf 
WHERE   MEPID  = p_mepid
	AND datumvan  <= p_datmonster
	AND (datumtot  > p_datmonster  OR DATUMTOT  IS NULL)
	AND UPPER(TYPENORM)  = UPPER(p_norm) ORDER BY VOLGNR  )
	LOOP
		CASE TRIM(r_nrm.operand)
	    	WHEN '<' THEN
 	      	IF p_waarde < r_nrm.NORMWAARDE
  	      	THEN t_waarde := 1;
			END IF;
		WHEN '>' THEN
 	      	IF p_waarde > r_nrm.NORMWAARDE
  	      	THEN t_waarde := 1;
			END IF;		
		WHEN '>=' THEN
 	      	IF p_waarde <= r_nrm.NORMWAARDE
  	      	THEN t_waarde := 1;
			END IF;
		WHEN '<=' THEN
 	      	IF p_waarde <= r_nrm.NORMWAARDE
  	      	THEN t_waarde := 1;
			END IF;					
	END CASE;
	END LOOP;
Return t_waarde;
END;
/


Thanks for any respons

[Mod Edit: Added Code tags]

[Updated on: Wed, 09 September 2009 04:27] by Moderator

Report message to a moderator

Re: CASE WHEN [message #421562 is a reply to message #421560] Wed, 09 September 2009 04:31 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Hi apr, did you do a search on the error? I'm assuming not, because a simple google search reveals:
Quote:
ORA-06592: CASE not found while executing CASE statement
Cause: A CASE statement must either list all possible cases or have an else clause.

Action: Add all missing cases or an else clause.



Which would seem to clearly point you towards a resolution. If you have any more problems understanding this, then post back
Re: CASE WHEN [message #421568 is a reply to message #421560] Wed, 09 September 2009 04:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In addition to @pablolee's point about needing an ELSE,

1) you end a CASE statement with END, not END CASE

2)
WHEN '>=' THEN
 	      	IF p_waarde <= r_nrm.NORMWAARDE
  	      	THEN t_waarde := 1;
			END IF;
should probably be
WHEN '>=' THEN
 	      	IF p_waarde >= r_nrm.NORMWAARDE
  	      	THEN t_waarde := 1;
			END IF;


3) As you're looping through all the rows and looking for ant matches, you don't need the Order By in SELECT

4) You could exit from the loop as soon as you find the first match to avoid processing unneccessary rows

5) you could replace the whole loop with
SELECT 1
FROM DUAL
WHERE EXISTS (SELECT 1 
              FROM  bgsdblib.bgsnrmpf 
              WHERE   MEPID  = p_mepid
              AND datumvan  <= p_datmonster
              AND (datumtot  > p_datmonster  OR DATUMTOT  IS NULL)
              AND UPPER(TYPENORM)  = UPPER(p_norm)
              AND (   (TRIM(r_nrm.operand) = '<' AND p_waarde < r_nrm.NORMWAARDE)
                   OR (TRIM(r_nrm.operand) = '>' AND p_waarde > r_nrm.NORMWAARDE)
                   OR (TRIM(r_nrm.operand) = '>=' AND p_waarde <= r_nrm.NORMWAARDE)
                   OR (TRIM(r_nrm.operand) = '<=' AND p_waarde <= r_nrm.NORMWAARDE)) 
Re: CASE WHEN [message #421570 is a reply to message #421562] Wed, 09 September 2009 04:42 Go to previous messageGo to next message
apr@hvl.nl
Messages: 5
Registered: September 2009
Junior Member
The statement contains all possibility's and still it doesn't will work.

Re: CASE WHEN [message #421573 is a reply to message #421570] Wed, 09 September 2009 04:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
apr@hvl.nl wrote on Wed, 09 September 2009 11:42
The statement contains all possibility's and still it doesn't will work.

No, it does not contain them.

Regards
Michel

Re: CASE WHEN [message #421578 is a reply to message #421570] Wed, 09 September 2009 05:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
The statement contains all possibility's and still it doesn't will work.


Add
ELSE null;
before the END and see if it works.

I was wrong - for stand alone CASE statements (as opposed to case statments used as functions) you do use END CASE as the syntax.
Re: CASE WHEN [message #421579 is a reply to message #421568] Wed, 09 September 2009 05:04 Go to previous messageGo to next message
apr@hvl.nl
Messages: 5
Registered: September 2009
Junior Member
Thanks for your quick reply

In following your commend:

Mark 1
It doesn'n matter if CASE is ended with END CASE or END.

Mark 2
You are right but I'am first looking for a working syntax after that I will set the right operands. (<,> etc)

Mark 3
I can delete the order by statement but this is not the reason the syntax failed

Mark 4
Good idea, I will add this after that syntax works.

Mark 5
Thanks for the syntax, i can try this but I'am still wondered that the loop syntax doesn't work.



Re: CASE WHEN [message #421590 is a reply to message #421578] Wed, 09 September 2009 05:24 Go to previous messageGo to next message
apr@hvl.nl
Messages: 5
Registered: September 2009
Junior Member
Thanks for your answer

I tried this before (add null) but this doesn't help.


The problem is that case statement doesn't work. It looks like that cursor doesn't know the operand parameter.

By debugging the syntax I can see that the steps (toad) stops by the first case statement. I expect that if the case is false the debugging mode will jump to the next case statement.

The operand is a varchar so I don't understand what is wrong about it.

kind regards

Alex
Re: CASE WHEN [message #421595 is a reply to message #421590] Wed, 09 September 2009 06:10 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Quote:
I tried this before (add null) but this doesn't help.

Are you saying that when you have the statement as:
CASE TRIM(r_nrm.operand)
	    	WHEN '<' THEN
 	      	IF p_waarde < r_nrm.NORMWAARDE
  	      	THEN t_waarde := 1;
			    END IF;
		WHEN '>' THEN
 	      	IF p_waarde > r_nrm.NORMWAARDE
  	      	THEN t_waarde := 1;
			    END IF;		
		WHEN '>=' THEN
 	      	IF p_waarde <= r_nrm.NORMWAARDE
  	      	THEN t_waarde := 1;
			    END IF;
		WHEN '<=' THEN
 	      	IF p_waarde <= r_nrm.NORMWAARDE
  	      	THEN t_waarde := 1;
			    END IF;
                ELSE
                NULL;					
	END CASE;

That you still get the Error
ORA-06592: CASE not found while executing CASE statement.
If you do not mean this then please be more specific with what "this doesn't help" means, if it does still give that error, then can you confirm this.
Quote:
It looks like that cursor doesn't know the operand parameter

Then comment out the case statement and insert a DBMS_OTPUT.PUT_LINE(TRIM(r_nrm.operand));
Tell us/ Show us the result.

[Updated on: Wed, 09 September 2009 06:12]

Report message to a moderator

Re: CASE WHEN [message #421598 is a reply to message #421590] Wed, 09 September 2009 06:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you post the current code that you're using.

After that, try replacing the whole CASE statement with:
DBMS_OUTPUT.PUT_LINE(TRIM(r_nrm.operand)||' '||dump(trim(r_nrm.operand)));

Set Server Output on in SQL*Plus and call this function.

It should then show you the values of Operand that it is fetching, and the ASCII code of each character - this is to let you spot any unprimtable characters that might have got into your code.

[@Pablolee: great minds think alike]

[Updated on: Wed, 09 September 2009 06:16]

Report message to a moderator

Re: CASE WHEN [message #421602 is a reply to message #421598] Wed, 09 September 2009 06:17 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
JRowbottom wrote on Wed, 09 September 2009 12:14

[@Pablolee: great minds think alike]

So true (but then my father would often add on to that "Fools seldom differ") I prefer to think the former of course Smile
Re: CASE WHEN [message #421605 is a reply to message #421595] Wed, 09 September 2009 06:36 Go to previous message
apr@hvl.nl
Messages: 5
Registered: September 2009
Junior Member
Thanks for your respons

I will cut your statement in the syntax...
Previous Topic: Issue with SELECT query of CURSOR
Next Topic: Table colunms
Goto Forum:
  


Current Time: Fri Feb 07 17:39:20 CST 2025