How to build this Oracle SQL query?

Question

I am writing an SQL query in Oracle, something like this:

SELECT * FROM ( SELECT testid, max(decode(name, 'longitude', stringvalue, NULL)) as longitude, max(decode(name, 'latitude', stringvalue, NULL)) as latitude FROM test_av GROUP BY testid ) av INNER JOIN ( SELECT id, ((ACOS( SIN(16.15074 * 3.141592653 / 180) * SIN(latitude * 3.141592653 / 180) + COS(16.15074 * 3.141592653 / 180) * COS(latitude * 3.141592653 / 180) * COS((-22.74426 - longitude)*3.141592653 / 180) )*6373)) as distance FROM test ) t ON t.id = av.testid WHERE t.distance <= 100 

When I execute this query, Oracle says 'longitude invalid identifier' . I tried to access the subquery alias, but the query is not working.

How can I access the "alias" of one subquery in another query?

Answer 1

If I understand correctly what you are trying to do, you really do not need an INNER JOIN , because you are not accepting any real information from test that is not yet on test_av . So you can write:

 SELECT * FROM ( SELECT id, longitude, latitude, ((ACOS( SIN(16.15074 * 3.141592653 / 180) * SIN(latitude * 3.141592653 / 180) + COS(16.15074 * 3.141592653 / 180) * COS(latitude * 3.141592653 / 180) * COS((-22.74426 - longitude) * 3.141592653 / 180) )*6373)) AS distance FROM ( SELECT testid AS id, max(decode(name, 'longitude', stringvalue, NULL)) as longitude, max(decode(name, 'latitude', stringvalue, NULL)) as latitude FROM test_av GROUP BY testid ) ) WHERE distance <= 100 ; 

If you want to make sure explicitly that you only get records that exist in test - that is, if you have records in test_av that have no parents in test , and you want to filter them out - then you can handle this in the innermost subquery, after your FROM test_av .

Answer 2

Well, I am a SQL Server maven, not an Oracle guru, but I think you want something like this:

 SELECT * FROM ( SELECT testid, max(decode(name, 'longitude', stringvalue, NULL)) as longitude, max(decode(name, 'latitude', stringvalue, NULL)) as latitude FROM test_av av GROUP BY testid ) av, TABLE ( CAST (MULTISET( SELECT id, ((ACOS( SIN(16.15074 * 3.141592653 / 180) * SIN(latitude * 3.141592653 / 180) + COS(16.15074 * 3.141592653 / 180) * COS(latitude * 3.141592653 / 180) * COS((-22.74426 - longitude)*3.141592653 / 180) )*6373)) as distance FROM test ti WHERE ti.id = av.testid ) )) t WHERE t.distance <= 100 

You will need to check the whole thing CAST (MULTISET (..)), since I know nothing about it, except for some black magic that was provided to me in the last project in order to receive the query SQL Server CROSS APPLY work on Oracle

Answer 3

You asked how to access the alias of one subquery in another ... with a quick glance, this is one way to do this using two "simulated tables". You can use select statements similar to a table. Not sure if this is your best solution, but should help you on the right track.

 SELECT testav.*, testt.* FROM ( SELECT av.testid as id, max(decode(av.name, 'longitude', stringvalue, NULL)) as longitude, max(decode(av.name, 'latitude', stringvalue, NULL)) as latitude FROM test_av av GROUP BY av.testid ) testav, (SELECT t.id as id, ((ACOS( SIN(16.15074 * 3.141592653 / 180) * SIN(t.latitude * 3.141592653 / 180) + COS(16.15074 * 3.141592653 / 180) * COS(t.latitude * 3.141592653 / 180) * COS((-22.74426 - t.longitude)*3.141592653 / 180) )*6373)) as distance FROM test t ) testt WHERE testav.id = testt.id and testt.distance <= 100 

Another subquery method simply adds two selections for two columns of latitude and longitude and refers to an external query by alias. It seems like it will be poor performance, but Oracle does a great job with this type of query. There is no need for a single column group. I would think about returning an empty string, not null if you don't need it. I'm not sure if Oracle will like zero or not for the "else" situation. I suppose it should work for you this way.

 SELECT id, ACOS(..snipped details..)*6373) as distance, (SELECT max(decode(av.name, 'longitude', stringvalue, NULL)) FROM test_av WHERE test_av.testid = av.id) as longitude, (SELECT max(decode(av.name, 'latitude', stringvalue, NULL)) FROM test_av WHERE test_av.testid = av.id) as latitude FROM test_av av WHERE av.distance <= 100 

Adding a final comment. The second request will not get what the OP requires, since the longitude and latitude are used in the calculation. This is one example of a subquery, but not a solution to the OP question. Sorry if he is misleading anyone.