Minus operation is not pushed to Sybase IQ data source when casting as date

Minus operation is not pushed to Sybase IQ data source when casting as date

book

Article ID: KB0082809

calendar_today

Updated On:

Products Versions
TIBCO Data Virtualization All Versions

Description

When you are doing minus operation with casting as date to find the date interval then minus operation is not pushed to Sybase IQ data source when casting as date. Please see the below query for example -
 
select CAST('2016-07-08' AS DATE) - Cast(T2.FROM_Z AS DATE) as "TEST_CAST" from /shared/Goldman_Cast_issue/RequestManagerCIP/public_cip_master_prod2/dbo/CaseReqApproval T2
 
You will find the below reason in the query execution plan
 
Cannot push selectable because the data source Sybase_IQ does not support the function --CAST('2016-07-08' AS DATE) - Cast(T2.FROM_Z AS DATE) --

Issue/Introduction

Minus operation is not pushed to Sybase IQ data source when casting as date.

Resolution

Please add the below capabilities in the Sybase IQ data source capability file to push the minus operation -
 
@date-@date: DATEDIFF(dd, $2, $1)
 
Note: DATEDIFF function is more appropriate in such scenerio and DATEDIFF function should be used directly in query instead of minus operation.