Error while using UNPIVOT - expression must have same datatype as corresponding expression

Question

I have the following dataset using this SQL

SELECT TEST_NUMBER, ANALYSIS, STARTER, DATE_STARTED, SAMPLE
FROM T WHERE T.SAMPLE IN (SELECT ID FROM S)

TestNumber	Analysis	Starter	DateStarted	Sample
528	RD_Test	ABC	01-01-15	4
592	RD_620	XYZ	19-08-18	111

I want to UNPIVOT it as follows:

TestNumber	ParamID	ParamValue
528	Analysis	RD_Test
528	Starter	ABC
528	DateStarted	01-01-15
528	Sample	4
592	Analysis	RD_620
592	Starter	XYZ
592	DateStarted	19-08-18
592	Sample	111

I am using the follwing script using UNPIVOT:

SELECT TEST_NUMBER, ParamID, ParamValue FROM
(SELECT TEST_NUMBER, ANALYSIS, STARTER, DATE_STARTED, SAMPLE
FROM T WHERE T.SAMPLE IN (SELECT ID FROM S)) OriginalT
UNPIVOT
(ParamValue FOR ParamID IN (ANALYSIS, STARTER, DATE_STARTED, SAMPLE)
) ;

But I am getting the following error:

ORA-01790: expression must have same datatype as corresponding expression 01790. 00000 - "expression must have same datatype as corresponding expression"

Can you advise what's the problem?

Answer 1

As the error suggests, you have different data types. Your columns are strings, numbers and dates (I hope), but your final result column ParamValue can only be a single type. So you need to convert all of the original values to the same type - i.e. strings - before unpivoting, e.g.:

SELECT TEST_NUMBER, ParamID, ParamValue
FROM
(
  SELECT to_char(TEST_NUMBER) as test_number,
    ANALYSIS,
    STARTER,
    to_char(DATE_STARTED, 'DD-MM-RR') as date_started,
    to_char(SAMPLE) as sample
  FROM T WHERE T.SAMPLE IN (SELECT ID FROM S)
) OriginalT
UNPIVOT (
  ParamValue FOR ParamID IN (ANALYSIS, STARTER, DATE_STARTED, SAMPLE)
) ;

db<>fiddle

I would generally join the two tables rather than using in() as you have. I'd also perhaps question your date format - particularly using two-digit years, but an unambiguous format like YYYY-MM-DD might be better, unless you're required to have that specific format.