CODEWITHSUNDEEP
sqlselectplsqlwhere-clauseselect-into
Golan_trevize
Golan_trevize

Reputation: 2433

In clause of a select but with more than one value to look for

I have a set of results that I query with connect by prior, now I need to check in the where clause of a query if ONE of those results is IN some other set of values(a select * from another table). I'm trying to use 'IN' but I think that that only works when I have one unique value to check for, and not a group of values.

SELECT COUNT('X')
INTO V_COUNT 
FROM SIC_NEA_CATFRU
WHERE (SELECT cod_nivel_estr_art
       FROM niveles_estr_art
       CONNECT BY PRIOR cod_nivel_estr_art_P = cod_nivel_estr_art
       START WITH cod_nivel_estr_art = V_COD_NIVEL_eSTR_ART) IN ( SELECT COD_NIVEL_eSTR_ART FROM SIC_NEA_CATFRU);

Upvotes: 0

Views: 37

Answers (1)

Littlefoot
Littlefoot

Reputation: 143103

Would the intersect set operator do? Something like this: first query is your hierarchical query, while the second returns values from the sic_nea_catfru table. If there are any matches, you'll know how many:

SELECT COUNT (*)
  INTO v_count
  FROM (    SELECT cod_nivel_estr_art
              FROM niveles_estr_art
        CONNECT BY PRIOR cod_nivel_estr_art_p = cod_nivel_estr_art
        START WITH cod_nivel_estr_art = v_cod_nivel_estr_art
        INTERSECT
        SELECT cod_nivel_estr_art FROM sic_nea_catfru)

Upvotes: 1

Related Questions