0

I need to UNION the results of the same SELECT code over two different schemas. (Example below.) But every time I need to change something in the code, I have to change it twice. I've already been bitten for forgetting to make the change in the second SELECT. Is there a way to abstract the SELECT statement to some sort of function?

This is a query saved on a .sql file, which I run sometimes. I do not have permissions to create views or functions to the DB.

SELECT a.column_a,
  b.column_b,
  c.column_c
FROM schema_A.table_a AS a,
  schema_A.table_b AS b,
  schema_A.table_c AS c
WHERE a.id_b = b.id
  AND b.id_c = c.id
  AND a.column_a LIKE 'something%'

UNION ALL

SELECT a.column_a,
  b.column_b,
  c.column_c
FROM schema_B.table_a AS a,
  schema_B.table_b AS b,
  schema_B.table_c AS c
WHERE a.id_b = b.id
  AND b.id_c = c.id
  AND a.column_a LIKE 'something%'

I'd like to be able to do something like this:

FUNCTION something(@schema) AS (
  SELECT a.column_a,
    b.column_b,
    c.column_c
  FROM @schema.table_a AS a,
    @schema.table_b AS b,
    @schema.table_c AS c
  WHERE a.id_b = b.id
    AND b.id_c = c.id
    AND a.column_a LIKE 'something%'
)

SELECT something(schema_A)
UNION ALL
SELECT something(schema_B)
Kit
  • 20,354
  • 4
  • 60
  • 103
Pahbloo Marks
  • 135
  • 4
  • 2
    Short answer: without views, no. SQL has age privileges over DRY, so it gets to not comply with such practices. The best you can achieve, where it's an option, is to shift as much of the logic as possible to *after* the `UNION`, but in this case the only piece that qualifies for that is `a.column_a LIKE 'something%'`, which doesn't help much. Speaking of age privileges: old-style `JOIN`s have been obsolete for almost 30 years now, they're a [bad habit to kick](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins). – Jeroen Mostert Sep 17 '21 at 18:14

1 Answers1

1

You could do:

SELECT a.column_a,
  b.column_b,
  c.column_c
FROM (select * from schema_A.table_a 
      union all
      select * from schema_B.table_a 
     ) AS a,
  (Select * from schema_A.table_b 
   union all
   Select * from schema_b.table_b 
  ) AS b,
  (select * from schema_A.table_c 
   union all
   select * from schema_B.table_c 
  ) AS c
WHERE a.id_b = b.id
  AND b.id_c = c.id
  AND a.column_a LIKE 'something%'

But that would not make life easier.......

EDIT: My assumption that above statement has the same result as te original query is wrong, see DBFIDDLE.

I need more ☕☕ to know why ...

Luuk
  • 12,245
  • 5
  • 22
  • 33