We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Decode statement?

dds110
dds110 asked
on
Medium Priority
1,432 Views
Last Modified: 2008-02-01
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
Comment
Watch Question

Commented:
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;
Commented:
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;

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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).

Commented:
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?
Database Administrator
CERTIFIED EXPERT
Commented:
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.


Author

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)...
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.