ORA-00932 on subquery join from subquery join

Question

The only guess I have is that perhaps the size of the subquery is considered a CLOB? I have no null values for the fields selected AFAIK.

SELECT DISTINCT 
 kbq_ent.qid AS kbq_ent_qid,
 kbq_ent.category AS kbq_ent_category,
 kbq_ent.consequence AS kbq_ent_consequence,
 kbq_ent.diagnosis AS kbq_ent_diagnosis,
 kbq_ent.last_service_modify_datetime AS kbq_ent_last_servic_1,
 kbq_ent.patchable AS kbq_ent_patchable,
 kbq_ent.pci_flag AS kbq_ent_pci_flag,
 kbq_ent.published_datetime AS kbq_ent_published_d_2,
 kbq_ent.severity_level AS kbq_ent_severity_le_3,
 kbq_ent.solution AS kbq_ent_solution,
 kbq_ent.title AS kbq_ent_title,
 kbq_ent.vuln_type AS kbq_ent_vuln_type,
 kbq_ent.discovery_remote AS kbq_ent_discovery_r_4,
 kbq_ent.cvss_base AS kbq_ent_cvss_base,
 kbq_ent.cvss_temporal AS kbq_ent_cvss_tempor_5,
 kbq_ent.last_customization_user_login AS kbq_ent_last_custom_6,kbq_ent.last_customization_datetime AS kbq_ent_last_custom_7 
FROM kbq_ent JOIN (SELECT vuln_eid AS vuln_eid, qid AS qid, anon_2.rel_eid AS anon_2_rel_eid 
FROM (
 SELECT entity_vuln.entity_id AS vuln_eid,
 entity_vuln.qid AS qid 
 FROM entity_vuln
 ) JOIN (
 SELECT entity_responsible_org.entity_id AS rel_eid
 FROM entity_responsible_org 
 WHERE entity_responsible_org.org_id = :org_id_1
 ) anon_2 ON vuln_eid = anon_2.rel_eid) anon_1 ON kbq_ent.qid = anon_1.qid

kbq_ent.solution AS kbq_ent_solution is a CLOB.

• Balazs Papp: At least add table definitions with datatypes and the exact error you got. You mentioned CLOB. Maybe you have a CLOB column in your SELECT list. You can not use DISTINCT on CLOB columns, and you will receive an ORA-00932 if you try. But without the column types and the error, I can not say for sure.
• user1822: Not the reason for the error, but: all those derived tables are useless and can be replaced with a simple reference to the base table used inside them. FROM kbq_ent JOIN entity_vuln ON ... JOIN entity_responsible_org anon_2 ON ... WHERE entity_responsible_org.org_id = :org_id_1
• SkyLeach: @a_horse_with_no_name I know, but I'm in the process of building and perfecting the query which is easier to do with subqueries that I can use to inspect the results.

Answer 1

LOB Rules and Restrictions

...

• You cannot specify LOB columns in the ORDER BY clause of a query, the GROUP BY clause of a query, or an aggregate function.

• You cannot specify a LOB column in a SELECT... DISTINCT or SELECT... UNIQUE statement or in a join. However, you can specify a LOB attribute of an object type column in a SELECT... DISTINCT statement, a query that uses the UNION, or a MINUS set operator if the object type of the column has a MAP or ORDER function defined on it.

...

• SkyLeach: This is the right answer, but in trying to work around it I've had nothing but headaches. I changed the query to pull only the qid, order on it, and then do the distinct and a full record pull from the result. Problem is that while this works, it is FAR slower than doing two queries?