Home » SQL & PL/SQL » SQL & PL/SQL » Using Case within a Function
icon9.gif  Using Case within a Function [message #210798] Fri, 22 December 2006 07:44 Go to next message
tdudley
Messages: 6
Registered: December 2006
Location: Barrie, Ontario
Junior Member
Hi Folks,

I'm a bit confused. I'm trying to pass a 6 character string and get an eight character string out of a function using the CASE function. I came across this before and finally decided to use the DECODE function as a workaround. As a transact SQL user I'm more comfortable reading a CASE than a DECODE so I wish to know what I'm doing wrong here. In Oracle SQL Developer it creates the function with a small red X and indicates "NO RETURN FOUND" ? I'm guessing this is due to the use of the word END to close the CASE being misinterpreted as the end of the FUNCTION...

create or replace FUNCTION FNNX2CLLI(str IN VARCHAR2(6)) RETURN VARCHAR2 as OutCLLI VARCHAR2;
BEGIN
 SELECT CASE str
	 When '416234' Then 'TOROON26'
	 When '416238' Then 'TOROON29'
	 Else 'X'||str||'X' /* Return the initial value surrounded by X to indicate no CASE entry found*/
	End Case
	INTO OutCLLI
FROM dual;
RETURN OutCLLI;
END;
Re: Using Case within a Function [message #210804 is a reply to message #210798] Fri, 22 December 2006 07:58 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
I think you just need to wrap the CASE / END block in parenthesis as such:
(CASE
 ...
 END)


or, if this is 8i, I don't think CASE was supported in PL/SQL in that version.

[Updated on: Fri, 22 December 2006 07:59]

Report message to a moderator

Re: Using Case within a Function [message #210805 is a reply to message #210798] Fri, 22 December 2006 07:59 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Hi,

The CASE statement just needs an END (not an END CASE).

Oops, cross posted with joy_division

[Updated on: Fri, 22 December 2006 08:00]

Report message to a moderator

Still Doesn't work [message #210806 is a reply to message #210798] Fri, 22 December 2006 08:06 Go to previous messageGo to next message
tdudley
Messages: 6
Registered: December 2006
Location: Barrie, Ontario
Junior Member
Thanks but I still have the same problem.

I altered
create or replace FUNCTION FNNX2CLLI(str IN VARCHAR2(6)) RETURN VARCHAR2 as OutCLLI VARCHAR2;
BEGIN
 SELECT (CASE str
	 When '416234' Then 'TOROON26'
	 When '416238' Then 'TOROON29'
	 Else 'X'||str||'X' /* Return the initial value surrounded by X to indicate no CASE entry found*/
	End)
	INTO OutCLLI
FROM dual;
RETURN OutCLLI;
END;
and still have the same error.

PS. I'm using 9i so I should be able to use CASE.

[Updated on: Fri, 22 December 2006 08:10]

Report message to a moderator

Re: Still Doesn't work [message #210813 is a reply to message #210806] Fri, 22 December 2006 08:21 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Hi,

I had a try and got the little red x too.

Then I tried it within a package and found out it didn't seem to like the function declaration so I altered it in the package to :-

create or replace FUNCTION FNNX2CLLI(str IN VARCHAR2) RETURN VARCHAR2 as OutCLLI VARCHAR2(6);


I retried the function with the same syntax and it seemed to be OK.

I haven't tested it though.

[Updated on: Fri, 22 December 2006 08:22]

Report message to a moderator

Re: Using Case within a Function [message #210825 is a reply to message #210798] Fri, 22 December 2006 08:48 Go to previous messageGo to next message
tdudley
Messages: 6
Registered: December 2006
Location: Barrie, Ontario
Junior Member
Discovered that Oracle SQL Developer or Oracle 9i must only like funcion names of 8 characters or less. The following works fine.

Thanks to all for your interest and help.

All the best during the holiday season.
TCD

create or replace FUNCTION FNX2CLLI(p_in_NPAFNNX IN VARCHAR2) RETURN VARCHAR AS OutCLLI VARCHAR2(10);
BEGIN
 SELECT (CASE p_in_NPAFNNX
	 When '416234' Then 'TOROON26'
	 When '416238' Then 'TOROON29'
	 Else 'XXXXXXXXXX'
	End)
	INTO OutCLLI
FROM dual;
RETURN OutCLLI;
END;
Re: Using Case within a Function [message #210829 is a reply to message #210825] Fri, 22 December 2006 09:05 Go to previous messageGo to next message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
I came across with this one and it seems to work fine:

CREATE OR REPLACE FUNCTION FNNX2CLLI(str IN VARCHAR2) RETURN VARCHAR2 AS
  OutCLLI VARCHAR2(10);
BEGIN
  SELECT CASE str
           WHEN '416234' THEN
            'TOROON26'
           WHEN '416238' THEN
            'TOROON29'
           ELSE
            'X' || str || 'X' /* Return the initial value surrounded by X to indicate no CASE entry found*/
         END
    INTO OutCLLI
    FROM dual;
  RETURN OutCLLI;
END;
Re: Using Case within a Function [message #210841 is a reply to message #210829] Fri, 22 December 2006 11:18 Go to previous message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member

I tried the last one given by gbarbisan and it is working fine thanks a lot .

Bye
Ashu
Previous Topic: How to replace all single quote by another character
Next Topic: What is problem with Check Constraint
Goto Forum:
  


Current Time: Sat Dec 10 11:15:53 CST 2016

Total time taken to generate the page: 0.19533 seconds