0

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.

  • 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. – Balazs Papp May 4 '16 at 21:39
  • 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 – a_horse_with_no_name May 4 '16 at 22:09
  • @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. – SkyLeach May 5 '16 at 13:26
0

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.

...

  • 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? – SkyLeach May 4 '16 at 22:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.