Nested SELECT in Oracle

Question

I'm trying to do as follows,

    SELECT ID_ESTACIONAMIENTO
    FROM ESTACIONAMIENTO
    WHERE '-33.52053824804071,-70.68874349999999' 
= (SELECT LATITUD_ESTACIONAMIENTO||','||LONGITUD_ESTACIONAMIENTO FROM ESTACIONAMIENTO);

But returns

ORA-01427: single-row subquery returns more than one row
01427. 00000 -  "single-row subquery returns more than one row"
*Cause:    
*Action:

How can I get ID_ESTACIONAMIENTO WHERE values (in this case '-33.52053824804071,-70.68874349999999') are being compared with a concat query ?

Tim Biegeleisen · Accepted Answer · 2016-10-31 23:53:49Z

1

I think a better approach is to just check the latitude and longitude columns separately:

SELECT ID_ESTACIONAMIENTO
FROM ESTACIONAMIENTO
WHERE LATITUD_ESTACIONAMIENTO = '-33.52053824804071' AND
      LONGITUD_ESTACIONAMIENTO = '-70.68874349999999'

Note that you may not need to use quotes if your latitude/longitude data be stored in a numeric column.

If you want to "fix" your exact current query, then just use the concatenated columns from the outer query directly in your WHERE clause:

SELECT ID_ESTACIONAMIENTO
FROM ESTACIONAMIENTO
WHERE LATITUD_ESTACIONAMIENTO||','||LONGITUD_ESTACIONAMIENTO =
      '-33.52053824804071,-70.68874349999999'

By the way, the reason behind your error, which is self-explanatory, is that the subquery returns more than one row. Actually, it should return every row from the ESTACIONAMIENTO table.

edited Oct 31, 2016 at 23:53

answered Oct 31, 2016 at 23:47

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user5683823 · Accepted Answer · 2016-10-31 23:51:09Z

1

You don't need a subquery, and you don't need to concatenate either.

SELECT ID_ESTACIONAMIENTO
FROM   ESTACIONAMIENTO
WHERE  LATITUD_ESTACIONAMIENTO  = -33.52053824804071
  AND  LONGITUD_ESTACIONAMIENTO = -70.68874349999999;

If the input is a string that concatenates latitude and longitude, you should break it up instead of concatenating the values from the table. Something like - if the input is str = '-33.52053824804071,-70.68874349999999' -

... WHERE LATITUD_ESTACIONAMIENTO  = to_number(substr(str, 1, instr(str, ',') - 1))
      AND LONGITUD_ESTACIONAMIENTO = to_number(substr(str, instr(str, ',') + 1));

answered Oct 31, 2016 at 23:51

user5683823

Collectives™ on Stack Overflow

Nested SELECT in Oracle

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related