TD_UNPIVOT - ERROR [Teradata Version 15.10.0.2, DB Version 16.20.32.08]

Database

TD_UNPIVOT - ERROR [Teradata Version 15.10.0.2, DB Version 16.20.32.08]

Hello,

I am doing a transposition of data using TD_UNPIVOT & i get following error:

SELECT Failed. [9134] Failure in TD_Unpivot contract function. Error determining column type of value columns.

 

Now i had this error before, where i was transposing date, char, integer - but i fixed it by casting everything to varchar, since in the end it gets dump in a .txt file anyways (and that still works).

 

However, this time i can't get rid of the error eventhough i casted everything as varchar. The only difference between before and now is:

 

Before i was simply calling column names from an exsisting table view in our DB

Now i am doing the same, but i am also add a few case when columns and constant columns. Removing those i can perform the transposition...

Anyone know why this is happening & if there is a workaround ?

 

(total number of columns i am transposing is 17, i just brough 7 in example below)

SELECT 
      COLUMN_NAME, column_value
FROM TD_UNPIVOT (
    ON (
        SELECT
            CAST(CPR_NR AS VARCHAR(100)) AS CPR_NR,
            CAST(EMAIL_ADDRESS AS VARCHAR(100)) AS EMAIL_ADDRESS,
            CAST(POST_ADDRESS AS VARCHAR(100)) AS POST_ADDRESS,
            CAST(CITY AS VARCHAR(100)) AS CITY,
            CAST(POSTAL_CODE AS VARCHAR(100)) AS POSTAL_CODE,
            CAST(FULL_NAME AS VARCHAR(100)) AS FULL_NAME,
            CAST(ProductLevel4 AS VARCHAR(100)) AS PRODUCT_LEVEL_4,

            '002DKCPR' AS one, '002DKEML' AS two, '002DKAD1' AS three, '002DKAD2' AS four,
            '002DKAD3' AS five, '002DKNAM' AS six, '002FSTY1' AS seven
             
            FROM DB_NAME.VIEW_NAME    ) USING
        VALUE_COLUMNS('column_name', 'column_value')
        UNPIVOT_COLUMN('event_number')
        COLUMN_LIST('one,CPR_NR', 'two,EMAIL_ADDRESS','three,POST_ADDRESS', 'four,CITY', 
                    'five,POSTAL_CODE', 'six,FULL_NAME', 'seven,PRODUCT_LEVEL_4'                    )
                    
) AS keypivot  

 


Accepted Solutions
Highlighted
Teradata Employee

Re: TD_UNPIVOT - ERROR [Teradata Version 15.10.0.2, DB Version 16.20.32.08]

Your example appears that it should work.

 

Note that the CHARACTER SET must also match for input column values being pivoted to the same output VARCHAR column.

Character literals are CHARACTER SET UNICODE unless you explicitly convert, e.g. TRANSLATE('002DKCPR' USING Unicode_to_Latin).

And CAST to VARCHAR (from some other data type) will assign the session default (Latin for Teradata mode, Unicode for ANSI mode) unless you include a CHARACTER SET clause as part of the CAST.

 

1 ACCEPTED SOLUTION
2 REPLIES 2
Highlighted
Teradata Employee

Re: TD_UNPIVOT - ERROR [Teradata Version 15.10.0.2, DB Version 16.20.32.08]

Your example appears that it should work.

 

Note that the CHARACTER SET must also match for input column values being pivoted to the same output VARCHAR column.

Character literals are CHARACTER SET UNICODE unless you explicitly convert, e.g. TRANSLATE('002DKCPR' USING Unicode_to_Latin).

And CAST to VARCHAR (from some other data type) will assign the session default (Latin for Teradata mode, Unicode for ANSI mode) unless you include a CHARACTER SET clause as part of the CAST.

 

Re: TD_UNPIVOT - ERROR [Teradata Version 15.10.0.2, DB Version 16.20.32.08]

The column_name fields did not need a TRANSLATE, but some of the column_values from the view did. 

Now my issue is solved. Thank you for your help and fast reply.