SQL works standalone but not wrapped around begin/end

[dooge]

I run the following in sqlplus and get an error.
-------------------------------------
BEGIN
UPDATE account
SET
activity_total = nvl((SELECT 5 FROM dual),0);
END
-------------------------------------

However when I run the following in sqlplus:
-------------------------------------
UPDATE account SET activity_total = nvl((SELECT 5 FROM dual),0);
-------------------------------------
It executes.

The error from the first part:
ERROR at line 4:
ORA-06550: line 4, column 39:
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>

 

[sem]

Your syntax is invalid: you can not apply NVL function to subquery. But I still can not understand what result you're trying to obtain.

Regards, Dima

 

[carp]

You could try
BEGIN
UPDATE account
SET
activity_total = (SELECT NVL(5,0) FROM dual);
END;

I'm assuming you used '5' as a simple example; since '5' will NEVER evaluate to NULL, using NVL makes no sense here.

 

[dooge]

Yes, I simplified the query to my problem. It's quite
a bit more complex than what is shown above. I did end up
doing what carp did above. If the nvl on the function
is invalid, then why did it work at sqlplus without
the begin/end wrapped around it?

 

[dooge]

Actually it didn't solve the problem entirely. The query looks more like:
UPDATE monthly_totals mt
SET
checking_total=nvl(
(SELECT sum(check_amount) FROM checks c
WHERE c.customer_id=mt.customer_id
and c.month=mt.month)
,0),
previous_balance=nvl(
(SELECT balance FROM accounts a
WHERE a.customer_id=mt.customer_id
and a.month=mt.month)
,0)
WHERE
mt.month = 1;

This is not the actual query, but it is close in principle.
Each customer has a row in monthly totals. Suppose the
customer is new and has no previous balance. If I use:

previous_balance=(SELECT nvl(balance,0) FROM accounts a
WHERE a.customer_id=mt.customer_id
and a.month=mt.month)

then previous_balance gets set to NULL, not 0, because
no records were returned. This is why I want to use NVL
like show above. The first query works at sqlplus and not
when wrapped around a BEGIN/END. WHY????

The checking_balance one works because the sum() function
always returns a row. I guess I could use the sum() for
the previous balance like:

previous_balance=(SELECT nvl(sum(balance),0) FROM accounts a
WHERE a.customer_id=mt.customer_id
and a.month=mt.month)

to get what I want. It's just a roundabout way to do
what I want.

 

[sem]

I mean that it's invalid in PL/SQL. In general the syntax used in queries within pl/sql is a bit restricted and normally remains behind SQL used in database. Though in most cases those new features are implemented in the next release. Your query is not reliable, because you may get runtime error if your 2nd subquery returns more than 1 row. You may use some grouping function and rewrite your query as


UPDATE monthly_totals mt
SET
checking_total=
(SELECT nvl(sum(check_amount),0)
FROM checks c
WHERE c.customer_id=mt.customer_id
and c.month=mt.month)
,previous_balance=
(SELECT nvl(max(balance),0)
FROM accounts a
WHERE a.customer_id=mt.customer_id
and a.month=mt.month)
WHERE
mt.month = 1;

Regards, Dima