CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle11g
jonny
jonny

Reputation: 797

ORA-00932: inconsistent datatypes: expected - got CLOB in union query

I have some Oracle query where i need to use CLOB_AGG function because when i used string_agg i got string buffer error (more than 4000 characters). The problem is that when i use query separately it works but when i do the union.

I get:

inconsistent datatypes: expected - got CLOB error.

SELECT
  A.EVENT_ID,
  clob_agg(CAL.CALENDAR_DT) AS CALENDAR_DAYS
FROM
  TABLE1 A,
  CALENDAR CAL
WHERE
AND CAL.EVENT_ID(+) = A.EVENT_ID
GROUP BY
  A.EVENT_ID
UNION
SELECT
  B.SUB_EVENT_CD,
  CLOB_AGG(CALSUB.CALENDAR_DT) AS CALENDAR_DAYS
FROM
  TABLE1 A,
  TABLE1 B,
  RSS_CALENDAR CALSUB
WHERE
AND A.EVENT_ID             = B.EVENT_ID
AND CALSUB.SUB_EVENT_ID(+) = B.SUB_EVENT_ID
GROUP BY
  A.EVENT_ID,
  B.SUB_EVENT_ID

Upvotes: 3

Views: 3742

Answers (1)

Matt
Matt

Reputation: 15061

There were a few issues with your code. You needed a UNION ALL rather than a UNION and you had two AND's straight after a WHERE.

I have also replaced your joins with standard notation explicit joins rather than using join operators.

SELECT A.EVENT_ID, clob_agg(CAL.CALENDAR_DT) AS CALENDAR_DAYS
FROM TABLE1 A
LEFT OUTER JOIN CALENDAR CAL ON CAL.EVENT_ID = A.EVENT_ID
GROUP BY A.EVENT_ID
UNION ALL
SELECT B.SUB_EVENT_CD, CLOB_AGG(CALSUB.CALENDAR_DT) AS CALENDAR_DAYS
FROM TABLE1 A
INNER JOIN TABLE1 B ON A.EVENT_ID = B.EVENT_ID
LEFT OUTER JOIN RSS_CALENDAR CALSUB ON CALSUB.SUB_EVENT_ID = B.SUB_EVENT_ID
GROUP BY A.EVENT_ID, B.SUB_EVENT_ID

Upvotes: 2

Related Questions