Skip to main content

Hi,

 

We’re using CData ADO.NET Provider for Snowflake 2019 0Build 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,
Value
FROM
SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CALL_CENTER
UNPIVOT (
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?

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 Value
FROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CALL_CENTER

UNION ALL

SELECT
CC_CALL_CENTER_SK,
CC_CALL_CENTER_ID,
CC_CLOSED_DATE_SK,
CC_OPEN_DATE_SK,
'CC_CLASS' AS Attribute,
CC_CLASS AS Value
FROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CALL_CENTER

UNION ALL

SELECT
CC_CALL_CENTER_SK,
CC_CALL_CENTER_ID,
CC_CLOSED_DATE_SK,
CC_OPEN_DATE_SK,
'CC_HOURS' AS Attribute,
CC_HOURS AS Value
FROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CALL_CENTER

UNION ALL

SELECT
CC_CALL_CENTER_SK,
CC_CALL_CENTER_ID,
CC_CLOSED_DATE_SK,
CC_OPEN_DATE_SK,
'CC_MANAGER' AS Attribute,
CC_MANAGER AS Value
FROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CALL_CENTER

UNION ALL

SELECT
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 Value
FROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CALL_CENTER

UNION ALL

SELECT
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 Value
FROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CALL_CENTER

UNION ALL

SELECT
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 Value
FROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CALL_CENTER
ORDER BY CC_CALL_CENTER_ID;