Snowflake how can we cast all fields of select query when unpivot is not detecting fields with a different types?

Question

Following up on this question, I made the following script:

select
    OBSERVATION_DATE,
    name_col,
    md5(
        md5(
            concat_ws(
                '',
                val_col,
                DATE(OBSERVATION_DATE, 'DD/MM/YYYY'),
                'CAMP',
                CAMPNO
            )
        )
    ),
    current_timestamp(),
    NULL,
    'ONA',
    val_col,
    md5(
        concat_ws(
            '',
            'name_col',
            DATE(OBSERVATION_DATE, 'DD/MM/YYYY'),
            'CAMP',
            CAMPNO
        )
    )
from
    (
        select
            T.*
        FROM
            TEMP_TABLE_NAME T
    ) unpivot (val_col for name_col in (FACILITY_ID, CAMPNO));

It will help me transpose a temporary table to add all related fields into another table, where each field/value pair represent a row.

The error I am getting out of this query is:

SQL compilation error: error line 2 at position 77 invalid identifier 'CAMPNO'

I think it is maybe because the campNo is a number, and all fields should be casted to varchar, so I tried this query:

select cast(campno as varchar) as CAMPNO FROM TEMP_TABLE_NAME;

And it worked, but within the second select statement I got an error:

...from (select cast(CAMPNO as varchar) as campno, T.* FROM TEMP_TABLE_NAME T)

And got an error of:

SQL compilation error: ambiguous column name 'CAMPNO'

I need to cast all fields inside select * from myTable into varchar.

score 1 · Accepted Answer · answered Mar 01 '21 at 18:00

The reason you're getting this error is that, once you unpivot, there is no column named campno. You unpivoted that column into rows:

Setup example

create or replace transient table source_table
(
    id               number,
    observation_date varchar,
    facility_id      varchar,
    campno           number
);

insert overwrite into source_table
values (1, '01/01/2020', 2, 23),
       (2, '02/01/2020', 3, 44),
       (3, '03/01/2020', 1, 123),
       (4, '04/01/2020', 1, 2233)
;

If you do a select * from this source table, you have a column called campno and facility_id:

select st.* from source_table st;

-- Resulting table:
-- +--+----------------+-----------+------+
-- |ID|OBSERVATION_DATE|FACILITY_ID|CAMPNO|
-- +--+----------------+-----------+------+
-- |1 |01/01/2020      |2          |23    |
-- |2 |02/01/2020      |3          |44    |
-- |3 |03/01/2020      |1          |123   |
-- |4 |04/01/2020      |1          |2233  |
-- +--+----------------+-----------+------+

But once you unpivot the table on the campno and facility_id fields, then the column names become rows and you can no longer select campno, facility_id. You also need to make sure the datatypes of the column you're unpivoting on are the same (hence the subquery):

select *
from (
    select
        id,
        observation_date,
        facility_id,
        campno::varchar as campno
    from source_table) unpivot ( val_col for name_col in (facility_id, campno) );

-- Resulting table:
-- +--+----------------+-----------+-------+
-- |ID|OBSERVATION_DATE|NAME_COL   |VAL_COL|
-- +--+----------------+-----------+-------+
-- |1 |01/01/2020      |FACILITY_ID|2      |
-- |1 |01/01/2020      |CAMPNO     |23     |
-- |2 |02/01/2020      |FACILITY_ID|3      |
-- |2 |02/01/2020      |CAMPNO     |44     |
-- |3 |03/01/2020      |FACILITY_ID|1      |
-- |3 |03/01/2020      |CAMPNO     |123    |
-- |4 |04/01/2020      |FACILITY_ID|1      |
-- |4 |04/01/2020      |CAMPNO     |2233   |
-- +--+----------------+-----------+-------+

See how there is no column named campno or facility_id above? They have been unpivoted into rows and are no longer columns...

I think (but not sure) what you might be looking for is:

