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/DD/RRRR') event_date,
       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
LVL 8
dds110Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RCorfmanCommented:
Are you getting an error message on compile?
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;
0
RCorfmanCommented:
stupid me. forgot the end if;

function F_p_actloc1FormatTrigger return boolean is
begin
 if :p_actloc = hreport_univ3.state then
  return (TRUE);
 else
  return (FALSE);
 end if;
end;
0
dds110Author Commented:
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).
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

RCorfmanCommented:
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?
0
Mark GeerlingsDatabase AdministratorCommented:
The basic problem is that "decode" is only valid in SQL statements like: select..., insert..., etc.  It is not a valid keyword in PL\SQL, so that's why the PL\SQL compiler complains and says:
Table, View or Sequence Reference "HREPORT_UNIV3.STATE" not allowed in this context.


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dds110Author Commented:
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)...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.