Re: update with subselect

Tom,

Sorry to say it, but that's just life with PL/SQL. I think you'll have to use a cursor and do your update one row at a time. It's one of those cases where PL/SQL can't do quite everything SQL*Plus can.

Good luck!
- Catherine
http://profiles.yahoo.com/arcticturtle

In article <e6ec512f.0109232327.7b70d756_at_posting.google.com>, Tom <tomsonn_at_gmx.net> writes:
>Hi everybody,
>
>I've an update-stmt that works fine in sql-plus, but if i try to
>compile it inside an procedure it fails with the following error:
>
>PLS-00103: Encountered the symbol "SELECT" when expecting one of
>the following:
>( - + mod not null others <an identifier>
><a double-quoted delimited-identifier> <a bind variable> avg
>count current exists max min prior sql stddev sum variance
>execute forall time timestamp interval date
><a string literal with character set specification>
><a number> <a single-quoted SQL string>
>
>update-stmt:
>
>update
> rep_account rep_account1
>set
> rep_account1.percentage =
> rep_account1.debit /
> ( select distinct
> rep_account2.debit
> from
> rep_account rep_account2
> where
> rep_account2.rcv_internalid =
>rep_account1.rcv_internalid
> and rep_account2.pos_id =
>pkg_gloconst.pos_accountbalance )
> * 100
>where
> rep_account1.rcv_internalid = ext_rcv_row.internalid
> and rep_account1.pos_id in ( pkg_gloconst.pos_ccy_balance,
>
>pkg_gloconst.pos_accountbalance_all )
>;
>
>
>any ideas? thnx in advance
>Tom

• Posted via NewsOne.Net: Free (anonymous) Usenet News via the Web ----- http://newsone.net/ -- Free reading and anonymous posting to 60,000+ groups NewsOne.Net prohibits users from posting spam. If this or other posts made through NewsOne.Net violate posting guidelines, email abuse_at_newsone.net