How to: Reference: |
The DB_EXPR function inserts a native SQL expression exactly as entered into the native SQL generated for a FOCUS or SQL language request.
The DB_EXPR function can be used in a DEFINE command, a DEFINE in a Master File, a WHERE clause, a FILTER FILE command, a filter in a Master File, or in an SQL statement. It can be used in a COMPUTE command if the request is an aggregate request (uses the SUM, WRITE, or ADD command) and has a single display command. The expression must return a single value.
DB_EXPR(native_SQL_expression)
where:
Is a partial native SQL string that is valid for inserting into the SQL generated by the request. The SQL string must have double quotation marks (" ") around each field reference, unless the function is used in a DEFINE command with a WITH phrase.
The following TABLE request against the WF_RETAIL data source uses the DB_EXPR function in the COMPUTE command to call two DB2 functions. It calls the BIGINT function to convert the squared revenue to a BIGINT data type, and then uses the CHAR function to convert that value to alphanumeric.
TABLE FILE WF_RETAIL SUM REVENUE NOPRINT AND COMPUTE BIGREV/A31 = DB_EXPR(CHAR(BIGINT("REVENUE" * "REVENUE") ) ) ; AS 'Alpha Square Revenue' BY REGION ON TABLE SET PAGE NOPAGE END
WF_RETAIL is a sample data source you can create by right-clicking an application on the Reporting Server Web Console and selecting New and then Samples from the context menu.
The trace shows that the expression from the DB_EXPR function was inserted into the DB2 SELECT statement:
SELECT
T11."REGION",
SUM(T1."Revenue"),
((CHAR(BIGINT( SUM(T1."Revenue") * SUM(T1."Revenue")) ) ))
FROM
wrd_fact_sales T1,
wrd_dim_customer T5,
wrd_dim_geography T11
WHERE
(T5."ID_CUSTOMER" = T1."ID_CUSTOMER") AND
(T11."ID_GEOGRAPHY" = T5."ID_GEOGRAPHY")
GROUP BY
T11."REGION "
ORDER BY
T11."REGION "
FOR FETCH ONLY;
END
The output is:
WebFOCUS |