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.
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 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
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