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.

Simon D Simon D · Accepted Answer · 2021-03-01T18:00:43

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.

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

1 Answers