Using Microsoft SQLServer 'NVL2' function in TIBCO Data Virtualization results in an error

Using Microsoft SQLServer 'NVL2' function in TIBCO Data Virtualization results in an error

book

Article ID: KB0076893

calendar_today

Updated On:

Products Versions
TIBCO Data Virtualization All supported versions

Description

In TIBCO Data Virtualization, properties to push NVL2 function to the underlying Microsoft SQLServer data source are not present which results in the query to fail with following error:

"Cannot push selectable because the data source SqlServer does not support the function ~nvl2"

Issue/Introduction

Using Microsoft SQLServer 'NVL2' function in TIBCO Data Virtualization results in an error

Environment

All supported operating systems

Resolution

There are two ways to resolve this error. 

Method 1: 
Add the following property in the sqlserver.capabilities file located at "[TDV_Server_Install_dir]\apps\dlm\cis_ds_mssql\conf\" 

NVL2(~any,~any,~any):NVL2($1,$2,$3). Save the file and restart the TDV Server Services for the changes to take effect.

Note: If current TDV instance is on a hotfixed version, copy the function to the mssql<TDV_version><Hotfix_version>.capability file present at the same path location. 

Method 2: 
To retain the changes when installing hotfixes or patches, add the below block in the values.xml file located at [TDV_Server_Install_dir]\conf\adapters\systems\[microsoft_sql_server_version_adapter]\[microsoft_sql_server_version_file]". Save the file and restart the TDV Server Services for the changes to take effect.

<common:attribute xmlns:common="http://www.compositesw.com/services/system/util/common">
  <common:name>/custom/NVL2(~any,~any,~any)</common:name>
  <common:type>STRING</common:type>
  <common:value>NVL2($1,$2,$3)</common:value>
  <common:configID>NVL2(~any,~any,~any)</common:configID>
</common:attribute>

Powered by Wolken Software