Nvl function with multiple row set in oracle

Question

I have a problem with NVL function in oracle. If I send periods variable as null it is working.There is no data in query.I mean NVL function is working properly. If I send periods variable as for example 10 , it gives error like that

single-row-subquery-returns-more-than-one

This code block does not work because select query returns more than one row

Select .......  FROM students st, orders od  
WHERE  st.id IN NVL((select id from students 
             where student_id = 321
     ORDER BY id desc
          FETCH FIRST periods ROWS ONLY),(od.countNo)) 

I tried to use case when in where block but I cannot use it.Do you have any idea?

Answer 1

You would get the same error if you changed IN to =. IN can handle sets of multiple rows from a subquery, but NVL() and = cannot.

If you INSIST on using NVL, change your subquery to return AT MOST 1 row:

-- using periods=1
Select .......  FROM students st, orders od  
WHERE  st.id = NVL((select id from students 
                  where student_id = 321
                  ORDER BY id desc
                  FETCH FIRST 1 ROWS ONLY),(od.countNo)); 

-- using an aggregate function
Select .......  FROM students st, orders od  
WHERE  st.id = NVL((select max(id) from students 
                  where student_id = 321),(od.countNo));

Or if you need multiple rows, rewrite your query to not use NVL:

Select .......  FROM students st, orders od  
WHERE EXISTS (select 1 from students 
               where student_id = 321
                 and st.id = students.id
               ORDER BY students.id desc
               FETCH FIRST periods ROWS ONLY)
      OR st.id = od.countNo;

A modified version of Gordon's answer from yesterday is another example:

with s3 as (
       select id 
       from students 
       where student_id = 321
       ORDER BY id desc
       FETCH FIRST periods ROWS ONLY
      )
Select .......  FROM students st, orders od  
where st.id in (select id from s3) or
      (not exists (select 1 from s3) and st.id = od.countNo);