Least Value But Not Null in Oracle SQL

Least value but not NULL in Oracle SQL

I doubt that's actually your query. Maybe you're doing something more like this?

select least(some_column) from dual

If so, change it to this:

select least(some_column) from dual where some_column is not null

---

Or, if you're doing something more like this, where you can't just use where to filter the set,

select least(expr1,expr2,expr3) from dual

do this:

select least(coalesce(expr1, 12345), coalesce(expr2, 12345), coalesce(expr3, 12345)) from dual

Where 12345 is a value big enough that it would only be chosen if all other expressions are NULL.

Oracle SQL Least Value Not Null

select
    trunc(dateX)-trunc(sysdate) daysTilX,
    trunc(dateY)-trunc(sysdate) daysTilY,
    least(trunc(nvl(dateX, dateY))-trunc(sysdate),trunc(nvl(dateY, dateX))-trunc(sysdate)) leastOfTheTwo
from myTable

Oracle SQL - Select oldest of 3 dates, but ignore NULLs

Here is one way:

SELECT LEAST(COALESCE(DATE_1, DATE_2, DATE_3),
             COALESCE(DATE_2, DATE_1, DATE_3),
             COALESCE(DATE_3, DATE_2, DATE_1)
            )
FROM MYTABLE

Oracle PL/SQL: Return true if column has at least one null value or no data found

Would this do?

SQL> with temp as
  2    (select count(*) cnt
  3     from (select distinct 1 as val
  4              from my_emp
  5              where fk_col = &&par_fk_col
  6                and (   last_name is null
  7                     or email is null
  8                    )
  9           union all
 10           select distinct 2
 11              from my_emp
 12              where fk_col = &&par_fk_col
 13          )
 14    )
 15  select case when cnt = 1 then 'false'
 16              else 'true'
 17         end as result
 18  from temp;

It results in

Enter value for par_fk_col: 1

RESUL
-----
true

SQL> undefine par_fk_col
SQL> /
Enter value for par_fk_col: 2

RESUL
-----
false

SQL> undefine par_fk_col
SQL> /
Enter value for par_fk_col: 3

RESUL
-----
true

SQL> undefine par_fk_col
SQL> /
Enter value for par_fk_col: 700

RESUL
-----
true

SQL>

That's all in one select statement. If you're writing a function, it is probably simpler to check whether fk_col exists in a separate query; if not, return true immediately. If so, then check for other conditions.

(BTW, thank you for test case. It is SO rare that I'm positively surprised!)

---

[EDIT] To "convert" that statement into PL/SQL isn't that difficult. Instead of an anonymous PL/SQL block, I'd suggest a function.

SQL> create or replace function f_test (par_fk_col in my_emp.fk_col%type)
  2    return varchar2
  3  is
  4    retval varchar2(10);
  5  begin
  6    with temp as
  7       (select count(*) cnt
  8        from (select distinct 1 as val
  9                 from my_emp
 10                 where fk_col = par_fk_col
 11                   and (   last_name is null
 12                        or email is null
 13                       )
 14              union all
 15              select distinct 2
 16                 from my_emp
 17                 where fk_col = par_fk_col
 18             )
 19       )
 20     select case when cnt = 1 then 'false'
 21                 else 'true'
 22            end
 23     into retval
 24     from temp;
 25
 26     return retval;
 27  end;
 28  /

Function created.

Testing:

SQL> select f_test(1) from dual;

F_TEST(1)
---------------------------------------
true

SQL> select f_test(2) from dual;

F_TEST(2)
---------------------------------------
false

SQL> select f_test(700) from dual;

F_TEST(700)
---------------------------------------
true

SQL>

Exclude not null and less than condition

I have added the logic to remove those client_no in report 1 in the subquery represented by the alias B. Used that list of client_no in B in a full outer join on client_no with your original table(report 2 with the alias A). This will create NULLs in both A.client_no and B.client_no columns wherever there is a mismatch.

Then added a WHERE B.CLIENT_NO IS NULL which means you will only have client_no that are in A and not in B[FYI if B.client_no is not NULL, that means there is a match with A.client_no and you don't want that client_no in report 3].

I have not made changes at other places in the query.

SELECT A.CLIENT_NO,
    sum(decode(category,'3',decode(nvl(cancel_flag,'N'),'N',1,-2) ,0)) CASH,
    sum(decode(chq_no, null,0, decode(nvl(cancel_flag,'N'),'N',1,-2))) CHQ,
    0 YTD_PURCHASE,
    0 YTD_SALES,
    0  CURRENT_CRLIMIT,
    0 CR_LIMIT 
FROM BOS_M_LEDGER_REC A
FULL OUTER JOIN
(SELECT CLIENT_NO FROM BOS_M_LEDGER_REC WHERE ((CHQ_NO IS NOT NULL AND CHQ_AMT>=50000) or (CATEGORY='3' AND CHQ_AMT>=10000)) GROUP BY CLIENT_NO) B
ON A.CLIENT_NO = B.CLIENT_NO
WHERE B.CLIENT_NO IS NULL
AND ((CHQ_NO IS NOT NULL AND CHQ_AMT<50000) or (CATEGORY='3' AND CHQ_AMT<10000)) 
AND A.CLIENT_NO>=:P_CLIENT_NO_FROM 
AND A.CLIENT_NO <=:P_CLIENT_NO_TO 
AND TRAN_DATE>=:P_FROM_DATE 
AND TRAN_DATE<=:P_TO_DATE 
GROUP BY A.CLIENT_NO;

---

Related Topics