DB_EXPR: Inserting an SQL Expression Into a Request

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.

Syntax: How to Insert an SQL Expression Into a Request With DB_EXPR 

DB_EXPR(native_SQL_expression)

where:

native_SQL_expression

Is a partial native SQL string that is valid to insert 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.

Reference: Usage Notes for the DB_EXPR Function

  • The expression must return a single value.
  • Any request that includes one or more DB_EXPR functions must be for a synonym that has a relational SUFFIX.
  • Field references in the native SQL expression must be within the current synonym context.
  • The native SQL expression must be coded inline. SQL read from a file is not supported.

Example: Inserting the DB2 BIGINT and CHAR Functions Into a TABLE Request

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 pointing to New and then clicking Tutorials 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: