ORA-00932 on subquery join from subquery join

join;oraclesubquery

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.

Best Answer

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.

...

Related Solutions

LEFT JOIN conversion to INNER JOIN, can’t change FROM clause

That one caused me quite a bit of headache before I found a halfway elegant solution:

WITH x AS (
    SELECT BASE.id AS b_id
          ,BASE.start_date AS b_start_date
          ,BASE.end_date AS b_end_date
          ,CHILD.id AS c_id
          ,CHILD.start_date AS c_start_date
          ,CHILD.end_date AS c_end_date
          ,CASE WHEN DATE '2011-1-1' BETWEEN CHILD.start_date AND CHILD.end_date
                                     OR CHILD.id IS NULL THEN 0 ELSE 1 END AS c_hit
    FROM   BASE LEFT OUTER JOIN CHILD ON (BASE.ID=CHILD.ID)
    WHERE  BASE.ID = 1
    AND    DATE '2011-1-1' BETWEEN BASE.start_date AND BASE.end_date
    )
SELECT b_id, b_start_date, b_end_date
      ,c_id, c_start_date, c_end_date
FROM   x
WHERE  x.c_hit = 0

UNION ALL
SELECT b_id, b_start_date, b_end_date
      ,NULL, NULL,         NULL
FROM   x
GROUP  BY b_id, b_start_date, b_end_date
HAVING min(c_hit) = 1;

Of course, the better solution would be to get rid of the restrictions and adjust the JOIN in the base query. As @a_horse expressed it so poetically in the comments above:

the perils of ORM frameworks

Anyway, to solve the question at hand:
After selecting rows where BASE.id and BASE.start_date / BASE.end_date match, we have four different cases:

No child at all, so CHILD.id is NULL.
Child matches.
Child does not match and no other matching child.
Child does not match but other matching child.

This is hard to solve on one query level. You could do it with window functions and CASE statements and DISTINCT, in fact I had a basic working query, but it gets monstrous.

Using a CTE dramatically simplifies the syntax.
In the CTE:

Filter BASE on id and date range. Still includes rows with non-matching children.
Assign aliases for identical column names
Tag rows where child matches the date

In the final SELECT:

Return all rows with matching children
Return one row where no matching children exist, with NULLs for child-columns.

Here is working demo on sqlfiddle.com for those who want to toy with it.

Mysql – Delete from table where multiple fields match select subquery from other table

Perhaps the DELETE JOIN would be preferable

DELETE A.* FROM table1 A INNER JOIN table2 b
ON  A.id = B.id
AND a.field1 = b.field1
AND a.field2 = b.field2
AND a.field3 = b.field3
AND b.id = ?;

I wrote about why DELETE with WHERE involving a subquery is sort of unhealthy to deal with in a past post of mine back on Feb 22, 2011: Problem with MySQL subquery

Even though there have been great strides in improving the Query Optimizer, evaluation of subqueries could make keys unavailable for key comparisons downstream.

ALTERNATIVE

Try gathering the keys you know need to be deleted and do the DELETE JOIN with it:

CREATE TABLE DeleteIDs SELECT id FROM table1 WHERE 1=2;
INSERT INTO table1
    SELECT A.id FROM table1 A INNER JOIN table2 b
    ON  A.id = B.id
    AND a.field1 = b.field1
    AND a.field2 = b.field2
    AND a.field3 = b.field3
    AND b.id = ?
;
ALTER TABLE DeleteIDs ADD PRIMARY KEY (id);
DELETE B.* FROM DeleteIDs A INNER JOIN table1 B;
DROP TABLE DeleteIDs;

SUGGESTION

Maybe an index would help

ALTER TABLE table1 ADD INDEX id123 (id,field1,field2,field3);
ALTER TABLE table2 ADD INDEX id123 (id,field1,field2,field3);