CODEWITHSUNDEEP
sqloracle-database
NoSocks
NoSocks

Reputation: 61

ORA-00923: expected - got CLOB

I've got a query that uses GROUP BY, so I need to put the selected items in either the GROUP BY or in an aggregate function. When I put put them in the GROUP BY, t2.category throws the error in the title. I tried incorporating an aggregate function in the CASE/WHEN statements instead of putting t2.category in the GROUP BY, but I get an invalid identifier error. An example of t2.category could be "Server Reliability -- connection issues because of db dropout". How can I get rid of this error?

SELECT
 msr._id,

 CASE ans.question_id WHEN '767' THEN ans.category END "767",
 CASE ans.question_id WHEN '768' THEN ans.category END "768"

FROM table1 t1
LEFT OUTER JOIN table2 t2
ON t1._id = t2._id

WHERE t2.question_id in (767,768) AND t2.assigned_to not in ('Smith, John')
 AND t1.request_type_id in (288,289) and t1.status_id not in (0, 11);

GROUP BY t1._id, t2.question_id, t2.category

Upvotes: 0

Views: 178

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

Other than only using the first 4000 characters of the CLOB value, which probably isn't acceptable, the only way I can see to get around the restictions on CLOBs (including that you can't group by them) is to join to the second table multiple times:

SELECT
 msr.id,
 ans_767.category AS "767",
 ans_768.category AS "768"
FROM msr
LEFT OUTER JOIN ans ans_767
ON ans_767.id = msr.id
AND ans_767.question_id = 767
AND ans_767.assigned_to not in ('Smith, John')
LEFT OUTER JOIN ans ans_768
ON ans_768.id = msr.id
AND ans_768.question_id = 768
AND ans_768.assigned_to not in ('Smith, John')
WHERE msr.request_type_id in (288,289)
AND msr.status_id not in (0, 11);

        ID 767                  768                
---------- -------------------- --------------------
         1 back                 dev
         2                      dev

They are both left joins but mean you only get one row back, so don't have to collapse the values you get from two rows with your version.

I've had to move the question_id filter into each of the ON clauses; but I've also moved the assigned_to filter, as having it in the where clause turns the outer joins back into inner joins.

This is ugly but bearable for two question IDs, but doesn't scale well in terms of maintenance and readability.

SQL Fiddle demo, using values from your previous question; and your original code (sort of; edited a bit) failing.

Upvotes: 2

Related Questions