CODEWITHSUNDEEP
sqloracle-database
Tina
Tina

Reputation: 1

Oracle SQL 00907. 00000 - "missing right parenthesis"

I'm trying to insert values into a table from another table. Replacing any possible nulls with 'Undefined', and also I created a sequence to populate one of my columns. I keep getting an error on the second line. 

INSERT INTO PRODUCT_DIM
SELECT PRODUCT_SEQ.NEXTVAL AS PRODUCT_KEY, 
       NVL((PRODUCTID, PRODUCTDESCRIPTION, PRODUCTLINEID),'Undefined')
FROM PRODUCT_T;

Upvotes: 0

Views: 162

Answers (3)

Littlefoot
Littlefoot

Reputation: 142788

I'd say that you're, actually, inserting 4 different columns so you need 4 separate NVLs, but it is difficult to guess (so that's what we're exactly doing - guessing).

If you specified the column list that should be populated in the PRODUCT_DIM table, it would be easier to answer.

INSERT INTO PRODUCT_DIM
  (product_key, 
   productid, 
   productdescription, 
   productlineid
  )
  SELECT PRODUCT_SEQ.NEXTVAL, 
         NVL(PRODUCTID         , 'Undefined'),
         NVL(PRODUCTDESCRIPTION, 'Undefined'),
         NVL(PRODUCTLINEID     , 'Undefined')
  FROM PRODUCT_T;

Upvotes: 0

Kiran
Kiran

Reputation: 3315

You can use the COALESCE function as Connor demonstrates.

With NVL you need to nest the function like shown below:

INSERT INTO PRODUCT_DIM
SELECT PRODUCT_SEQ.NEXTVAL AS PRODUCT_KEY, 
    NVL(PRODUCTID, NVL(PRODUCTDESCRIPTION, NVL(PRODUCTLINEID, 'Undefined')))
FROM PRODUCT_T;

Upvotes: 1

Connor McDonald
Connor McDonald

Reputation: 11591

NVL only takes 2 parameters (maximum). Look at using COALESCE instead, ie.

COALESCE(param1, param2, param3, .... )

Upvotes: 1

Related Questions