Home » SQL & PL/SQL » SQL & PL/SQL » FN return error : invalid identifier
FN return error : invalid identifier [message #311840] Mon, 07 April 2008 02:19 Go to next message
iriswancy
Messages: 17
Registered: December 2005
Location: Malaysia
Junior Member
Hi,

I have created a FN (shown at below) to return a value :

CREATE OR REPLACE FUNCTION "FN_AGNT_PS_QUOTA_IRIS" (i_agent_rank in varchar2,i_ps_amt in number)
return number
as
v_mysql_1 varchar2(1000);
r_ps_quota number(19,2);
Begin

if i_agent_rank = 'AM' and
i_ps_amt between ('select personal_sales from
(select a.*,dense_rank() over
(partition by from_agent_rank,to_agent_rank order by personal_sales) as rank
from t_promodemo_criteria a
where rectype = ''L'' and promodemo_type = ''P'' and criteria_code = ''F''
and from_agent_rank = ''AM'' and to_agent_rank = ''GAM'') where rank = 1') and
('select personal_sales from
(select a.*,dense_rank() over
(partition by from_agent_rank,to_agent_rank order by personal_sales) as rank
from t_promodemo_criteria a
where rectype = ''L'' and promodemo_type = ''P'' and criteria_code = ''F''
and from_agent_rank = ''AM'' and to_agent_rank = ''GAM'') where rank = 2') then
v_mysql_1 :=
'select personal_sales from
(select a.*,dense_rank() over
(partition by from_agent_rank,to_agent_rank order by personal_sales) as rank
from t_promodemo_criteria a
where rectype = ''L'' and promodemo_type = ''P'' and criteria_code = ''F''
and from_agent_rank = ''AM'' and to_agent_rank = ''GAM'') where rank = 1';
EXECUTE IMMEDIATE v_mysql_1
INTO r_ps_quota;
end if;

return nvl(r_ps_quota,0);

EXCEPTION
WHEN OTHERS THEN
r_ps_quota:=0;
return r_ps_quota;
end;
/


However, i keep get this error ORA:00904 Invalid Identifier whenever i try to run this select statament :

select fn_agnt_ps_quota_iris('AM','300000') from dual

Pls help.

Thanks & Regards,
Iris
Re: FN return error : invalid identifier [message #311846 is a reply to message #311840] Mon, 07 April 2008 02:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is NOT an expert question.

It is clearly stated in the forum description: "Newbies should not post to this forum!"
Expert notion is defined in the sticky: Not an EXPERT? Post in the NEWBIES forum, NOT here
Rules are described in: OraFAQ Forum Guide
Read them, follow them including what is said about formatting.

As you are an expert, I don't answer the question because you obviously already knows it.

Regards
Michel
Re: FN return error : invalid identifier [message #312885 is a reply to message #311846] Thu, 10 April 2008 01:02 Go to previous messageGo to next message
iriswancy
Messages: 17
Registered: December 2005
Location: Malaysia
Junior Member
Sorry...
Re: FN return error : invalid identifier [message #312890 is a reply to message #311840] Thu, 10 April 2008 01:13 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
check whether your function is created without any compilation errors i.e. is in valid state or not.
Re: FN return error : invalid identifier [message #312894 is a reply to message #312890] Thu, 10 April 2008 01:20 Go to previous messageGo to next message
iriswancy
Messages: 17
Registered: December 2005
Location: Malaysia
Junior Member
Thanks for reply!

I have compile the FN and it is in valid state. No error.

Im still new in creating FN and just wondering is my FN is written in correctly?

Thanks & Regards
Re: FN return error : invalid identifier [message #312901 is a reply to message #312894] Thu, 10 April 2008 01:30 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Did you create it using the same user as you use for calling it?

By the way, such a beast is called a function, not an FN.
Re: FN return error : invalid identifier [message #312909 is a reply to message #312894] Thu, 10 April 2008 01:46 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
I think you have to re-think your function completely.

- What's the purpose of the IF-statement?
- What do you try to test here:

 i_ps_amt between ( '....' ) AND ( '...' )


I'm pretty sure that in this case the IF-statement will always be FALSE.

And Frank is right: probably function has been created with user X and you try to use it with user Y.
Re: FN return error : invalid identifier [message #312916 is a reply to message #311840] Thu, 10 April 2008 02:01 Go to previous messageGo to next message
iriswancy
Messages: 17
Registered: December 2005
Location: Malaysia
Junior Member
The way i'm writing this statement

if i_ps_amt between (.....) and (.....)

is for the this purpose :

if i_ps_amt between 300000 and 450000 then
return ps_quota = 500000
end if

As check again,Frank you are rite as i am really using different user. Thanks Smile !

Now when i execute the Function and it return result now and MarcS you are rite too and it keep return false result. Then i will have to write the statement in another way.

Thanks guys!!

Re: FN return error : invalid identifier [message #312918 is a reply to message #312916] Thu, 10 April 2008 02:06 Go to previous message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
iriswancy wrote on Thu, 10 April 2008 09:01
The way i'm writing this statement

if i_ps_amt between (.....) and (.....)

is for the this purpose :

if i_ps_amt between 300000 and 450000 then
return ps_quota = 500000
end if

As check again,Frank you are rite as i am really using different user. Thanks Smile !

Now when i execute the Function and it return result now and MarcS you are rite too and it keep return false result. Then i will have to write the statement in another way.

Thanks guys!!




Actually you're comparing i_ps_amt with two strings !!!!

Previous Topic: Regarding Pseudo Columns
Next Topic: Compilation errors
Goto Forum:
  


Current Time: Sun Dec 04 20:47:00 CST 2016

Total time taken to generate the page: 0.14163 seconds