I'd like to create a master query / report in Oracle SQL Developer, against which I can create multiple other reports.

e.g. create a master query / report which returns a list of values (known as 'Channels'); my Child/second report / query is able to 'call' that query and return its own set of results (including Channel), for example only the related results where the Channel exists in the first query.

I'm able to create successfully Master / Child reports where I select a single value in the Master report and the corresponding values are displayed in the Child report, but I don't want to **select ** values in the Master report, I just want the Child report to return results where the Channel in the Child report is 'in' (or in some cases 'not in') the list of Channel values from the Master report.

There will be multiple child reports that all depend on the Master data, but are querying different things.

I have read-only access to the database, so can't create Views.

I am using the word report, but actually it's just a query I need to be able to requery from multiple reports.

I'd like to create a master query / report in Oracle SQL Developer, against which I can create multiple other reports.

e.g. create a master query / report which returns a list of values (known as 'Channels'); my Child/second report / query is able to 'call' that query and return its own set of results (including Channel), for example only the related results where the Channel exists in the first query.

I'm able to create successfully Master / Child reports where I select a single value in the Master report and the corresponding values are displayed in the Child report, but I don't want to **select ** values in the Master report, I just want the Child report to return results where the Channel in the Child report is 'in' (or in some cases 'not in') the list of Channel values from the Master report.

There will be multiple child reports that all depend on the Master data, but are querying different things.

I have read-only access to the database, so can't create Views.

I am using the word report, but actually it's just a query I need to be able to requery from multiple reports.

• sql
• oracle-sqldeveloper

• "able to requery from multiple reports" - are you building reports or queries? I expect have to build report and apply relevant filter criteria. – June7 Commented Feb 15 at 17:35
• Hi @June7, it could be done either as a report or query, but had considered reports because of their master/child relationship; it may not be the best solution. But essentially there are multiple different queries that need to be run, but they use a common (lengthy) subquery as a where clause. I'd like to avoid making copies of that clause in case it needs to be edited; I'd have to edit every instance. The clause will be a single column of data to be used e.g. as... where ChannelID IN [the common clause]. I'd hoped I could use the Child report to look at any value IN the Master report. – L'le Tom Commented Feb 15 at 18:21
• 1 I suppose you need to figure out how to parameterize that nested query. I use Access as GUI and I would build WHERE clause with VBA and apply to report when opening. I have no idea how this could be accomplished with oracle-sqldeveloper. – June7 Commented Feb 15 at 18:29

1 Answer 1

Use a Common Table Expression (CTE) in a WITH clause:

WITH                                                                               
tb1(id,score,score_date) AS ( -- first CTE: name(column,column2 ...) AS (
          SELECT 1234, 99,DATE '2025-01-23' FROM dual -- any full select
UNION ALL SELECT 5678,153,DATE '2025-01-23' FROM dual - in round parentheses
UNION ALL SELECT 9101,121,DATE '2025-01-23' FROM dual
UNION ALL SELECT 1234,103,DATE '2025-01-30' FROM dual
UNION ALL SELECT 5678,155,DATE '2025-01-30' FROM dual
UNION ALL SELECT 9101,126,DATE '2025-01-30' FROM dual
UNION ALL SELECT 1234,101,DATE '2025-02-06' FROM dual
UNION ALL SELECT 5678,150,DATE '2025-02-06' FROM dual
UNION ALL SELECT 9101,124,DATE '2025-02-06' FROM dual
UNION ALL SELECT 1234,100,DATE '2025-02-13' FROM dual
UNION ALL SELECT 5678,152,DATE '2025-02-13' FROM dual
)
,
tb2(id,list,list_date) AS ( -- you can have several CTE-s, comma separated
          SELECT 1234,'LISTA',DATE '2025-01-27' FROM dual
UNION ALL SELECT 5678,'LISTB',DATE '2025-01-30' FROM dual
UNION ALL SELECT 9101,'LISTC',DATE '2025-02-06' FROM dual
)
,                                                                                                                                                                                                                                          
tb1n AS ( -- and you can select from CTE-s above
  SELECT
    *
  , LEAD(score_date,1,'9999-12-31') OVER(PARTITION BY id ORDER BY score_date) AS next_date
  FROM tb1
)
SELECT -- and the outermost query can select from all CTE-s above.
  tb2.id
, score
, list
, list_date
FROM tb1n
JOIN tb2 
 ON tb1n.id = tb2.id
AND list_date >= score_date
AND list_date <  next_date
;