Databases Reference
In-Depth Information
Listing 13-4.
Adding a Derived Column
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,
(select COUNT(*)
from AB PRODUCT REVIEWS R
where P.PRODUCT ID = R.PRODUCT ID) NBR REVIEWS,
lower(CREATED BY) CREATED BY,
CREATED ON CREATED,
CREATED ON,
lower(UPDATED BY) UPDATED BY,
UPDATED ON UPDATED,
UPDATED ON
from AB PRODUCTS P
Be aware that you can edit the SQL for your report, but all columns added, regardless of their
position within your select, will display at the bottom on your Report Attributes. Because you order your
columns within your running report, the order on Report Attributes is really not important but if you are
crazy like me, feel free to use the up and down arrows on the far right to shift your columns around to
match your revised query. Also, be cautious about your column aliases when editing. Application
Express matches columns by alias, not by position, so if you edit
CREATED BY
and change it to
lower(CREATED BY) WHO CREATED
, the old column and any definition you customized will be removed and
a new column will be added.
Once the column is defined, I define the column link as shown in Figure 13-7, passing the
PRODUCT ID
to the report you are calling. To pass a filter into an interactive report, you prefix the column
alias you are passing to with a condition. You need to remember to use the column alias, not the column
name from the table, because you might have modified the column in some way and used an alias. For
this use, I want
IR
which means
equals
(equals is the default).
If you want to pass a term and set the filter for
contains
, you would use the prefix of
IRC
. The
documentation lists all the valid operators and I will include them below for reference:
•
EQ
= equals (this is the default)
NEQ
= not equals
•
LT
= less than
•
•
LTE
= less than or equal to
GT
= greater than
•
GTE
= greater than or equal to
•
