Case when not returning properly values-CodePudding

I have 3 "case when" selects but i'm having troubles with the last one:

select  
case  
 when :wprod IN (select cod from products where standard = 'N' and disabled_date is null and classif = 1)
  THEN 
   case when :wprod NOT IN 
   (select cod from invoice where seq in 
   (select invoice_prod from invoice_outrosdocto pout where pout.register = :register and 
   pout.disabled_date is null))
    

     -- My problem are below:
      
      THEN  
      CASE WHEN (SELECT * from
      (SELECT selfinvoice FROM invoice WHERE seq =
      (SELECT Max(invoice) FROM invoice WHERE register = :register AND cod = :wprod AND deleted IS NULL))) = 'S'
         THEN 'SA' 
         else 'SB' end


-- How can I compare a column (selfinvoice) from the last entry date Max(invoice)?
-- I tried the selected as shown, it's great when having 'S' or 'N' explicit values, but sometimes
-- it returns 0 rows (nothing) and by this I can't compare this "case when"
-- I've also tried Nvl(selfinvoice, 0) but it didn't worked as well



end  ELSE 'NB' end
else 'NC' end logic
, '  ' value 
from  
dual group by '  '

Is there any other approach to this problem? Seems like I hit a big concrete wall... Thanks in advance.

CodePudding user response：

If I understood it correctly, it is not selfinvoice that requires NVL, but the innermost subquery, this:

   CASE
      WHEN (SELECT *
              FROM (SELECT selfinvoice
                      FROM invoice
                     WHERE seq = (SELECT NVL (MAX (invoice), 'X')     --> here
                                    FROM invoice
                                   WHERE     register = :register
                                         AND cod = :wprod
                                         AND deleted IS NULL))) = 'S'
      THEN
         'SA'
      ELSE
         'SB'
   END

[EDIT]

I don't have your tables so I tried to simulate it on Scott's EMP table.

SQL> select distinct deptno, job from emp order by deptno, job;

    DEPTNO JOB
---------- ---------
        10 CLERK        --> there's no ANALYST in deptno = 10
        10 MANAGER
        10 PRESIDENT
        20 ANALYST      --> ANALYST in deptno = 20
        20 CLERK
        20 MANAGER
        30 CLERK
        30 MANAGER
        30 SALESMAN

9 rows selected.

This is your query:

SQL> SELECT CASE
  2            WHEN (SELECT *
  3                    FROM (SELECT DISTINCT job
  4                            FROM emp
  5                           WHERE job = (SELECT MIN (job)
  6                                          FROM emp
  7                                         WHERE deptno = &par_deptno))) = 'ANALYST'
  8            THEN
  9               'sa'
 10            ELSE
 11               'sb'
 12         END
 13    FROM DUAL;
Enter value for par_deptno: 10      --> for DEPTNO = 10, it returns SB because ANALYST doesn't exist there

CA
--
sb

SQL> /
Enter value for par_deptno: 20     --> for DEPTNO = 20, it returns SA because ANALYST exists there

CA
--
sa

SQL>

You said that the problem is that query that runs SELECT selfinvoice ... doesn't return anything. So let's try it: this is my "selfinvoice" piece of query; as there's no department -1, it returns no rows:

SQL> SELECT DISTINCT job
  2                            FROM emp
  3                           WHERE job = (SELECT MIN (job)
  4                                          FROM emp
  5                                         WHERE deptno = &par_deptno)
  6  /
Enter value for par_deptno: -1

no rows selected

What does then the complete query return?

SQL> SELECT CASE
  2            WHEN (SELECT *
  3                    FROM (SELECT DISTINCT job
  4                            FROM emp
  5                           WHERE job = (SELECT MIN (job)
  6                                          FROM emp
  7                                         WHERE deptno = &par_deptno))) = 'ANALYST'
  8            THEN
  9               'sa'
 10            ELSE
 11               'sb'
 12         END
 13    FROM DUAL;
Enter value for par_deptno: -1

CA
--
sb


SQL>

It returns SB, so ... it actually works, doesn't fail.

If possible, create a SIMPLE test case which illustrates your problem; explain which result you'd want to get out of it.