Oracle – Joining Subquery from Same Table on 1=1

join;oracle

I have a table with zip codes, latitude and longitude. I have a working query that takes a radius and latitude and longitude and returns all the zip codes within the required radius:

        SELECT zip.zip,
                ROUND(SDO_GEOM.SDO_DISTANCE((MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(:clng, :clat, NULL), NULL, NULL)), (
                            SELECT geom
                            FROM standard_zip
                            WHERE zip = zip.zip
                            ), 0.005) / 1609.34, 2) AS dist
            FROM standard_zip zip
            WHERE zip.zip IN (
                    SELECT DISTINCT (zip) AS zip
                    FROM standard_zip c
                    WHERE SDO_FILTER(c.geom, SDO_UTIL.CIRCLE_POLYGON(:clng, :clat, :r, 5)) = 'TRUE'
                        AND SDO_RELATE(c.geom, SDO_UTIL.CIRCLE_POLYGON(:clng, :clat, :r, 5), 'mask=ANYINTERACT querytype = WINDOW') = 'TRUE'
                    )
            ORDER BY DIST

Since the lat and long are already in the table I would like to provide a zip code as input for the query instead of having to do a separate lookup first, but this just seems to churn forever..

SELECT 
    zip.zip,
    ROUND(SDO_GEOM.SDO_DISTANCE((MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(inp.lng, inp.lat, NULL), NULL, NULL)), (SELECT geom FROM standard_zip WHERE zip = zip.zip), 0.005) / 1609.34, 2) AS dist,
    inp.lat,
    inp.lng
FROM standard_zip zip
LEFT JOIN (SELECT x as lng, y as lat from standard_zip where zip = :zip) inp on 1=1
WHERE zip.zip IN (
        SELECT DISTINCT (zip) AS zip
        FROM standard_zip c
        WHERE SDO_FILTER(c.geom, SDO_UTIL.CIRCLE_POLYGON(inp.lng, inp.lat, (:r * 1609.34), 5)) = 'TRUE'
            AND SDO_RELATE(c.geom, SDO_UTIL.CIRCLE_POLYGON(inp.lng, inp.lat, (:r * 1609.34), 5), 'mask=ANYINTERACT querytype = WINDOW') = 'TRUE'
        )
ORDER BY DIST

What am I doing wrong?

MguerraTorres suggested using a CTE, which sounds great. Here's what I tried:

WITH inp as (SELECT x as lng, y as lat from standard_zip where zip = :zip)
SELECT 
    zip.zip,
    ROUND(SDO_GEOM.SDO_DISTANCE((MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(inp.lng, inp.lat, NULL), NULL, NULL)), (SELECT geom FROM standard_zip WHERE zip = zip.zip), 0.005) / 1609.34, 2) AS dist,
    inp.lat,
    inp.lng
FROM standard_zip zip
WHERE zip.zip IN (
        SELECT DISTINCT (zip) AS zip
        FROM standard_zip c
        WHERE SDO_FILTER(c.geom, SDO_UTIL.CIRCLE_POLYGON(inp.lng, inp.lat, (:r * 1609.34), 5)) = 'TRUE'
            AND SDO_RELATE(c.geom, SDO_UTIL.CIRCLE_POLYGON(inp.lng, inp.lat, (:r * 1609.34), 5), 'mask=ANYINTERACT querytype = WINDOW') = 'TRUE'
        )
ORDER BY DIST

Problem is, Oracle says inp.lng is an invalid identifier. Why?

Best Answer

You're cross joining to your "inp" query, but then using the results from that in the where clause for the subquery of your main where clause. Effectively then the subquery will run once for every row of the zip table. Which will slow things a tad.

I'm slightly rusty as to what you could achieve with any PL/SQL around this, so in pure SQL you could write it as:

SELECT 
  zip.zip,
  ROUND(SDO_GEOM.SDO_DISTANCE((MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(inp.lng, inp.lat, NULL), NULL, NULL)), (SELECT geom FROM standard_zip WHERE zip = zip.zip), 0.005) / 1609.34, 2) AS dist,
  inp.lat,
  inp.lng
