SQL Language (MOSC)

MOSC Banner

Query of a view containing a max() from a union of queries appears to match internal values

edited May 18, 2023 1:43PM in SQL Language (MOSC) 4 commentsAnswered

Oracle Database 11g Release 11.2.0.2.0 - 64bit (SE)

I’m hoping someone can help or confirm the theory behind some behaviour we are seeing in (possibly not occuring on XE version).

We have a complicated view that “union all”s a number of subqueries in a CTE to produce two columns an ID and timestamp, within the view we then group by ID and MAX the timestamp to get the latest date for each ID. A (very) simplified pseudo-code illustration:

CREATE VIEW complex_view (id, latest_date)
WITH 
  query1(id,date) AS : 1, 1 Jan 1970
  query2(id,date) AS : 1, 1 Jan 1980
SELECT merged.id, MAX(merged.date) FROM (
  SELECT id, date FROM query1
  UNION ALL
  SELECT id, date FROM query2
) merged
GROUP BY merged.id

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center