Database Reference
In-Depth Information
Listing 13-4.
Creating Table Aliases with the AS Keyword
SELECT
FS.
TitleKey
,
DT.
TitleName
, OrderNumber
, OrderDateKey
, StoreKey
, SalesQuantity
FROM DWPubsSales.dbo.FactSales
AS FS
INNER JOIN DWPubsSales.dbo.DimTitles
AS DT
ON
FS.
TitleKey =
DT.
TitleKey
Ordering Results
Another improvement for your report may be reordering the column listings so that they make more sense to the
end user. Most modern reporting software can also modify the display of the columns in the user interface, but
here we are dealing with just the raw SQL code. Therefore, modifying this display is a simple matter of moving the
columns in the select list to their more appropriate positions.
Since this report is about titles, we have moved the title information to the top of the select. We have also
placed the TitleName in the first column in the select list, because this provides the most user-friendly column
results.
At the same time, we order the results by both title and dates (see Listing 13-5).
Listing 13-5.
Reordering the Columns and Rows for Better Results
SELECT
DT.TitleName
, DT.TitleId
, FS.TitleKey
, OrderNumber
, OrderDateKey
, StoreKey
, SalesQuantity
FROM DWPubsSales.dbo.FactSales AS FS
INNER JOIN DWPubsSales.dbo.DimTitles AS DT
ON FS.TitleKey = DT.TitleKey
ORDER BY [TitleName], [OrderDateKey]
To improve the report, some developers like to include column aliases to further simplify or elaborate on
the column definitions. Listing 13-6 further improves the outcome of our query by adding two column aliases.
Notice the
ORDER BY
clause works with either the column alias such as
[Title]
or the column name such as
[OrderDateKey]
.
Listing 13-6.
Adding Column Aliases for Better Results
SELECT
[Title]
= DT.TitleName
, DT.TitleId
,
[Internal Data Warehouse Id]
= FS.TitleKey
