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.


Top of page

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


Top of page

x
Reference: Usage Notes for the DB_EXPR Function


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