Home » Developer & Programmer » Reports & Discoverer » URGENT PL/SQL functions in SQL select statement
URGENT PL/SQL functions in SQL select statement [message #88152] Wed, 17 April 2002 03:29 Go to next message
Anderson
Messages: 10
Registered: December 1999
Junior Member
I have written a function which takes one argument as below:
FUNCTION CHECKING (DPT_CODE VARCHAR2)RETURN VARCHAR2 is
DEPT VARCHAR2(4);

BEGIN
DEPT := :DPT_CODE;
if :DPT_CODE like 'C%'
then
return 'y';
else
return 'n';
end if;
END;
If I use this in the select statement as this select DPT_Code from tableName where Checking(DPT_Code) = 'y'
it doesnt work.

I get an error messege INVALID COLUMN NAME. CAN U PLEASE HELP. I HAVE PUT THIS UNDER THE PROGRAM UNITS IN REPORTS. PLEASE HELP
Re: URGENT PL/SQL functions in SQL select statement [message #88153 is a reply to message #88152] Wed, 17 April 2002 12:08 Go to previous messageGo to next message
Ayanna
Messages: 2
Registered: April 2002
Junior Member
Have you defined the parameter as a variable in the user parameters section of reports?
Re: URGENT PL/SQL functions in SQL select statement [message #88156 is a reply to message #88152] Fri, 19 April 2002 03:50 Go to previous message
Ayan Joshi
Messages: 1
Registered: April 2002
Junior Member
BEGIN
DEPT := :DPT_CODE;
*******if :DPT_CODE like 'C%' *******

use dept here or if you want to use dpt_code then try without ':' i.e.
if dept like 'C%' or if dpt_code like 'C%'
Hope this will help you.
Ayan

then
return 'y';
else
return 'n';
end if;
END;
If I use this in the select statement as this select DPT_Code from tableName where Checking(DPT_Code) = 'y'
it doesnt work.
Previous Topic: Ignorance is no excuse...
Next Topic: Urgent Pls!!!! Discoverer executable report
Goto Forum:
  


Current Time: Sat May 18 11:25:06 CDT 2024