For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Hi, when I run the runInstaller I get this error : [INS-08101] Unexpected error while executing the action at state: 'supportedOSCheck' Does that mean that you cannot install 19c on linux 8.3 ? Regards Jean-Yves
Please show me with an example how coalesce will return the information I need which is; if any of the list members is NULL
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 Connected as hr SQL> SELECT coalesce('x', 'y', '', 'z', NULL) "first-NON-null-expr-in-list" 2 FROM dual 3 / first-NON-null-expr-in-list --------------------------- x
Best regards
I don't think coalesce is what you need, but maybe I'm missing something here.
You can use some nested nvl2 expression, but why doing it complicated. As s.rajaram says, using the or-operator will do just fine.
SQL> create table mytable 2 as 3 select 1 b, 1 c, 1 d, 1 e, 1 f, 1 g, 1 h from dual union all 4 select 1, 1, 1, 1, null, 1, 1 from dual union all 5 select null, null, null, null, null, null, null from dual union all 6 select 1, null, 1, null, 1, null, 1 from dual 7 / Tabel is aangemaakt. SQL> select b, c, d, e, f, g, h 2 , coalesce(b,c,d,e,f,g,h) 3 , nvl2(b, nvl2(c, nvl2(d, nvl2(e, nvl2(f, nvl2(g, h, null), null), null), null), null), null) anyone_null 4 from mytable 5 / B C D E F G H COALESCE(B,C,D,E,F,G,H) ANYONE_NULL -- -- -- -- -- -- -- ----------------------- ----------- 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 4 rijen zijn geselecteerd. SQL> select b, c, d, e, f, g, h 2 from mytable 3 where nvl2(b, nvl2(c, nvl2(d, nvl2(e, nvl2(f, nvl2(g, h, null), null), null), null), null), null) is null 4 / B C D E F G H -- -- -- -- -- -- -- 1 1 1 1 1 1 1 1 1 1 3 rijen zijn geselecteerd. SQL> select b, c, d, e, f, g, h 2 from mytable 3 where b is null 4 or c is null 5 or d is null 6 or e is null 7 or f is null 8 or g is null 9 or h is null 10 / B C D E F G H -- -- -- -- -- -- -- 1 1 1 1 1 1 1 1 1 1 3 rijen zijn geselecteerd.
Regards, Rob.
select * from emp where exists (select null from table (sys.dbms_debug_vc2coll (empno, comm, sal)) where column_value is null)
Brilliant, michaels!
SQL> select b, c, d, e, f, g, h 2 from mytable 3 where exists 4 ( select 'a null value' 5 from table(sys.dbms_debug_vc2coll (b, c, d, e, f, g, h)) 6 where column_value is null 7 ) 8 / B C D E F G H -- -- -- -- -- -- -- 1 1 1 1 1 1 1 1 1 1 3 rijen zijn geselecteerd.
It has already been proposed but....
Where do you want to find something quicker than this
SELECT * FROM T WHERE a IS NULL OR b IS NULL OR c IS NULL ... ... OR z IS NULL
Bye Alessandro
decode(null, a,0, b,0, c,0, ...) = 0
a+b+c+... is null
SQL> create table nulltest (x int, y int, z int); Table created. SQL> insert into nulltest values (1,2,3); 1 row created. SQL> insert into nulltest values (1,2,null); 1 row created. SQL> insert into nulltest values (1, null,null); 1 row created. SQL> insert into nulltest values (1, null,2); 1 row created. SQL> select * from nulltest 2 where x+y+z is null; X Y Z ---------- ---------- ---------- 1 2 1 1 2
select * from emp where lnnvl(sys.dbms_debug_vc2coll() not member of (sys.dbms_debug_vc2coll(empno, comm, sal)))
Based on michaels suggestion to treat the column list as a collection - an alternative to the subquery might be to use MULTISET operators to check for NULLs.
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production SQL> SELECT b, c, d, e, f, g, h 2 FROM mytable 3 WHERE sys.dbms_debug_vc2coll (NULL) MULTISET EXCEPT 4 sys.dbms_debug_vc2coll (b, c, d, e, f, g, h) IS EMPTY; B C D E F G H ---------- ---------- ---------- ---------- ---------- ---------- ---------- 1 1 1 1 1 1 1 1 1 1 SQL> SELECT b, c, d, e, f, g, h 2 FROM mytable 3 WHERE sys.dbms_debug_vc2coll (NULL) MULTISET INTERSECT 4 sys.dbms_debug_vc2coll (b, c, d, e, f, g, h) IS NOT EMPTY; B C D E F G H ---------- ---------- ---------- ---------- ---------- ---------- ---------- 1 1 1 1 1 1 1 1 1 1 SQL>
select x.* from ( select 1 b, 2 c, 1 d, 1 e, 1 f, 1 g, 1 h from dual union all select 1, 1, 1, 1, null, 1, 1 from dual union all select 1, 1, 1, 1, 1, 1, 1 from dual union all select null, null, null, null, null, null, null from dual union all select 1, null, 1, null, 1, null, 1 from dual ) x where case when ( b, c, d, e, f, g, h ) = all ( ( b, c, d, e, f, g, h ) ) then 1 end is null
WHERE NULL IN (b,c,d,e,f,g,h)
SQL> with WorkView as (select 1 b, 2 c, 1 d, 1 e, 1 f, 1 g, 1 h from dual 2 union all select 1, 1, 1, 1, null, 1, 1 from dual 3 union all select 1, 1, 1, 1, 1, 1, 1 from dual 4 union all select null, null, null, null, null, null, null from dual 5 union all select 1, null, 1, null, 1, null, 1 from dual) 6 select b,c,d,e,f,g,h, 7 case when greatest(b, c, d, e, f, g, h) is null then 1 else 0 end as "null1", 8 case when Least(b, c, d, e, f, g, h) is null then 1 else 0 end as "null2" 9 from WorkView; B C D E F G H null1 null2 ---- ---- ---- ---- ---- ---- ---- ----- ----- 1 2 1 1 1 1 1 0 0 1 1 1 1 null 1 1 1 1 1 1 1 1 1 1 1 0 0 null null null null null null null 1 1 1 null 1 null 1 null 1 1 1
On greatest function and Least function, if parameters have null, these functions return null.
Thank you all for your interests and time,
JS1 : where x+y+z is null;
If the columns are not numeric you get "ORA-01722: invalid number" error
Rob van Wijk : Then the question will boil down to: is one full table scan a problem in your case? If not, the "several or" approach looks sufficient. If it is a problem, then you might need a function based index on the nvl2 expression. Rob van Wijk : But no matter how brilliant, or how fun-to-write these solutions are, I cannot think of a valid reason in terms of performance and future maintenance, why not to write the "several or" variant. Alessandro Rossi : Where do you want to find something quicker than this
I thought there could be an Analytics alternative versus Generic SQL for this need but upto now yes or method seems to be the best and since this is a daily report on an oltp(update, insert intensive) table function based indexing is not prefered.
best regards.
create table demo_table nologging as select * from dba_source union all select * from dba_source union all select * from dba_source / update demo_table set owner = NULL where mod(line, 43) = 0 ; update demo_table set text = NULL where mod(line, 11) = 0 ; update demo_table set type = NULL where mod(line, 93) = 0 ; analyze table demo_table compute statistics ; select num_rows from user_tables where table_name = 'DEMO_TABLE' ; NUM_ROWS ---------- 462099 set timing on set autotrace traceonly statistics select count(*) from demo_table where nvl2(owner, nvl2(name, nvl2(type, nvl2(line, text, null), null), null), null) is null / -- each scenerio have the same consistent gets amount - 29903 -- avg. of 5 runs is .37 select count(*) from demo_table where owner is null or name is null or type is null or line is null or text is null / -- avg. of 5 runs is .17 select count(*) from demo_table where exists ( select 'a null value' from table(sys.dbms_debug_vc2coll (owner, name, type, line, text)) where column_value is null ) / -- avg. of 5 runs is .87 select count(*) from ( select case when greatest(owner, name, type, line, text) is null then 1 else 0 end as x, case when least(owner, name, type, line, text) is null then 1 else 0 end as y from demo_table ) where x is null or y is null / -- avg. of 5 runs is .65 set autot off