FROM standard_zip zip
WHERE zip.zip IN (
    SELECT DISTINCT (zip) AS zip
    FROM standard_zip c
    WHERE SDO_FILTER(c.geom, SDO_UTIL.CIRCLE_POLYGON((SELECT x as lng from standard_zip where zip = :zip),
                                                     (SELECT y as lat from standard_zip where zip = :zip),
                                                     (:r * 1609.34), 5)) = 'TRUE'
        AND SDO_RELATE(c.geom, SDO_UTIL.CIRCLE_POLYGON((SELECT x as lng from standard_zip where zip = :zip),
                                                       (SELECT y as lat from standard_zip where zip = :zip),
                                                       (:r * 1609.34), 5), 'mask=ANYINTERACT querytype = WINDOW') = 'TRUE'
    )
ORDER BY DIST

Might be a little quicker. Essentially it ought to behave much like your original working query did.

PROPOSED QUERY

SELECT
   IFNULL(name,'Total') name,
   SUM(IF(type='dog',quant,0)) dogs,
   SUM(IF(type='cat',quant,0)) cats
FROM
(
    SELECT AA.name,BB.type,SUM(BB.quant) quant
    FROM customers AA INNER JOIN purchases BB
    ON AA.id = BB.owner_id
    GROUP BY AA.name,BB.type
) A
GROUP BY name WITH ROLLUP;

SAMPLE DATA

drop database if exists meccooll;
create database meccooll;
use meccooll
create table customers
(
    id int not null,
    name varchar(20),
    primary key (id)
);
create table purchases
(
    owner_id int not null,
    type varchar(20),
    quant int not null,
    key (owner_id,type)
); 
insert into customers (id,name) values (1,'Matt'),(3,'John');
insert into purchases (owner_id,type,quant) values
(3,'cat',3),(3,'cat',2),(1,'cat',4),(1,'dog',1),(1,'dog',2);
select * from customers;
select * from purchases;

When you execute it

mysql> drop database if exists meccooll;
Query OK, 2 rows affected (0.28 sec)

mysql> create database meccooll;
Query OK, 1 row affected (0.00 sec)

mysql> use meccooll
Database changed
mysql> create table customers
    -> (
    ->     id int not null,
    ->     name varchar(20),
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.47 sec)

mysql> create table purchases
    -> (
    ->     owner_id int not null,
    ->     type varchar(20),
    ->     quant int not null,
    ->     key (owner_id,type)
    -> );
Query OK, 0 rows affected (0.62 sec)

mysql> insert into customers (id,name) values (1,'Matt'),(3,'John');
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into purchases (owner_id,type,quant) values
    -> (3,'cat',3),(3,'cat',2),(1,'cat',4),(1,'dog',1),(1,'dog',2);
Query OK, 5 rows affected (0.15 sec)
Records: 5  Duplicates: 0  Warnings: 0

you get this

mysql> select * from customers;
+----+------+
| id | name |
+----+------+
|  1 | Matt |
|  3 | John |
+----+------+
2 rows in set (0.00 sec)

mysql> select * from purchases;
+----------+------+-------+
| owner_id | type | quant |
+----------+------+-------+
|        3 | cat  |     3 |
|        3 | cat  |     2 |
|        1 | cat  |     4 |
|        1 | dog  |     1 |
|        1 | dog  |     2 |
+----------+------+-------+
5 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SELECT
    ->    IFNULL(name,'Total') name,
    ->    SUM(IF(type='dog',quant,0)) dogs,
    ->    SUM(IF(type='cat',quant,0)) cats
    -> FROM
    -> (
    ->     SELECT AA.name,BB.type,SUM(BB.quant) quant
    ->     FROM customers AA INNER JOIN purchases BB
    ->     ON AA.id = BB.owner_id
    ->     GROUP BY AA.name,BB.type
    -> ) A
    -> GROUP BY name WITH ROLLUP;
