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.

Answer 1

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.