Hello, I am a newbie at Oracle using PL/SQL. I am getting an error PLS-00201- identifier needs to be declared error on the following on the below code, but I am declaring so I do not know where Im doing it wrong, please help!
thanks in advance
DECLARE
Contract varchar(20);
Lives number(10);
BEGIN
Contract:= g.reporting1;
Lives:= e.members;
SELECT CASE
WHEN g.reporting1 = 'IPMOPEN1' THEN 'IPMBASOP'
WHEN g.reporting1 = 'IPMINCT1' THEN 'IPMBASIN'
WHEN g.reporting1 = 'IPMNPINC' THEN 'IPMNPFIN'
WHEN g.reporting1 = 'IPMNPOPN' THEN 'IPMNPFOP'
ELSE RPAD(NVL(SUBSTR(g.reporting1,1,8),' '),8,' ')
END, Sum(e.members)
Into Contract, Lives
FROM laker.groups g inner join laker.eligmonthlyloc e on (g.carriercode=e.carrier and g.GROUPNUM=e.GROUPNUM)
WHERE e.MONTH BETWEEN ADD_MONTHS(TRUNC(SYSDAATE,'Q'),-3) AND (TRUNC(SYSDATE,'Q')-1)
GROUP BY g.reporting1;
END;
the aliases "g" and "e" are only valid inside the SELECT. So it doesn't make sense to use them like this:
Contract:= g.reporting1;
Lives:= e.members;
You can just remove those lines, since your query selects data into those variables. Note with this kind of construction, you will get an error if the query returns more than one row, because these variables only hold one value each. If you need to process multiple rows then you might need a cursor for loop or some other kind of construction.
If you just want to see the data in toad, all you need is the SELECT (without the INTO line).
Hi Grachel Gibbons,
What John said.
If I may add another observation which will save your programming life
at some point in the future, never ever define a variable, which will
receive column data, as a VARCHAR or a CHAR or whatever.
If it is being used to SELECT INTO for example, then you really should
"anchor" the variable to the column definition.
In your code you have:
Contract varchar2(20);
But in an ideal world, which I admit, we don't live in, you would do the
following instead:
Contract LAKER.GROUPS.REORTING1%TYPE;
That way, if anyone ever changes the data type of the column REPORTING1
in the table, your code will simply recompile itself in the background
and you will not have to do anything else to change it.
Lets say that a decision to expand the column to VARCHAR2(30) was taken.
Your code doesn't need to be changed. Ever. (OK, unless they make it a
totally different data type like NUMBER of course!)
Any time you are declaring a variable, to hold data from, or, feed a
value to, a column in a table, always always use the format:
[owner.]table_name.column_name%TYPE
instead of explicitly defining the data type. This will save you lots of
work when things change, or even better, your code will not fall over
with "value to big for variable" errors when they make the column bigger
and you forgot to change your code. (Ask me how I learned this!)
Have fun.
HTH