+-------+------+------+
| name  | dogs | cats |
+-------+------+------+
| John  |    0 |    5 |
| Matt  |    3 |    4 |
| Total |    3 |    9 |
+-------+------+------+
3 rows in set, 1 warning (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED WITHOUT ROLLUP

mysql> SELECT
    ->    IFNULL(name,'Total') name,
    ->    SUM(IF(type='dog',quant,0)) dogs,
    ->    SUM(IF(type='cat',quant,0)) cats
    -> FROM
    -> (
    ->     SELECT AA.name,BB.type,SUM(BB.quant) quant
    ->     FROM customers AA INNER JOIN purchases BB
    ->     ON AA.id = BB.owner_id
    ->     GROUP BY AA.name,BB.type
    -> ) A
    -> GROUP BY name;
+------+------+------+
| name | dogs | cats |
+------+------+------+
| John |    0 |    5 |
| Matt |    3 |    4 |
+------+------+------+
2 rows in set, 1 warning (0.00 sec)

mysql>

GIVE IT A TRY !!!

Oracle – Selecting Multiple Columns from Subquery with Connect By

ORA-00913 too many values

Cause: The SQL statement requires two sets of values equal in number. This error occurs when the second set contains more items than the first set. For example, the subquery in a WHERE or HAVING clause may return too many columns, or a VALUES or SELECT clause may return more columns than are listed in the INSERT.
Action: Check the number of items in each set and change the SQL statement to make them equal.

In your case your subquery is returning two columns to the first query expecting one column. You can change your query as follow.

SELECT  folder_name name,
  document_count,
  (SELECT SUM(e2.document_count)
    FROM       folder_mapping e2      START WITH e2.folder_mapping_id       = e1.folder_mapping_id CONNECT BY prior e2.folder_mapping_id = e2.parent_folder_mapping_id
  )
  sum_sal, (SELECT SUM(e2.document_count)
    FROM       folder_mapping e2      START WITH e2.folder_mapping_id       = e1.folder_mapping_id CONNECT BY prior e2.folder_mapping_id = e2.parent_folder_mapping_id
  ) folder_count_total
FROM
  folder_mapping e1

That was just the fix for your error. Actually you can use the following query to get the desired output which is better(In terms of cost) than the original query.

SQL> desc folder_mapping;
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FOLDER_MAPPING_ID                  NUMBER
 FOLDER_NAME                        VARCHAR2(20)
 FOLDER_FILENET_ID                  NUMBER
 PARENT_FOLDER_MAPPING_ID               NUMBER
 FOLDER_COUNT                       NUMBER
 DOCUMENT_COUNT                     NUMBER
 SHARED_FLAG                        NUMBER
 ATTACHMENT_FLAG                    NUMBER

SQL> select * from folder_mapping;

FOLDER_MAPPING_ID FOLDER_NAME          FOLDER_FILENET_ID
----------------- -------------------- -----------------
PARENT_FOLDER_MAPPING_ID FOLDER_COUNT DOCUMENT_COUNT SHARED_FLAG ATTACHMENT_FLAG
------------------------ ------------ -------------- ----------- ---------------
        4 root3                  111
               2        2          2           0           0

        1 root                    11
               0        2          3           0           0

        2 root1                  111
               1        2         33           0           0



SQL> SELECT folder_name, SUM(document_count) "Total_Doc_Count", SUM(folder_count) "Total_Folder_Count"  FROM(
   SELECT CONNECT_BY_ROOT folder_name as folder_name, document_count, folder_count
      FROM folder_mapping
      CONNECT BY PRIOR folder_mapping_id = parent_folder_mapping_id)
      GROUP BY folder_name;   

FOLDER_NAME      Total_Doc_Count Total_Folder_Count
-------------------- --------------- ------------------
root1                 35              4
root                  38              6
root3                  2              2

SQL>

Best Answer

Related Solutions

MySQL – LEFT JOINing Same Table Twice