CDATA Snowflake Unpivot

Live Connectivity CData Drivers
, ,
1

Hi,

We’re using CData ADO.NET Provider for Snowflake 2019 [Build 7362] and we’re facing a problem working with UNPIVOT.

We’re targeting the Snowflake_Sample_Data with this command:

SELECT     CC_CALL_CENTER_SK,    CC_CALL_CENTER_ID,    CC_CLOSED_DATE_SK,    CC_OPEN_DATE_SK,    Attribute,    ValueFROM     SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CALL_CENTERUNPIVOT (    Value FOR Attribute IN (        CC_NAME,        CC_CLASS,        CC_HOURS,        CC_MANAGER,        CC_MKT_CLASS,        CC_MKT_DESC,        CC_MARKET_MANAGER    )) AS unpivoted_data;

The query is working fine on Snowflake instance but not with CDATA Snowflake driver.

We’re getting the error below:

Malformed SQL Statement: Expected token ‘)’ but found Identifier with value ‘FOR’ instead

Is UNPIVOT syntax supported for this version of CDATA?

2

Hi ​ @ABJoe ,

Thank you for reaching out!

The UNPIVOT syntax was introduced to our driver’s logic in 2022, which means that the v19 driver you are currently using does not support this functionality. To address this issue, you can either upgrade to the latest version of the driver or use an alternative SQL method to replicate the same behavior.

I conducted some tests on my end, and by using UNION ALL, you can achieve the same results. The only notable difference is that the query will be longer. Please give the following query a try and let me know how it works for you (kindly make sure to make the necessary adjustments if I have missed something):

SELECT     CC_CALL_CENTER_SK,    CC_CALL_CENTER_ID,    CC_CLOSED_DATE_SK,    CC_OPEN_DATE_SK,    'CC_NAME' AS Attribute,    CC_NAME AS ValueFROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CALL_CENTERUNION ALLSELECT     CC_CALL_CENTER_SK,    CC_CALL_CENTER_ID,    CC_CLOSED_DATE_SK,    CC_OPEN_DATE_SK,    'CC_CLASS' AS Attribute,    CC_CLASS AS ValueFROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CALL_CENTERUNION ALLSELECT     CC_CALL_CENTER_SK,    CC_CALL_CENTER_ID,    CC_CLOSED_DATE_SK,    CC_OPEN_DATE_SK,    'CC_HOURS' AS Attribute,    CC_HOURS AS ValueFROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CALL_CENTERUNION ALLSELECT     CC_CALL_CENTER_SK,    CC_CALL_CENTER_ID,    CC_CLOSED_DATE_SK,    CC_OPEN_DATE_SK,    'CC_MANAGER' AS Attribute,    CC_MANAGER AS ValueFROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CALL_CENTERUNION ALLSELECT     CC_CALL_CENTER_SK,    CC_CALL_CENTER_ID,    CC_CLOSED_DATE_SK,    CC_OPEN_DATE_SK,    'CC_MKT_CLASS' AS Attribute,    CC_MKT_CLASS AS ValueFROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CALL_CENTERUNION ALLSELECT     CC_CALL_CENTER_SK,    CC_CALL_CENTER_ID,    CC_CLOSED_DATE_SK,    CC_OPEN_DATE_SK,    'CC_MKT_DESC' AS Attribute,    CC_MKT_DESC AS ValueFROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CALL_CENTERUNION ALLSELECT     CC_CALL_CENTER_SK,    CC_CALL_CENTER_ID,    CC_CLOSED_DATE_SK,    CC_OPEN_DATE_SK,    'CC_MARKET_MANAGER' AS Attribute,    CC_MARKET_MANAGER AS ValueFROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CALL_CENTERORDER BY CC_CALL_CENTER_ID;