select
    id,
    observation_date,
    name_col,
    val_col,
    'ONA',
    md5(concat_ws('', id, name_col, val_col, observation_date)),
    current_timestamp()
from (
    select
        id,
        to_date(observation_date, 'DD/MM/YYYY') as observation_date,
        facility_id::varchar                    as facility_id,
        campno::varchar                         as campno
    from source_table
) unpivot ( val_col for name_col in (facility_id, campno) )

-- Resulting table:
-- +--+----------------+-----------+-------+-----+----------------------------------------------------------------+------------------------------------+
-- |ID|OBSERVATION_DATE|NAME_COL   |VAL_COL|'ONA'|MD5(MD5(CONCAT_WS('', ID, NAME_COL, VAL_COL, OBSERVATION_DATE)))|CURRENT_TIMESTAMP()                 |
-- +--+----------------+-----------+-------+-----+----------------------------------------------------------------+------------------------------------+
-- |1 |2020-01-01      |FACILITY_ID|2      |ONA  |19baf986df81f1818afae848cd14fc87                                |2021-03-01 09:59:45.919000000 -08:00|
-- |1 |2020-01-01      |CAMPNO     |23     |ONA  |1fcb518697772362a0dabcba7aacfa8a                                |2021-03-01 09:59:45.919000000 -08:00|
-- |2 |2020-01-02      |FACILITY_ID|3      |ONA  |60a82dbc3d1b78d09519fc50b26026cd                                |2021-03-01 09:59:45.919000000 -08:00|
-- |2 |2020-01-02      |CAMPNO     |44     |ONA  |cb03dc5d1df4e2548f26284c5ff339c2                                |2021-03-01 09:59:45.919000000 -08:00|
-- |3 |2020-01-03      |FACILITY_ID|1      |ONA  |fe0dd77e601f6f3bac4cde8da537eb3d                                |2021-03-01 09:59:45.919000000 -08:00|
-- |3 |2020-01-03      |CAMPNO     |123    |ONA  |95604e260fe1a69bc54100b08fee6d87                                |2021-03-01 09:59:45.919000000 -08:00|
-- |4 |2020-01-04      |FACILITY_ID|1      |ONA  |a94029663591b1c942e9f3be1467e04f                                |2021-03-01 09:59:45.919000000 -08:00|
-- |4 |2020-01-04      |CAMPNO     |2233   |ONA  |1d90ae0854a9042bf44906511e90ced8                                |2021-03-01 09:59:45.919000000 -08:00|
-- +--+----------------+-----------+-------+-----+----------------------------------------------------------------+------------------------------------+

It's pointless to md5(md5()) which I think I've seen in another one of your posts so not sure why you're doing that.

I will try it tomorrow. For the md5, the foreign key is hashed. Amd for this sat table there is no use for its primary key so we made it as the hash of the already hashed foreign key. We are planning to replace it with a hashed sequence in our later data model updates. — alim1990, Mar 01 '21 at 18:16
What if the fields are dynamic? How can I cast all fields into varchar without knowing what fields we have ? — alim1990, Mar 01 '21 at 18:17
I commented on your other post which asks about dynamic fields numbers. If you don't know how many columns you need to unpivot on then you'll need to use a stored proc to do this. — Simon D, Mar 01 '21 at 18:19
I am using a procedure. But how can I cast them all as varchar ? I know most of them are dates and numbers as we are collecting aggregated data. — alim1990, Mar 01 '21 at 18:20
You might have to ask that as a new question where you show the stored proc code since it's a bit hard for me to understand what problem you are having with the proc — Simon D, Mar 01 '21 at 18:24
Here is a new post: https://stackoverflow.com/questions/66429053/snowflake-how-to-bind-a-specific-value-of-an-object-where-the-key-is-equal-to-th — alim1990, Mar 01 '21 at 20:17

Snowflake how can we cast all fields of select query when unpivot is not detecting fields with a different types?

1 Answers1

Linked