How to handle subquery that returns multiple rows in Oracle?

Question

I am writing a bit complex query for my project I have two tables like shown below test, test_Av

Test:

 CREATE table test(id int not null, title varchar(50), place
     varchar(20), postcode varchar(20), primary key(id));

          INSERT INTO test VALUES(1,'test','','91982'); 
          INSERT INTO test VALUES(2,'test','','91982'); 
          INSERT INTO test VALUES(3,'test','','91982'); 
          INSERT INTO test VALUES(4,'test','','91982'); 
          INSERT INTO test VALUES(5,'test','','91982');

Test_AV

    CREATE table test_AV(id int not null, testid int, name varchar(25),
     stringvalue varchar(25) ,primary key(id));

INSERT INTO test_AV VALUES(1,1,'latitude','16.15074'); 
INSERT INTO test_AV VALUES(2,1,'longitude', '-22.74426'); 
INSERT INTO test_AV VALUES(3,2,'latitude','16.13725'); 
INSERT INTO test_AV VALUES(4,2,'longitude', '-22.85822'); 
INSERT INTO test_AV VALUES(5,3,'latitude','14.85633'); 
INSERT INTO test_AV VALUES(6,3,'longitude', '-24.72379'); 
INSERT INTO test_AV VALUES(7,4,'latitude','14.86949'); 
INSERT INTO test_AV VALUES(8,4,'longitude', '-24.70150'); 
INSERT INTO test_AV VALUES(9,5,'latitude','15.03118'); 
INSERT INTO test_AV VALUES(10,5,'longitude', '-24.32523');

testid in test_AV table is the foreign key

I need to find the results that are in radius 100 km from a give 'latitude' and 'longitude' to achieve this I wrote the query some thing like this

select * from (   SELECT id, 
     title, 
     ((ACOS(
           SIN(16.15074 * 3.141592653 / 180) 
           * SIN((SELECT stringvalue from test_AV WHERE name='latitude')
           * 3.141592653 / 180) 
           + COS(16.15074 * 3.141592653 / 180) 
           * COS((SELECT stringvalue from test_AV WHERE name='latitude') 
           * 3.141592653 / 180)
           * COS((-22.74426 - (SELECT stringvalue from test_AV WHERE name='longitude'))
           * 3.141592653 /180))*6373))    AS distance   FROM
 test ) t where distance <= 100

In the place of sub query with in the sub query I need all latitude and longitude values that are in the test_AV Table, When I wrote this it is saying subquery is saying subquery returns multiple rows

How can I do this?

adding some whitespace would probably increase the readability of your question. — wildplasser
– wildplasser, Commented Oct 8, 2012 at 22:29
your subqueries SELECT stringvalue from test_AV WHERE name='latitude' are uncorrelated to the main query. You need to match them up (probably by test_id). Also the lat/lon things appear to be strings, so you are relying on implicit type-coercions. — wildplasser
– wildplasser, Commented Oct 8, 2012 at 22:48

DimaA6_ABC · Accepted Answer · 2012-10-09 06:47:57Z

1

As wildplasser correctly mentioned, you need to correlate subqueries with main query - see the query which would work below, notice WHERE av.testid = ts.id clauses in subqueries:

select * from (   SELECT id,  
 title,  
 ((ACOS( 
       SIN(16.15074 * 3.141592653 / 180)  
       * SIN((SELECT stringvalue from test_AV av WHERE av.testid = ts.id and name='latitude')  
       * 3.141592653 / 180)  
       + COS(16.15074 * 3.141592653 / 180)  
       * COS((SELECT stringvalue from test_AV av WHERE av.testid = ts.id and name='latitude')  
       * 3.141592653 / 180)  
       * COS((-22.74426 - (SELECT stringvalue from test_AV av WHERE av.testid = ts.id and name='longitude'))  
       * 3.141592653 /180))*6373))    AS distance   FROM  
test ts ) t where distance <= 100

That was to fix your query. But there is a technique of pivoting Oracle rows to columns. You can get a dataset where your longitude and latitude rows are assotiated with testid in one row:

select testid,
max(decode(name, 'longitude', to_number(stringvalue), NULL)) longitude,  
max(decode(name, 'latitude', to_number(stringvalue), NULL)) latitude  
from test_AV  
group by testid

Now just join this table with test.

edited Oct 9, 2012 at 6:47

answered Oct 9, 2012 at 6:38

DimaA6_ABC

6226 silver badges18 bronze badges

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

1 Comment

user964147 Over a year ago

thank you very much for your answer, when i tried the above query(big query) Oracle is throwing 'invalid number' message, what might be the reason ?

Collectives™ on Stack Overflow

How to handle subquery that returns multiple rows in Oracle?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related