Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Decode statement?

Posted on 2006-03-20
6
Medium Priority
?
1,416 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
0
Comment
Question by:dds110
  • 3
  • 2
6 Comments
 
LVL 16

Expert Comment

by:RCorfman
ID: 16236769
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
 
LVL 16

Assisted Solution

by:RCorfman
RCorfman earned 1000 total points
ID: 16236777
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
 
LVL 8

Author Comment

by:dds110
ID: 16236905
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Expert Comment

by:RCorfman
ID: 16236956
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
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 1000 total points
ID: 16236977
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
 
LVL 8

Author Comment

by:dds110
ID: 16237007
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

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question