CODEWITHSUNDEEP
sqldatabaseoracle-database
FIM
FIM

Reputation: 33

Oracle SQL Developer 0913 - too many values

I'm trying to return the MINIMUM VALUE and the MAXIMUM VALUE in my PRICE table(the field is the val_price). What is the problem with this query?

select des_price, val_price from price
WHERE val_price IN
(SELECT MIN(val_price), MAX(val_price) from price);

The error message is:
00913. 00000 - "too many values"
*Cause:
*Action:

If I try with just one value in the inner select everything works fine, but with multiple values I don't know why is not working. This example works fine with 2 values in the follow inner select:

UPDATE price SET val_price = val_price * 1.05
WHERE des_price in('NORMAL','RELEASE');

Upvotes: 0

Views: 123

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93724

You cannot select two columns from sub-query when you are using only one column in Where clause

Try this way 

SELECT des_price,
       val_price
FROM   price
WHERE  val_price IN (SELECT Min(val_price)
                     FROM   price
                     UNION ALL
                     SELECT Max(val_price)
                     FROM   price);

or 

SELECT des_price,
       val_price
FROM   price
WHERE  ( val_price, val_price ) IN (SELECT Min(val_price),
                                           Max(val_price)
                                    FROM   price)

Upvotes: 1

Related Questions