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?

decode is not a valid builtin function AFAIK, and that's not valid syntax for calling a UDF named "decode". So what's going on there? — RBarryYoung
– RBarryYoung, Commented Oct 9, 2012 at 20:42
@RBarryYoung: It's a completely valid builtin function; see docs.oracle.com/cd/B12037_01/server.101/b10759/functions036.htm. decode(name, 'longitude', stringvalue, NULL) is (approximately?) equivalent to CASE WHEN name = 'longitude' THEN stringvalue END. — ruakh
– ruakh, Commented Oct 9, 2012 at 20:44
Oops sorry. Got confused, thought it was SQL Server. My bad... — RBarryYoung
– RBarryYoung, Commented Oct 9, 2012 at 20:45
Why is the Haversine subquery nested twice? It seems redundant since the outer one isn't doing anything? — RBarryYoung
– RBarryYoung, Commented Oct 9, 2012 at 20:47
As mentioned by someone else one problem is the inner join you don't need that. Just use where t.id = av.id for the join in Oracle, and when using aliases be consistent and use them throughout — Rich Bianco
– Rich Bianco, Commented Oct 9, 2012 at 21:08

ruakh · Accepted Answer · 2012-10-09 21:10:15Z

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.

edited Oct 9, 2012 at 21:10

answered Oct 9, 2012 at 20:49

ruakh

185k29 gold badges292 silver badges324 bronze badges

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

6 Comments

user964147 Over a year ago

thank you very much for your reply this query is also failing and Oracle is saying 'FROM keyword not found where expected'

user964147 Over a year ago

I provided some test data that might be interesting to you

ruakh Over a year ago

@user964147: Good idea; that allowed me to find my mistake very quickly. Apparently Oracle doesn't like SELECT *, ... for some reason. Anyway, I've fixed my query now. :-)

user964147 Over a year ago

can you please post your query

ruakh Over a year ago

@user964147: I did. By "I've fixed my query", I mean that I edited my answer to give a query that works.

|

RBarryYoung · Accepted Answer · 2012-10-09 20:55:03Z

1

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.

answered Oct 9, 2012 at 20:55

RBarryYoung

57k15 gold badges99 silver badges142 bronze badges

1 Comment

Rich Bianco Over a year ago

fyi - Barry, you have the right idea just don't need to explicitly cast the second select as a table. In Oracle you don't need to cast a select as a table, Oracle will treat the select like a table if you surround it with parenthesis and give it an alias. So you could remove the TABLE ( CAST ( and ending parens. And I think the alias reference will work fine the way you have it.

Rich Bianco · Accepted Answer · 2012-10-09 21:33:52Z

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.

Collectives™ on Stack Overflow

How to construct this Oracle SQL query?

3 Answers 3

6 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related