Oracle SQL - Nested sub query not joining to main query

Question

I'm having real trouble getting the sub query join to the main query in the where clause somewhere along the way.

Query that works when explicitly defining the field:

SELECT m.field1, m.field2, m.field3, m.myfield, etc etc
(SELECT aa.daysfromprev12 FROM(
  (SELECT subsubm.myfield, MAX(subsubm.date_to)-(SELECT MAX(add_months( to_date(subsubsubm.date_from), -12 )) FROM maintable subsubsubm WHERE subsubsubm.myfield= subsubm.myfield) AS daysfromprev12,
  row_number() OVER (ORDER BY (MAX(subsubm.date_to)-(SELECT MAX(add_months( to_date(subsubsubm.date_from), -12 )) FROM maintable subsubsubm WHERE subsubsubm.myfield= subsubm.myfield)) DESC) rn
  FROM maintable subsubm 
  WHERE subsubm.myfield = '123456'
  GROUP BY subsubm.myfield, subsubm.absence_id) aa) 
where aa.myfield = '123456' and aa.rn = 2)
AS dayss
FROM maintable m
where m.myfield = '123456'

How can I replace subsubm.myfield = '123456' & aa.myfield = '123456' to reference the main query = m.myfield

Please describe what you are trying to do. There is probably a way to express the logic so it works. — Gordon Linoff
– Gordon Linoff, Commented Jan 12, 2017 at 11:53
You use subm (line 7) in where subsubm.myfield = subm.myfield, but defines it on the end of query(line 8) ? I am not sure if its possible in Oracle — Jan Sršeň
– Jan Sršeň, Commented Jan 12, 2017 at 11:56
@JanSršeň to join it to the query outside of it, ive done this many times without issue — Matt
– Matt, Commented Jan 12, 2017 at 11:57
that would be a recursion, "subm" alias(or the resultset) is not exists at that moment — Thomas
– Thomas, Commented Jan 12, 2017 at 12:17

Boneist · Accepted Answer · 2017-01-12 15:29:54Z

1

There are way too many calls to the same table in your SQL statement there. If I've managed to unwind your query ok, I think it can be replaced with the following:

SELECT field1,
       field2,
       field3,
       field4,
       myfield,
       MAX(CASE WHEN rn = 2 THEN days end) OVER (PARTITION BY myfield) days
FROM   (SELECT field1,
               field2,
               field3,
               field4,
               myfield,
               daysfromprev12 AS days,
               row_number() OVER (ORDER BY daysfromprev12 DESC) rn
        FROM   (SELECT field1,
                       field2,
                       field3,
                       field4,
                       myfield,
                       MAX(date_to) OVER (PARTITION BY myfield, absence_id) -
                         MAX(add_months(TRUNC(date_from), -2)) OVER (PARTITION BY myfield) daysfromprev12
                FROM   maintable
                WHERE  myfield = '123456'));

N.B. Untested, since you haven't provided any sample data to work with. Also, you were doing to_date(date_from) which I have converted to trunc(date_from) on the assumption that date_from is of DATE datatype and you wanted to get rid of the time part. If it's a string, then you'd need to also input the date format mask in the to_date() to avoid the unnecessary implicit conversion taking place.

ETA: If you're going to go with this approach, you would probably find it easier to read/write/maintain if you use subquery factoring (aka common table expressions aka CTE) to separate out your subqueries. E.g. the above query could be rewritten as:

with get_initial_prev12days as (SELECT field1,
                                       field2,
                                       field3,
                                       field4,
                                       myfield,
                                       MAX(date_to) OVER (PARTITION BY myfield, absence_id) -
                                         MAX(add_months(TRUNC(date_from), -2)) OVER (PARTITION BY myfield) daysfromprev12
                                FROM   maintable
                                WHERE  myfield = '123456'),
            interim_results as (SELECT field1,
                                       field2,
                                       field3,
                                       field4,
                                       myfield,
                                       daysfromprev12 AS days,
                                       row_number() OVER (ORDER BY daysfromprev12 DESC) rn
                                FROM   get_initial_prev12days)
select field1,
       field2,
       field3,
       field4,
       myfield,
       MAX(CASE WHEN rn = 2 THEN days end) OVER (PARTITION BY myfield) days
from   interim_results;

edited Jan 12, 2017 at 15:29

answered Jan 12, 2017 at 12:18

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

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

9 Comments

Matt Over a year ago

field1 etx were just examples they dont need to be in the subquery, there are about 300 other fields in the main query

Boneist Over a year ago

Because my proposed query is a set of nested subqueries, if you want to refer to columns in the outermost query, you need to expose them at the innermost subquery and then at every subsequent level. If you prefer not to select all the columns until the outer query, you could use select * in the subqueries (e.g.

select field1, ..., max(...) over (...) from (select sq.*, row_number() over ... from (select mt.*, max(...) ... - max(...) ... from maintable mt where ...) sq)

Matt Over a year ago

I keep getting a ORA-00923: FROM keyword not found where expected

Boneist Over a year ago

If you're going to go with the nested table approach that I suggested, you'd probably find it easier to work with subquery factoring, which should hopefully make it easier to see what you're doing, and where the syntax errors are (usually because you've forgotten to add a comma after a column name in the select list, but if you're going to go with using * to list the column names in the subqueries and you're adding additional columns to the list, you MUST use an alias when referring to the *: <alias>.*. See my previous comment for an example of what I mean).

Matt Over a year ago

now im getting ORA-00904: "DAYS": invalid identifier using your second query

|

Collectives™ on Stack Overflow

Oracle SQL - Nested sub query not joining to main query

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related