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.
Advertisement
Answer
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.