How to repeat sections of a SQL query across UNIONs? (DRY in SQL)

Question

I have a query that returns the results of three UNION'ed queries. Each query has a lengthy select statement. There are parts of the select statement that look like this:

coalesce(a.fact1,'Fact1'),
coalesce(b.fact1,'Fact2')
...

the from/join section is huge as well

from table1 t1
join table2 t2 on t1.id = t2.t1_id
join table3 t3 on t2.id = t3.t2_id

etc. Each of these blocks is repeated identically across all three SELECT statements.

I was wondering if there was a way to put that piece of code (either a block of field names or a block of join statements) into a place where I could reference them in one line. Kind of like a mini-view/function but simply serving as a text replacement. This way, I can edit these things once and not have to edit the relevant bit of each select statement individually.

This is in MSSQL. Is there any way to do what I'm trying to explain?

Update

select a.field1, b.field2, c.field3 
from table1 a 
  join table2 b on a.id = b.table1_id 
  join table3 c on b.id = c.table2_id 
where cond1 = 'Pos Condition' 
  AND cond2 = 'Test' 

union

select a.field1, b.field2, d.field3 
from table1 a 
  join table2 b on a.id = b.table1_id 
  join table3 c on b.id = c.table2_id 
  join table4 d on c.id = d.table3_id 
where cond1 = 'Pos Condition' 
  AND cond2 = 'Second Type of Result'

The data in each field changes slightly based on type of result. I'd like to put the from table1 -> join table3 section in a separate place so I can insert it repeatedly

To clarify: All I'd like to do is replace sections of text in a query with a variable/macro that represents those statements. — DNadel, Dec 28 '11 at 19:25
Thanks! Couldn't figure out how to get the formatting down :-) — DNadel, Dec 28 '11 at 19:40

score 3 · Answer 1 · answered Dec 28 '11 at 20:03

3

The example you have given in your question can be refactored into a CTE fine.

WITH CTE
     AS (SELECT a.field1,
                b.field2,
                c.field3,
                c.id AS c_id
         FROM   table1 a
                JOIN table2 b
                  ON a.id = b.table1_id
                JOIN table3 c
                  ON b.id = c.table2_id
         WHERE  cond1 = 'Pos Condition') 
SELECT field1,
       field2,
       field3
FROM   CTE
WHERE  cond2 = 'Test'
UNION
SELECT field1,
       field2,
       d.field3
FROM   CTE c
       JOIN table4 d
         ON c_id = d.table3_id
WHERE  cond2 = 'Second Type of Result'

answered Dec 28 '11 at 20:03

Martin Smith

438,706
87
741
845

Thanks Martin. This makes sense. However, I'd still like to know if there's an answer to my original question as to how to replicate blocks of SQL code across the same query. – DNadel Dec 28 '11 at 20:18
1

@DNadel - Not really (unless you are running in `sqlcmd` mode). You can encapsulate in a View. Or use dynamic SQL. – Martin Smith Dec 28 '11 at 20:26

score 0 · Answer 2 · answered Dec 28 '11 at 19:04

0

Look into Common Table Expressions

answered Dec 28 '11 at 19:04

ypercubeᵀᴹ

113,259
19
174
235

Thanks! I've looked at CTE but I think it is a little too strict for what I'm looking for. It seems a bit subquery based and what I'm looking for is more a simple text replacement. Can you recommend anything else? – DNadel Dec 28 '11 at 19:23
Can you post the query or an abbreviation of it? – ypercubeᵀᴹ Dec 28 '11 at 19:26

score 0 · Answer 3 · answered Dec 28 '11 at 19:05

0

Have you looked at Common Table Expressions (CTEs)?

answered Dec 28 '11 at 19:05

Garrett Vlieger

9,354
4
32
44

Thanks, I have looked at CTE. See comments above :-) – DNadel Dec 28 '11 at 19:23
I don't like going this way since it's a pain to maintain, but you might have to use dynamic SQL. You would have to build up your SQL in a string, which would allow you to use the same block of code in multiple places without rewriting it. – Garrett Vlieger Dec 28 '11 at 19:29

How to repeat sections of a SQL query across UNIONs? (DRY in SQL)

3 Answers3

Linked