Databases
StackOverflow2013
Postsdbo
POHow to repeat sections of a SQL query across UNIONs? (DRY in SQL)
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to repeat sections of a SQL query across UNIONs? (DRY in SQL)
    text
    copied!<p>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:</p> <pre><code>coalesce(a.fact1,'Fact1'), coalesce(b.fact1,'Fact2') ... </code></pre> <p>the from/join section is huge as well</p> <pre><code>from table1 t1 join table2 t2 on t1.id = t2.t1_id join table3 t3 on t2.id = t3.t2_id </code></pre> <p>etc. Each of these blocks is repeated identically across all three SELECT statements.</p> <p>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.</p> <p>This is in MSSQL. Is there any way to do what I'm trying to explain?</p> <hr> <p>Update</p> <pre><code>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' </code></pre> <p>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</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload