UNION - inconsistent datatype got CLOB-CodePudding

I have a table that look like this

name	value
a	1
b	2
c	3

Of course not with these datas, but I will use it as an example

I need to use it as a inner join, where I can have each name as a column.
It is a defined amount of rows, so that should not be a problem

I have tried to do it as

SELECT
  value AS a, 
  NULL as b
FROM ex
WHERE name = 'a'

UNION

SELECT
   NULL as a,
   value AS b 
FROM ex
WHERE name = 'b'

And so on, but I get the error ORA-00932: inconsistent datatypes: expected - got CLOB

I have also tried with

 SELECT 
    CASE WHEN name = 'a' THEN value ELSE NULL END AS a,
    CASE WHEN name = 'b' THEN value ELSE NULL END AS b
 FROM ex
 WHERE name IN ('a', 'b')

But the result from this is of course

a	b
1	NULL
NULL	2

But I need to eliminate the NULL values, so I only have one row like

a	b
1	2

Does anybody have a good idea of how to solve this problem?

I can of course make 4 joins, but I was thinking, if it could be done in one join, as that will possibly be faster than look in the same table 4 times

CodePudding user response：

I have the impression that you just want to get the value per name. Is this what you aim for:

WITH dat AS
(

  SELECT 'a' AS NAME, 1 AS VALUE FROM dual UNION ALL
  SELECT 'b', 2 FROM dual UNION ALL
  SELECT 'c', 3 FROM dual
)
SELECT *
  FROM (SELECT *
          FROM dat
         PIVOT (MAX(VALUE) FOR NAME IN ('a','b'))); -- list of the ones you need