SQL Oracle get rows which matche exactly the list of values-CodePudding

I have a table and I need to get rows which matche a list of values. I've created a SQLFiddle where you can see the result.

This query is used to populate a DropDownList which uses or not autocompletion. So there are two possible uses for this request: the first is with an empty LIKE ('%') and the second with a populated LIKE ('***%'). Only the first use is problematic because all rows are selected without LIKE condition and I only need those which matches the list of values of PGM column.

My issue is that I can't get the rows which PGM = 'N' and 'L' (according to Fiddle example), not just 'L' or just 'N', nor 'L' and 'N' and 'P': only the pair 'N' and 'L' if this is the values in the list.

SELECT al.* FROM ALPHA al WHERE al.PN IN (
    SELECT al2.PN FROM ALPHA al2 WHERE TRIM(al2.PGM) IN ('N','L') AND TRIM(al2.NUM) IN ('2350') AND TRIM(al2.TEAM) = 'R2D2' 
    AND TRIM(al2.PN) LIKE '%' AND (
      SELECT COUNT(*) FROM ALPHA al3 WHERE TRIM(al3.PGM) IN ('N','L') AND TRIM(al3.PN) LIKE '%') = 2
)
AND TRIM(al.PGM) IN ('N','L') ORDER BY al.PN ASC;

/*
The idea, in a nutshell, is as follows:
line 1 - Get all rows from ALPHA
line 2 - Get filtered rows from ALPHA according to PGM, NUM, TEAM
line 3 - Filter PN with LIKE (according to autocompletion or not)
line 4 - Get rows which have a PGM = N and PGM = L ONLY | As there are two values in the list it should returns 2 but it does not because of empty LIKE
line 6 - Filter all rows according to PGM
*/

I'm aware of this link but I cannot use it in my case, in PROD I'm working on VIEW not joined TABLES.

Thank you !

CodePudding user response：

You appear to want to only get the rows where there is an N and an L for each PN that also matches the filters; in that case, use the analytic COUNT function with conditional aggregation:

SELECT sms, pgm, pn, team, num
FROM   (
  SELECT a.*,
         COUNT(CASE PGM WHEN 'N' THEN 1 END) OVER (PARTITION BY PN) AS cnt_n,
         COUNT(CASE PGM WHEN 'L' THEN 1 END) OVER (PARTITION BY PN) AS cnt_l,
         COUNT(CASE WHEN PGM IN ('L', 'N') THEN NULL ELSE 1 END) OVER (PARTITION BY PN) AS cnt_other
  FROM   ALPHA a
  WHERE  PN   LIKE 'BB%'
  AND    NUM  = '2350'
  AND    TEAM = 'R2D2'
)
WHERE cnt_l > 0
AND   cnt_n > 0
AND   cnt_other = 0
ORDER BY PN ASC;

fiddle

CodePudding user response：

You can create a CTE (pgm_filter) and use it later within your WHERE clause.

WITH
    pgm_filter AS
        (
            Select   PN, PGM_LIST
            From     (SELECT DISTINCT PN, PGM, LISTAGG(PGM, '') WITHIN GROUP (Order By PGM) OVER(PARTITION BY PN) "PGM_LIST"
                      FROM   ALPHA a)
            Where    PGM_LIST  = 'LN'
            Group By PN, PGM_LIST      
        )

LISTAGG() analytic function is used to generate a list of PGM values per PN. Where condition filters just the PNs that have your specific combination of values 'L' and 'N' - nothing less and nothing more.
Main SQL with pgm_filter in WHERE clause:

SELECT    SMS, PGM, PN, TEAM, NUM
FROM      ALPHA
WHERE     PN IN(Select PN From pgm_filter) And
          PN   LIKE 'PP%' And
          NUM  = '2350' And
          TEAM = 'R2D2'

... this will filter the rows having 'LN' combination of PGM values along with any other filter you need.
Regards...