In this section: |
The relational adapters can convert calls to certain FOCUS functions to calls to SQL functions.
The following FOCUS functions can be optimized:
Calls to the following FOCUS functions cannot be optimized:
Note: Do not confuse the FOCUS user-written subroutines MAX and MIN with the MAX. and MIN. prefix operators. DEFINE fields cannot include prefix operators.
In this section: |
The relational adapters can translate certain calls to the HPART, DPART, HDIFF, HDATE, and DATEDIF functions to SQL date and time functions, allowing the optimization of some TABLE requests with DEFINE fields or WHERE phrases that call these functions.
The DATEDIF function returns the difference between two dates in units, and the HDIFF function returns the difference between two date-time values in units. Calls to these functions can be translated to SQL when the unit is the number of days between two values.
The syntax for the DATEDIF function with the day unit is
DATEDIF(from_date, to_date, 'D')
The syntax for the HDIFF function with the day unit is
HDIFF(to_date_time, from_date_time, 'DAY', outfield)
where:
Is the starting date or date-time value from which to calculate the difference.
Is the ending date or date-time value from which to calculate the difference.
Numeric
Is the name of the field that contains the result, or the format of the output value enclosed in single quotation marks.
For Oracle, calls to DATEDIF and HDIFF translate calls to the native DATE-TIME functions with Datepart Parameter DAY, Startdate, and Enddate.
For DB2, calls to DATEDIF and HDIFF translate as calls to the DAYS function:
SELECT DAYS(d2) - DAYS(d1) FROM tablename
For Teradata, calls to DATEDIF and HDIFF translate as a simple difference between two date values for the DATE datatype and as calls to the CAST function for the TIMESTAMP datatype.
The following Master File represents a DB2 table named DATETIME with two DATE fields named DATE1 and DATE2 and two TIMESTAMP fields named TIMEST1 and TIMEST2:
FILENAME=DATETIME, SUFFIX=DB2 , $ SEGMENT=DATETIME, SEGTYPE=S0, $ FIELDNAME=DATE1, ALIAS=DATE1, USAGE=YYMD, ACTUAL=DATE, MISSING=ON, $ FIELDNAME=DATE2, ALIAS=DATE2, USAGE=YYMD, ACTUAL=DATE, MISSING=ON, $ FIELDNAME=TIMEST1, ALIAS=TIMEST1, USAGE=HYYMDm, ACTUAL=HYYMDm, MISSING=ON, $ FIELDNAME=TIMEST2, ALIAS=TIMEST2, USAGE=HYYMDm, ACTUAL=HYYMDm, MISSING=ON, $
The following request calculates the number of days difference between DATE1 and DATE2 using the DATEDIF function and the number of days difference between TIMEST1 and TIMEST2 using the HDIFF function:
SET TRACEUSER = ON SET TRACEOFF = ALL SET TRACEON = STMTRACE//CLIENT TABLE FILE DATETIME PRINT DATE1 DATE2 TIMEST1 TIMEST2 WHERE DATEDIF(DATE2, DATE1, 'D') LT 5 AND HDIFF(TIMEST1, TIMEST1, 'DAY', 'I11') EQ 1 END
The SQL generated for this request replaces the calls to DATEDIF and HDIFF with calls to the SQL DAYS function in the SQL WHERE predicate:
SELECT T1."DATE1", T1."DATE2", T1."TIMEST1", T1."TIMEST2" FROM USER1."DATETIME" T1 WHERE ((DAYS(T1."TIMEST1") - DAYS(T1."TIMEST1")) = 1) AND ((DAYS(T1."DATE1") - DAYS(T1."DATE2")) < 5) FOR FETCH ONLY;
The following Master File represents an Oracle table named DATETIME with two DATE fields named DATE1 and DATE2 and two TIMESTAMP fields named TIMEST1 and TIMEST2:
FILENAME=DATETIME, SUFFIX=SQLORA , $ SEGMENT=DATETIME, SEGTYPE=S0, $ FIELDNAME=DATE1, ALIAS=DATE1, USAGE=YYMD, ACTUAL=DATE, MISSING=ON, $ FIELDNAME=DATE2, ALIAS=DATE2, USAGE=YYMD, ACTUAL=DATE, MISSING=ON, $ FIELDNAME=TIMEST1, ALIAS=TIMEST1, USAGE=HYYMDm, ACTUAL=HYYMDm, MISSING=ON, $ FIELDNAME=TIMEST2, ALIAS=TIMEST2, USAGE=HYYMDm, ACTUAL=HYYMDm, MISSING=ON, $
The following request calculates the number of days difference between DATE1 and DATE2 using the DATEDIF function and the number of days difference between TIMEST1 and TIMEST2 using the HDIFF function:
SET TRACEUSER = ON SET TRACEOFF = ALL SET TRACEON = STMTRACE//CLIENT TABLE FILE DATETIME PRINT DATE1 DATE2 TIMEST1 TIMEST2 WHERE DATEDIF(DATE2, DATE1, 'D') LT 5 AND HDIFF(TIMEST1, TIMEST1, 'DAY', 'I11') EQ 1 END
The SQL generated for this request replaces the calls to DATEDIF and HDIFF with calls to the SQL EXTRACT and TRUNC functions in the SQL WHERE predicate:
SELECT T1."DATE1", T1."DATE2", T1."TIMEST1", T1."TIMEST2" FROM OWNER1.DATETIME T1 WHERE (EXTRACT(DAY FROM (TRUNC(T1."TIMEST1") - TRUNC(T1."TIMEST1")) DAY(9) TO SECOND) = 1) AND (EXTRACT(DAY FROM (TRUNC(T1."DATE1") - TRUNC(T1."DATE2")) DAY(9) TO SECOND) < 5);
The HPART function extracts a specified component from a date-time value and returns it in numeric format. The DPART function extracts a specified component from a date value and returns it in numeric format. Calls to HPART and DPART are optimized when the second parameter is a case insensitive YEAR, MONTH, QUARTER, DAY-OF-MONTH, or DAY. Calls to HPART are also optimized when the second argument is a case-insensitive HOUR, MINUTE, SECOND, or MICROSECOND.
HPART(dtvalue, 'component', outfield)
where:
Date-time
Is a date-time value, the name of a date-time field that contains the value, or an expression that returns the value.
Alphanumeric
Is the name of the component to be retrieved enclosed in single quotation marks.
Integer
Is the field that contains the result, or the integer format of the output value enclosed in single quotation marks.
DPART(datevalue, 'component', outfield)
where:
Date
Is a full component date.
Alphanumeric
Is the name of the component to be retrieved enclosed in single quotation marks. Valid values are:
For year: YEAR, YY
For month: MONTH, MM
For day: DAY, For day of month: DAY-OF-MONTH.
For quarter: QUARTER, QQ
Integer
Is the field that contains the result, or the integer format of the output value enclosed in single quotation marks.
For DB2, calls to HPART translate as calls to the YEAR, MONTH, or DAY function.
For Oracle and Teradata (Both DATE and TIMESTAMP data types), calls to HPART and DPART translate as calls to the EXTRACT function.
The following request uses the HPART function to retrieve the year component from the DB2 TIMEST1 column:
SET TRACEUSER = ON SET TRACEOFF = ALL SET TRACEON = STMTRACE//CLIENT TABLE FILE DATETIME PRINT TIMEST1 WHERE HPART(TIMEST1, 'YEAR', 'I4') EQ 2008 END
The generated SQL replaces the call to the HPART function with a call to the SQL YEAR function in the SQL WHERE predicate:
SELECT T1."TIMEST1" FROM USER1."DATETIME" T1 WHERE (YEAR(T1."TIMEST1") = 2008) FOR FETCH ONLY;
The following request uses the HPART function to retrieve the year component from the Oracle TIMEST1 column:
SET TRACEUSER = ON SET TRACEOFF = ALL SET TRACEON = STMTRACE//CLIENT TABLE FILE DATETIME PRINT TIMEST1 WHERE HPART(TIMEST1, 'YEAR', 'I4') EQ 2008 END
The generated SQL replaces the call to the HPART function with a call to the SQL EXTRACT function in the SQL WHERE predicate:
SELECT T1."TIMEST1" FROM OWNER1.DATETIME T1 WHERE (EXTRACT(YEAR FROM T1."TIMEST1") = 2008);
The HDATE function converts the date portion of a date-time value to the date format YYMD.
HDATE(dtvalue, {'YYMD'|outfield})
where:
Date-time
Is the date-time value to be converted, the name of a date-time field that contains the value, or an expression that returns the value.
Date
Is the output format. The value must be YYMD.
YYMD
Is the field that contains the result.
For DB2 and Teradata, calls to HDATE translate as calls to the CAST function.
For Oracle, calls to HDATE translate as calls to the TRUNC function.
The following request uses the HDATE function to extract the date portion of the TIMEST1 column:
SET TRACEUSER = ON SET TRACEOFF = ALL SET TRACEON = STMTRACE//CLIENT TABLE FILE DATETIME PRINT DATE1 TIMEST1 WHERE HDATE(TIMEST1, 'YYMD') GT DATE1 END
In the generated SQL, the call to HDATE is replaced by a call to the SQL CAST function in the SQL WHERE predicate:
SELECT T1."DATE1", T1."TIMEST1" FROM USER1."DATETIME" T1 WHERE (CAST(T1."TIMEST1" AS DATE) > T1."DATE1") FOR FETCH ONLY;
The following request uses the HDATE function to extract the date portion of the TIMEST1 column:
SET TRACEUSER = ON SET TRACEOFF = ALL SET TRACEON = STMTRACE//CLIENT TABLE FILE DATETIME PRINT DATE1 TIMEST1 WHERE HDATE(TIMEST1, 'YYMD') GT DATE1 END
In the generated SQL, the call to HDATE is replaced by a call to the SQL TRUNC function in the SQL WHERE predicate:
SELECT T1."DATE1", T1."TIMEST1" FROM OWNER1.DATETIME T1 WHERE (TRUNC(T1."TIMEST1") > T1."DATE1");
The SUBSTR character function can be passed to SQL for optimized processing that takes advantage of RDBMS substring functions, thus improving performance and response time.
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;
Information Builders |