dds110
asked on
Decode statement?
Help,
I have the following format function
function F_p_actloc1FormatTrigger return boolean is
state_name varchar2(20):='';
begin
decode(:p_actloc, hreport_univ3.state);
return (TRUE);
end;
The decode statement is not working. The user parameter ":p_actloc" contains a state abbreviation (in this instance, "AK"). hreport_univ3 is the table and state is the field. It's been a while since I've played with Oracle. What am I doing wrong? The error is:
Table, View or Sequence Reference "HREPORT_UNIV3.STATE" not allowed in this context.
Here is the underlying query in the report:
select a.handler_id, a.activity_location,
a.universe, a.justification, to_char(a.event_date,'MM/D
b.handler_name, b.location_city
from huniverse_just a, hreport_univ3 b
where a.handler_id = b.handler_id
and a.activity_location = b.activity_location
and a.handler_id = :p_hid
and a.activity_location = :p_actloc
order by a.handler_id, a.universe, a.event_date desc
TIA
DDS
I have the following format function
function F_p_actloc1FormatTrigger return boolean is
state_name varchar2(20):='';
begin
decode(:p_actloc, hreport_univ3.state);
return (TRUE);
end;
The decode statement is not working. The user parameter ":p_actloc" contains a state abbreviation (in this instance, "AK"). hreport_univ3 is the table and state is the field. It's been a while since I've played with Oracle. What am I doing wrong? The error is:
Table, View or Sequence Reference "HREPORT_UNIV3.STATE" not allowed in this context.
Here is the underlying query in the report:
select a.handler_id, a.activity_location,
a.universe, a.justification, to_char(a.event_date,'MM/D
b.handler_name, b.location_city
from huniverse_just a, hreport_univ3 b
where a.handler_id = b.handler_id
and a.activity_location = b.activity_location
and a.handler_id = :p_hid
and a.activity_location = :p_actloc
order by a.handler_id, a.universe, a.event_date desc
TIA
DDS
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hmmmmmmmmm, I didn't write this, so please forgive any "wrong doings".
What I'm trying to do is return the Full State name from the table lu_state from the parameter value, which is the state abbreviation. (forgot to mention lu_state in my previous post)
Lu_state has "Postal_Code" which is the abbreviation and "State_Name" (self explanatory).
What I'm trying to do is return the Full State name from the table lu_state from the parameter value, which is the state abbreviation. (forgot to mention lu_state in my previous post)
Lu_state has "Postal_Code" which is the abbreviation and "State_Name" (self explanatory).
No criticism intended. I'm not a reports expert either, so, hopefully someone else will chime in...
Are you getting the same error with the code changed?
Are you getting the same error with the code changed?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hey, found my problem. I was attempting to use the decode statement in place of where another user had written something to the effect of
decode_state(:p_actloc);
I figured out that "decode_state..." is a procedure that was not included in the report.
However, for your help, I'll split the points. I'm gonna need all the help I can get in the coming months on Oracle.
Thanks guys (or gals)...
decode_state(:p_actloc);
I figured out that "decode_state..." is a procedure that was not included in the report.
However, for your help, I'll split the points. I'm gonna need all the help I can get in the coming months on Oracle.
Thanks guys (or gals)...
I don't think you can use decode in straight code like this, you should use if.
function F_p_actloc1FormatTrigger return boolean is
begin
if :p_actloc = hreport_univ3.state then
return (TRUE);
else
return (FALSE);
end;