(Yet another) "Missing Right Parenthesis"

Question

I am using 10g, and am trying to do some simple calculation and then save the result in a column. The actual tables have many more columns, but here are the what I am using in my query:

CREATE TABLE "VACCINE_LOT"
(
  "VACCINE_LOT_ID"   NUMBER(10,0) NOT NULL ENABLE,
  "DOSE"             NUMBER(6,3),
  "QUANTITY_ON_HAND" NUMBER(12,2) NOT NULL ENABLE
)
CREATE TABLE "IMMUNIZATION"
(
  "VACCINE_LOT_ID" NUMBER(10,0),
  "DOSE_MAGNITUDE" NUMBER(4,2)
)
CREATE TABLE "VACCINE_LOT_TRANSACTION"
(
  "VACCINE_LOT_ID" NUMBER(10,0) NOT NULL ENABLE,
  "QUANTITY"       NUMBER(12,2) NOT NULL ENABLE
)
INSERT INTO vaccine_lot VALUES (100, 0.2, 120);
INSERT INTO immunization VALUES (100, 0.2);
INSERT INTO immunization VALUES (100, 0.3);
INSERT INTO vaccine_lot_transaction VALUES (100, 150);

Immunization shots are taken from a vaccine lot. 'Dose_magnitude' is how much a particular immunization shot uses from a lot. The 'Dose' column in vaccine_lot tells how much to use for a standard immunization shot. So a standard shot may be 0.1cc. But one immunization shot may actually use 0.2cc or even 0.05cc. The 'Quantity' column in vaccine_lot_transaction records originally how many standard immunization shots a vaccine lot contains.

What I am trying to do here is to calculate the correct 'Quantity_on_hand' for vaccine lots (that is, how many standard immunization shots are still left for vaccine lots).

Here is an example using the data we just inserted. We have a vaccine lot (lot ID is '100'), and it starts off with 150 standard shots (that is, it contains 150 0.2cc shots). There are two immunization shots from this lot already, one 0.2cc, the other 0.3cc). And the current quantity of 120 is obviously wrong, and we need to recalculate and update it.

Here is my query:

UPDATE vaccine_lot V SET quantity_on_hand =
(
 (
   (SELECT T.quantity * V.dose FROM vaccine_lot_transaction T WHERE V.vaccine_lot_id = T.vaccine_lot_id) -
   (SELECT SUM(I.dose_magnitude) FROM immunization I WHERE I.vaccine_lot_id = V.vaccine_lot_id)
 ) / dose
);

And sure enough, Oracle starts to complain about "missing right parenthesis". Looks like it thinks there is something syntactically wrong.

Can anyone help take a look at this query and see what's wrong with it? Thanks!

This is what I get when I run it through SQL*PLUS:

    SQL> run
  1  UPDATE vaccine_lot V SET quantity_on_hand =
  2  (
  3   (
  4     (SELECT T.quantity * V.dose FROM vaccine_lot_transaction T
  5        WHERE V.vaccine_lot_id = T.vaccine_lot_id) -
  6     (SELECT SUM(I.dose_magnitude) FROM immunization I
  7        WHERE I.vaccine_lot_id = V.vaccine_lot_id)
  8   ) / dose
  9* );
      WHERE V.vaccine_lot_id = T.vaccine_lot_id) -
                                                 *
ERROR at line 5:
ORA-00907: missing right parenthesis

By the way, I am using version 10.2.0.1.0 of SQL*Plus. I get the same result when using SQL Developer (version 3.0.04).

Can anyone help take a look at this issue? Thanks!

Answer 1

I cut and pasted your code and it seems to work for me (I believe the end result of 147.5 is correct). Are you sure that you didn't accidentally simplify the problem too far?

SQL> CREATE TABLE "VACCINE_LOT"
  2  (
  3    "VACCINE_LOT_ID"   NUMBER(10,0) NOT NULL ENABLE,
  4    "DOSE"             NUMBER(6,3),
  5    "QUANTITY_ON_HAND" NUMBER(12,2) NOT NULL ENABLE
  6  );

Table created.

SQL> CREATE TABLE "IMMUNIZATION"
  2  (
  3    "VACCINE_LOT_ID" NUMBER(10,0),
  4    "DOSE_MAGNITUDE" NUMBER(4,2)
  5  );

Table created.

SQL> CREATE TABLE "VACCINE_LOT_TRANSACTION"
  2  (
  3    "VACCINE_LOT_ID" NUMBER(10,0) NOT NULL ENABLE,
  4    "QUANTITY"       NUMBER(12,2) NOT NULL ENABLE
  5  );

Table created.

SQL> INSERT INTO vaccine_lot VALUES (100, 0.2, 120);

1 row created.

SQL> INSERT INTO immunization VALUES (100, 0.2);

1 row created.

SQL> INSERT INTO immunization VALUES (100, 0.3);

1 row created.

SQL> INSERT INTO vaccine_lot_transaction VALUES (100, 150);

1 row created.

SQL> commit;

Commit complete.

SQL> UPDATE vaccine_lot V SET quantity_on_hand =
  2  (
  3   (
  4     (SELECT T.quantity * V.dose FROM vaccine_lot_transaction T WHERE V.vacci
ne_lot_id = T.vaccine_lot_id) -
  5     (SELECT SUM(I.dose_magnitude) FROM immunization I WHERE I.vaccine_lot_id
 = V.vaccine_lot_id)
  6   ) / dose
  7  );

1 row updated.

SQL> select * from vaccine_lot;

VACCINE_LOT_ID       DOSE QUANTITY_ON_HAND
-------------- ---------- ----------------
           100         .2            147.5