CODEWITHSUNDEEP
sqldb2
John
John

Reputation: 660

Use View from a Query across multiple SELECTS

I've got a query in similar structure to below

SELECT blah
FROM   A, B, C
WHERE  $SOME_COMMON_IF_AND
AND    $SPECIFIC_1

UNION

SELECT blah
FROM   A, B, C, D
WHERE  $SOME_COMMON_IF_AND
AND    $SPECIFIC_2

UNION

...

In am attempt to reduce query time, I'm trying to strip out the $SOME_COMMON_IF_AND parts and project it as a view, and have the SELECT clauses use this view as a baseline.

I can't seem to figure out the correct syntax to do this, any suggestions?

Thanks in advance!

--- EDITED FOR MORE INFO---

For example:

(SELECT blah
FROM   A, B, C
WHERE  $SOME_COMMON_IF_AND)
AS WORK_TABLE_1

%$@#%$@# -- the missing link

SELECT blah
FROM   WORK_TABLE_1
WHERE  $SPECIFIC_1

UNION

SELECT blah
FROM   WORK_TABLE_1
WHERE  $SPECIFIC_2

UNION

....

This is on DB2 but I'm look for a general SQL solution, the original query is a beast (1000 line +), hence me abstracting the WHERE clause. Please let me know if more clarification is needed.

Upvotes: 0

Views: 187

Answers (1)

Ian Bjorhovde
Ian Bjorhovde

Reputation: 11042

What you're looking for is called a common table expression. It's just like having an in-line view:

with 
work_table_1 as (
   SELECT blah
   FROM   A, B, C
   WHERE  $SOME_COMMON_IF_AND
)
SELECT blah
FROM   WORK_TABLE_1
WHERE  $SPECIFIC_1

UNION

SELECT blah
FROM   WORK_TABLE_1
WHERE  $SPECIFIC_2

Upvotes: 2

Related Questions