How to use a table alias with a Union statement in Access?

Question

In an unpivoting operation, I would like the following:

SELECT A, B, C FROM [complex joins/where clause] As DerivedTable
UNION
SELECT A, B, D FROM DerivedTable
UNION
SELECT A, B, E FROM DerivedTable
...

but it complains that DerivedTable cannot be found (I use a derived table so that [complex joins/where clause] doesn't have to be evaluated again and again thereby slowing things).

I know I can simply create a new query called DerivedTable to represent [complex joins/where clause] but

The above SQL is passed from Excel - I'd rather not have to open the database to create a new query prior to running the above
The [complex joins/where clause] is generated dynamically, and changes from user to user, two of which may be running the above SQL at the same time.

That's not the way unions work, unfortunately. I assume you're restricted to a single query (rather than being able to output multiple SQL statements), so are you able to simply output `[complex joins/where clause]` in all unions, not just the first? — Adrian Wragg, Aug 14 '13 at 08:01
`[complex joins/where clause]` takes a long time to execute, so ideally I'd like to avoid repeated evaluation of `[complex joins/where clause]` — mchen, Aug 14 '13 at 08:06

score 1 · Accepted Answer · answered Aug 14 '13 at 08:12

Something like this. Use CASE and JOIN this table with (1,2,3,...) table. I'm not sure it is right syntax for Access but it will work on most SQL dialects.

SQLFiddle demo

SELECT A,B, 
       CASE WHEN CT.r=1 then C
            WHEN CT.r=2 then D
            WHEN CT.r=3 then E
       END

FROM [complex joins/where clause] As DerivedTable
CROSS JOIN (select 1 as r
            union all  
            select 2 as r
            union all  
            select 3 as r
           ) as CT
order by A,B

How to use a table alias with a Union statement in Access?

1 Answers1