Problem using Function in a SQL statement in oracle7.1

From: Vijayachandran J <vijayj_at_novell.com>
Date: 1995/10/31
Message-ID: <3096A44E.DBE_at_novell.com>#1/1

I have the following problem:

Problem : Decode function is not working in function used in SQL statement.

Scenario:

Function with Decode

---

create or replace function fum( var1 in char, amount in number ) return number is         

        local_value(20, 2 );
begin

	select decode (var1, 'C', amount, -amount ) into 
		local_value from dual;
	return( local_value );

end;

Execution of function

---

SQL > select fum( 'C', 200 ) from dial;

        select fum( 'C', 200 ) from dual

---

Result : Error at line 1:
ORA - 1403 no data found

Function without Decode:

---

create or replace function fnum( var1 in char, amount in number)  return number is

        local_value number( 20, 2)
begin

	select amount into local_value from dual;
	return ( loacal_value );

end;

Execution of the function:

---

SQL > select fnum( 'C', 200 ) from dual;

        FUM( 'C', 200 )

---

        200

Procedure with DECODE:

---

create or replace procedue fum1( var1 in char, amount in number) is
begin
  declare

        local_value number(20, 2);
  begin

	select decode( var1, 'C', amount, -amount ) into 
		local_value from dual;
	dbms_output.put_line( 'The value of the local_value is'
			|| to_char( local_value ) ); 

  end;
endl

Execution of the procedure:

---

SQL > set serveroutput on:
SQL > exec fum1( 'C' 200 );

      The value of the local_value is 200 SQL > exec fum1( 'D', 200 );

      The value of the local_value is -200

Thanking you in advance,

( vijayj_at_novell.com ) Received on Tue Oct 31 1995 - 00:00:00 CET