How to construct this Oracle SQL query?

Question

I am writing a sql query in Oracle that's 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 is saying 'longitude invalid identifier'. I was trying to access sub query alias, but the query is failing.

How can I access 'alias' of one sub query into another sub query?

Answer 1

You asked how to access the alias of one sub query into another... at a quick glance this is one way to do it by using two "simulated tables". You can use select statements like a table like this. Not sure if this is your best solution but should get 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 method of subquery just add two selects for the two columns of latitude and longitude and refer to the external query by alias. This seems like it would be poor performance but Oracle does a great job with this type query. No group needed with the single column select. I'd think about returning empty string rather than null unless you need it. I'm not sure if Oracle will like the null or not for the "else" situation. I suppose it must be working for you that 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 final comment. The second query will not get what OP wanted as longitude and latitude values are used in the computation. It is one example of nested query but not a solution for OP's question. Sorry if it mislead anyone.

Answer 2

If I'm understanding correctly what you're trying to do, you don't actually need the INNER JOIN, because you're not taking any real information from test that's not already 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 explicitly ensure that you only get records that exist in test — that is, if you have records in test_av that don't have parents in test, and you want to filter those out — then you can handle that in the innermost subquery, after your FROM test_av.

Answer 3

Well, I'm a SQL Server maven, and not an Oracle guru, but I think that 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 that whole CAST(MULTISET(..)) thing as I know nothing about it other than it was some black magic that was given to me on a past project to get a SQL Server CROSS APPLY query to work on Oracle.