ORA-01790: expression must have same datatype as corresponding expression in UNPIVOT query-CodePudding

I had an unpivot query that worked well when pulling from one table:

WITH test
 (SELECT field1,field2,field3,field4
  FROM Table1
  WHERE id = 1234)
 SELECT * FROM test
 UNPIVOT 
 (Value FOR Field IN (field1 as 'Field 1', field2 as 'Field 2', field3 as 'Field 3', field4 as 'Field 4'))

that displayed data as:

Field 1    Value1
Field 2    Value2
Field 3    Value3
Field 4    Value4

Now that I have changed the query to pull

WITH test
(SELECT t.field1,t.field2,t.field3,t.field4, x.qty1, x.qty2, (x.qty1   x.qty2) qty3
FROM Table1 t
 ,(select id, field1, Function1(field1, field3) qty1, Function2(field1, field3) qty2 FROM Table1) x
WHERE t.id = x.id
AND id = 1234)
SELECT * FROM test
UNPIVOT 
(Value FOR Field IN (field1 as 'Field 1', 
field2 as 'Field 2', 
field3 as 'Field 3', 
field4 as 'Field 4',  <-- ORA-01790: expression must have same datatype as corresponding expression
qty1 as 'Quantity 1', 
qty2 as 'Quantity 2', 
qty3 as 'Total'))

Not sure why is this happening and how can I fix it

CodePudding user response：

Your field1 to field4 values are (or appear to be) strings, while your qty1 to qty3 values are numbers. As the error message says, they all need to be the same datatype.

You might be confused that the message appears to be related to one of the original columns from the first query, field4, rather than one of the new columns. That's because statements are usually parsed backwards, so the parser saw the numeric columns first, and complained about the first non-numeric one.

Anyway, you just need to convert your numbers to strings in the inner query, e.g.:

WITH test
(SELECT t.field1,t.field2,t.field3,t.field4,
  to_char(x.qty1) as qty1, to_char(x.qty2) as qty2, to_char(x.qty1   x.qty2) as qty3
...

or in full:

WITH test AS
(SELECT t.field1,t.field2,t.field3,t.field4,
  to_char(x.qty1) as qty1, to_char(x.qty2) as qty2, to_char(x.qty1   x.qty2) as qty3
FROM Table1 t
 ,(select id, field1, Function1(field1, field3) qty1, Function2(field1, field3) qty2 FROM Table1) x
WHERE t.id = x.id
AND id = 1234)
SELECT * FROM test
UNPIVOT 
(Value FOR Field IN (field1 as 'Field 1', 
field2 as 'Field 2', 
field3 as 'Field 3', 
field4 as 'Field 4',
qty1 as 'Quantity 1', 
qty2 as 'Quantity 2', 
qty3 as 'Total'))

db<>fiddle demo using dummy values from dual instead of your real table.

You can, of course, apply explicit formatting in those to_char() calls - I've just kept it simple to demonstrate the issue.

You will probably hit other errors - AND id = 1234 will be ambiguous for example - and it would be better to use modern join syntax. I'm not sure why you are joining though - you could simplify the inner query to:

WITH test AS (
  SELECT field1, field2, field3, field4,
    to_char(qty1) as qty1, to_char(qty2) as qty2, to_char(qty1   qty2) as qty3
  FROM (
    SELECT field1, field2, field3, field_4,
      Function1(field1, field3) as qty1, Function2(field1, field3) as qty2
    FROM Table1
    WHERE id = 1234
  )
)

db<>fiddle with that nested query version added.