CODEWITHSUNDEEP
sqloracle-database
lostinOracle
lostinOracle

Reputation: 408

SQL (oracle) - how to query multiple changed values from multiple tables

I'm trying to pull an audit of all the values that were changed in a specified time period from multiple tables. Is there a simpler way of writing the below? 

select property_number, 'table1', count(*)
    from table1
   where start_date between to_date('07/01/2017','mm/dd/yyyy') and to_date('09/30/2017', 'mm/dd/yyyy')
group by property_number
union all
  select property_number, 'table2', count(*)
    from table2
   where start_date between to_date('07/01/2017', 'mm/dd/yyyy') and to_date('09/30/2017', 'mm/dd/yyyy')
group by property_number
union all
  select property_number, 'table3', count(*)
    from table3
   where start_date between to_date('07/01/2017', 'mm/dd/yyyy') and to_date('09/30/2017', 'mm/dd/yyyy')
group by property_number
union all
  select property_number, 'table4', count(*)
    from table4
   where start_date between to_date('07/01/2017', 'mm/dd/yyyy') and to_date('09/30/2017', 'mm/dd/yyyy')
group by property_number
union all
  select property_number, 'table5', count(*)
    from table5
   where start_date between to_date('07/01/2017', 'mm/dd/yyyy') and to_date('09/30/2017', 'mm/dd/yyyy')
group by property_number

Upvotes: 0

Views: 42

Answers (1)

Parfait
Parfait

Reputation: 107757

Consider a CTE for a DRY-er solution. 

WITH master_all AS
  (SELECT property_number, 'table1' AS "indicator"
   FROM table1
   UNION ALL 
   SELECT property_number, 'table2'
   FROM table2
   UNION ALL 
   SELECT property_number, 'table3'
   FROM table3
   UNION ALL 
   SELECT property_number, 'table4'
   FROM table4
   UNION ALL 
   SELECT property_number, 'table5'
   FROM table5)

SELECT property_number, indicator, Count(*) AS "Audit_Count"
FROM  master_all
WHERE start_date BETWEEN to_date('07/01/2017', 'mm/dd/yyyy') 
                     AND to_date('09/30/2017', 'mm/dd/yyyy')
GROUP BY property_number, indicator;

But once again, consider a schema adjustment and append all similar structured tables with indicator field into one master table.

Upvotes: 2

Related Questions