Databases Reference
In-Depth Information
from AB CATEGORIES C
where C.CATEGORY ID = P.CATEGORY ID) CATEGORY,
AVAILABLE YN,
PRICE,
CREATED BY,
CREATED ON,
UPDATED BY,
UPDATED ON
from AB PRODUCTS P
The nested subquery in Listing 13-2 is an example of an
inline select
. Inline selects are great for
columns that would typically not be displayed because the select will not be executed if the column is
not displayed. They are also very good for lookup tables with smaller data sets, as opposed to joining the
two tables together in the main select.
Data Formatting
Aside from “decoding” your values, you can also improve the display of your data. In the SQL below I
change the audit columns
CREATED BY
and
UPDATED BY
to lowercase. Unless you change the case during
insert/update, usernames are typically uppercase; in reports, uppercase not only takes up more physical
space than lowercase but also tends to display more prominently to the user, garnering more attention
than it needs to. I have also used a case statement to change the Y/N value for
AVAILABLE YN
to a more
readable and pleasing Yes or No. Lastly, I have included the
CREATED ON
and
UPDATED ON
columns twice.
One I will leave as a date, and the other I will change using a format mask.
Listing 13-3.
Data Formatting
select PRODUCT ID,
PRODUCT NAME,
PRODUCT DESC,
CATEGORY ID,
(select CATEGORY NAME
from AB CATEGORIES C
where C.CATEGORY ID = P.CATEGORY ID) CATEGORY,
case when AVAILABLE YN = 'Y'
then 'Yes'
else 'No'
end AVAILABLE YN,
PRICE,
lower(CREATED BY) CREATED BY,
CREATED ON CREATED,
CREATED ON,
lower(UPDATED BY) UPDATED BY,
UPDATED ON UPDATED,
UPDATED ON
from AB PRODUCTS P
With the initial SQL set, I will create the report, as shown in Figure 13-1.
