Wissensdatenbank

Hint Ref: 021603140002
Hint Date: 14/03/2016

Hint Details: There are times when a  complex query either requires, or it is easier to use, temporary tables as a min-processing stage. This is all very well in the case of something like an XML job, but if the query is in a report, it is not possible to use temporary tables here, unless they are set up in an XML job which then calls the report.

In these situations, for example, the answer is a 'Select from a Select'.

Put simply, instead of creating a query whose result is stored in a temporary table which is then itself queried, the original query becomes the 'from' for the second query and the whole thing is done in one.

As an example, let us consider the need to export a query using the CSV export commands in Job Runner. These commands allow for the direct creation of a CSV file from the full result set of a query.

Very useful indeed, but there is the potential for one problem; what if the required order of the CSV data has to be calculated (or stated) on a line by line basis, which could not be put in the Where statement directly, but had to appear as one of the returned columns? How could this be done without it being included in the CSV output?

The obvious method would be to use a temporary table:

Query 1.

Select 'w' as Col1, 'x' as Col2, 'y' as Col3, 'z' as Col4, 1 as TheOrder
into #ttemp
Union All
Select 'h', 'i' , 'j' , 'd', 3 
Union All
Select 'a', 'b', 'c', 'd', 2

Query 2.

Select Col1, Col2, Col3, Col4 from #ttemp order by TheOrder

But it can be done as a single query thus:

Select Col1, Col2, Col3, Col4 from 
(Select 'w' as Col1, 'x' as Col2, 'y' as Col3, 'z' as Col4, 1 as TheOrder
Union All
Select 'h', 'i' , 'j' , 'd', 3 
Union All
Select 'a', 'b', 'c', 'd', 2) as ttemp order by TheOrder

NOTE: The 'Select' used as the 'From' MUST be in brackets and it MUST be aliased;

Select AliasName.x, AliasName.y, AliasName.z... from (select a as x,b as y,c as z...) as AliasName