CASE WHEN [message #421560] |
Wed, 09 September 2009 04:20  |
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   |
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   |
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 #421578 is a reply to message #421570] |
Wed, 09 September 2009 05:03   |
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 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   |
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   |
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   |
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   |
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
|
|
|
|
|