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.
(FOC32605) NON OPTIMIZABLE EXPRESSION WITH SQL. SYNTAX
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 |