CODEWITHSUNDEEP
databaseoracle-database
Schavcovsky
Schavcovsky

Reputation: 81

Nested SELECT in Oracle

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 ?

Upvotes: 0

Views: 69

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

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.

Upvotes: 1

user5683823
user5683823

Reputation:

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));

Upvotes: 1

Related Questions