cptkirkh wrote:
>
> On Jan 29, 1:13 pm, DA Morgan <damor..._at_psoug.org> wrote:
>> Mark D Powell wrote:
>>
>>> On Jan 29, 12:35 pm, "cptkirkh" <k..._at_scic.com> wrote:
>>>> I want to fill a table from a triggered event on another table. The
>>>> field that will be changed in the destination table will be determined
>>>> by what is changed on the source. I think I have set this up right
>>>> but I get an ORA-1733 error on the word "field" in the update
>>>> statement. It says virtual column not allowed here. How can I
>>>> resolve this or what does anybody suggest is a better way to work
>>>> aorund this problem. Or of course is my PL/SQL wrong? Thnaks for
>>>> your help. Here is the PL.SQL
>>>> FIELD VARCHAR2(20);
>>>> x number;
>>>> y number;
>>>> old date;
>>>> new date;
>>>> id varchar2(10);
>>>> BEGIN
>>>> FIELD := CASE
>>>> WHEN x = 1 AND y = 1 THEN 'CLI_CIC_NPDATE'
>>>> ELSE 'NOT SURE'
>>>> END;
>>>> DBMS_OUTPUT.PUT_LINE(FIELD);
>>>> if nvl(old,'01-JAN-1901') <> nvl(new,'01-JAN-1901') and field is not
>>>> null then
>>>> UPDATE DESIGNATION_INFO_TEST SET FIELD = sysdate WHERE
>>>> CLI_RID = id;
>>>> ELSE
>>>> INSERT INTO DESIGNATION_INFO_TEST(CLI_RID,FIELD)
>>>> VALUES (id,sysdate);
>>>> end if;
>>>> end;
>>> You have a pl/sql variable named FIELD and a table column FIELD. It
>>> would appear that the parser is having difficulty telling which one
>>> you are trying to reference. You can find the variable scope rules
>>> defined in the PL/SQL manual for your version.
>>> I suggest you prefix or suffix your pl/sql variable names so that the
>>> pl/sql variable names and table column names are not identical.
>>> HTH -- Mark D Powell --It is worse than that:
>> The following are reserved words:
>> FIELD
>> ID
>> NEW
>> OLD
>>
>> and '01-JAN-1901' is not a date ... it is a string and the OP is relying
>> on implicit conversion which is best practice either.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damor..._at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -- Show quoted text -
>
> Ok so I went in and changed the names to non-reserved words. still
> get the same error. In answer to Mark's statement I don't have two
> field columns. the column in the table that needs to be updated is
> determined on what item is changed in the source table for example if
> x and y both equal one then change the column cli_cic_npdate at the
> row equal to the cli_id. the column could change names. I just left
> out the other possiblities toi shorten what I posted.
Now that you've changed it ... repost it along with your version number
and the DDL for any tables.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Mon Jan 29 2007 - 14:28:54 CST
