How can I dynamically pass all customer IDs to the WHERE IN clause of subquery-CodePudding

I'm trying to fetch 2nd MAX END_DATE for all listed customers by passing all customer_id to the WHERE IN clause of subquery. Right now my static customer_id in WHERE IN clause gives me the desired output but the rest customer_id shows NULL.

How can i pass all customer_id dynamically instead of static value in the WHERE IN of subquery. Would be appreciated your help.

SELECT d.customer_id
, ( SELECT DISTINCT g.end_date
FROM contract g
WHERE g.end_date = (SELECT MAX(g.end_date) FROM contract g WHERE g.customer_id IN ('64','65','69') 
AND g.customer_id = d.customer_id 
AND g.end_date<(SELECT MAX(g.end_date) FROM contract g WHERE g.customer_id IN ('64','65','69') 
AND g.customer_id = d.customer_id)
)) END_DATE
FROM customer_vw d
GROUP BY d.customer_id;

CodePudding user response：

Maybe you're overcomplicating it; analytic functions might help in this case. Here's an example - I'd want to select the 2nd highest hiredate from a table per each department:

SQL> break on deptno
SQL> select deptno, hiredate from emp order by deptno, hiredate desc;

    DEPTNO HIREDATE
---------- ----------
        10 23.01.1982
           17.11.1981    --> this, for department 10
           09.06.1981
        20 12.01.1983
           09.12.1982    -->
           03.12.1981
           02.04.1981
           17.12.1980
        30 03.12.1981
           28.09.1981    -->
           08.09.1981
           01.05.1981
           22.02.1981
           20.02.1981

14 rows selected.

So:

SQL> with temp as
  2    (select deptno, hiredate,
  3       rank() over (partition by deptno order by hiredate desc) rnk
  4     from emp
  5    )
  6  select deptno, hiredate
  7  from temp
  8  where rnk = 2;

    DEPTNO HIREDATE
---------- ----------
        10 17.11.1981
        20 09.12.1982
        30 28.09.1981

SQL>

As of "dynamically passed list of values", in Oracle (as you use Oracle SQL Developer, I (maybe wrongly) presume you actually use that database) you'd split that list of values into rows (that's what subquery in lines #5 - 8 does) and use it in IN clause:

SQL> with temp as
  2    (select deptno, hiredate,
  3       rank() over (partition by deptno order by hiredate desc) rnk
  4     from emp
  5     where deptno in (select regexp_substr('&&par_deptno', '[^,] ', 1, level)
  6                      from dual
  7                      connect by level <= regexp_count('&&par_deptno', ',')   1
  8                     )
  9    )
 10  select deptno, hiredate
 11  from temp
 12  where rnk = 2;
Enter value for par_deptno: 10,30

    DEPTNO HIREDATE
---------- ----------
        10 17.11.1981
        30 28.09.1981

SQL>

In SQL Developer, you'd substitute '&&par_deptno' with :par_deptno (i.e. change substitution for bind variable).

CodePudding user response：

You can use LIKE to compare to a string of comma-delimited numbers and the DENSE_RANK analytic function to find the second highest:

SELECT d.customer_id,
       ( SELECT end_date
         FROM   (
           SELECT end_date,
                  DENSE_RANK() OVER (ORDER BY end_date DESC) AS rnk
           FROM   contract g
           WHERE  ',' || :your_list_of_customers || ',' LIKE '%,' || customer_id || ',%'
           AND    g.customer_id = d.customer_id
         )
         WHERE rnk = 2
         AND   ROWNUM = 1
       ) AS END_DATE
FROM   customer_vw d
GROUP BY
       d.customer_id;

Note: Do not use RANK as if there are two (or more) rows tied for first then RANK would return the ranks 1, 1 and 3 for joint 1st and 3rd and there would be no second place. DENSE_RANK solves this by returning 1, 1, 2 in the previous example.