Passing Function Calls Directly to a Relational Engine

Reference:

The SQL adapters can pass virtual fields that call certain SQL scalar functions to the relational engine for processing. This enables you to use SQL functions in a request even when they have no equivalent in the WebFOCUS language. The function must be row-based and have a parameter list that consists of a comma-delimited list of column names or constants. In order to reference the function in an expression, prefix the function name with SQL.

If the virtual field is in the Master File, both TABLE requests and those SQL requests that qualify for Automatic Passthru (APT) can access the field. If the virtual field is created by a DEFINE FILE command, TABLE requests can access the field. The function name and parameters are passed without translation to the relational engine. Therefore, the expression that creates the DEFINE field must be optimized, or the request will fail.


Top of page

x
Reference: Usage Notes for Direct SQL Function Calls


Example: Calling the SQL CONCAT Function in a Request

This example uses the WebFOCUS Retail demo sample. You can create this sample data source for a relational adapter by right-clicking the application in which you want to place this sample, and selecting New, and then Samples from the context menu. Then select WebFOCUS - Retail Demo from the Sample procedures and data for drop-down list and click Create.

The following request against the WebFOCUS Retail demo data source uses the SQL CONCAT function to concatenate the product category with the product subcategory.

SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON = STMTRACE//CLIENT
SET TRACESTAMP=OFF
SET XRETRIEVAL = OFF
 
DEFINE FILE WF_RETAIL
CAT_SUBCAT/A50 = SQL.CONCAT(PRODUCT_CATEGORY, PRODUCT_SUBCATEG);
END
 
TABLE FILE WF_RETAIL
PRINT CAT_SUBCAT
BY PRODUCT_CATEGORY NOPRINT
END

The trace output shows that the SQL function call was passed to the RDBMS.

SELECT      
CONCAT(T2."PRODUCT_CATEGORY",T2."PRODUCT_SUBCATEG"),  
T2."PRODUCT_CATEGORY",      
T2."PRODUCT_SUBCATEG"  
FROM    
wfr_product T2  
ORDER BY    
T2."PRODUCT_CATEGORY"   
FOR FETCH ONLY;

WebFOCUS