I want to update the ColStatusB of Table B, only if the IDs in both
table matches and the colCheckC of TableC is of a particular type, say
'Out of Stock'. I have written this query and it does not seem to work.

UPDATE TABLEB SET ColStatusB = 'Updated' WHERE ColIDB = '' AND EXISTS
SELECT colCheckC FROM TABLEC WHERE ColIDC = '' AND colCheckC NOT IN
('',''))

These are the Table Structures of 2 tables,

TableB

ColIDB
ColStatusB

TableC

ColIDC
colCheckC

Thank you in advance,

Scotty.

On Thu, 27 Oct 2005 18:58:26 -0700, Scott wrote:

> I want to update the ColStatusB of Table B, only if the IDs in both
> table matches and the colCheckC of TableC is of a particular type, say
> 'Out of Stock'. I have written this query and it does not seem to work.
>
> UPDATE TABLEB SET ColStatusB = 'Updated' WHERE ColIDB = '' AND EXISTS
> SELECT colCheckC FROM TABLEC WHERE ColIDC = '' AND colCheckC NOT IN
> ('',''))
>
> These are the Table Structures of 2 tables,
>

Scotty, you shouldn't use comparison with '', you should compare with the
NULL value, is "colidc is null" and "colcheckc is not null". Of course,
such comparisons cannot use normal B-tree indexes. It's all in the manuals,
which are on http://tahiti.oracle.com. If it is hard for you to read
manuals, there is a great list where your questions can get answered. Look
into the thread named "Beginners list" for details.

--
http://www.mgogala.com

Hehe, he is hitting kind of bug in Oracle, having a little rant now ...
.. Empty and null are treated as same kind of coffee in SQL statements
but not in PL/SQL.

An empty glass of water is still a glass. Try that in SQL and will tell
you that:

INSERT '' INTO ....

will never retrieve SELECT ..... FROM ... WHERE ... = ''

In my eyes that is a bug. OK, try WHERE NVL(....,'') = '' or WHERE
NVL(.....'x') =' x'. Now this little bugger will force a full table
scan on say an highly selective value of a column. NULL is not EMPTY,
but treated the as same one time and not the other time.

Try this in PL/SQL (sorry to post some beginner examples)
......
v_var VARCHAR2(200);
BEGIN
.....
v_var := v_var || some_text;
.....
END;



but


......
v_var VARCHAR2(200);
BEGIN
.....
v_var := '';
v_var := v_var || some_text;
.....
END;

OK go back to SQL

SELECT a.something, 'Hello'|| b.something ||' world'
FROM a, b
WHERE a = b(+)
AND ....

or

SELECT a.something, 'Hello'|| NVL(b.something,'') ||' world'
FROM a, b
WHERE a = b(+)
AND ....

Sometimes that can be very tricky, especially when there are non
printable characters involved. The whole design comparision between
empty and null is flawed and inconsinstant.

Take a web page for example

www.mysite.com/pls/webesite?var_in=

In PLS/SQL:
PROCEDURE webesite(var_in VARCHAR2 DEFAULT NULL)
,,,,
IF var_in IS NULL THEN
HTP.P('HELLO WORLD');
END IF;

No joy, it will not do it.

Now:
IF NVL(LENGTH(var_in),0) = 0 THEN
HTP.P('HELLO WORLD');
END IF;

will always work, no matter if that variable is passed empty or not at
all.

Back to SQL
SELECT ...
FROM ...
WHERE NVL(col,'XXXXXXXXX') = 'XXXXXXXXX'

Now create a function based index
CREATE INDEX tab_fb_col_idx ON tab
( NVL(col,'XXXXXXXXX'))


Viola, instead of having a full table scan on tab it will pick up that
index straight away, if desired is another question.

.... WHERE col IS NULL => WHERE NVL(col,'XXXXXXXXX') = 'XXXXXXXXX'

.... WHERE col IS NOT NULL =>WHERE NVL(col,'XXXXXXXXX') !=
'XXXXXXXXX'

In applications there are a some cases (not many) where an empty string
is passed as an additional parameter, so it would be nice that Oracle
would be clever enough to see an EMPTY string as NULL and treat them
like this. It's a hassle to test that a parameter is empty and than to
use IS NULL, IS NOT NULL instead of a simple "a = :b", "a != :b" for
all cases.

Comparing NULL = NULL, NULL <> NULL, NULL > NULL, etc. will always
result in false (another paradox for the first case), however all empty
columns are NULL even when using UPDATE tab SET col = '', but on the
other hand it makes a big differene in PL/SQL. It's just very confusing
and easily to fall for it, even having +++ years experience, still
sneaking in or forgetting about, bugger.

I hope above samples clear it up a little bit.

<cybotto@yahoo.com> a écrit dans le message de news: 1130653256.986100.151560@g43g2000cwa.googlegroups. com...
|
| Comparing NULL = NULL, NULL <> NULL, NULL > NULL, etc. will always
| result in false ...
|

This is not true, these conditions return UNKNOWN and not FALSE.
Oracle works on a trivalue logic.

SQL> begin
2 if '' = '' then dbms_output.put_line('equal is TRUE');
3 elsif not ('' = '') then dbms_output.put_line('equal is FALSE');
4 else dbms_output.put_line('equal is NEITHER true nor false');
5 end if;
6 end;
7 /
equal is NEITHER true nor false

PL/SQL procedure successfully completed.

A select returns rows that returns TRUE for the where clause.
All rows that return FALSE or UNKNOWN are discarded.
This is a litlle confusing but logic.

Regards
Michel Cadot

On Sat, 29 Oct 2005 23:20:57 -0700, cybotto wrote:

>
> I hope above samples clear it up a little bit.

I know about the issue and the OP wasn't using PL/SQL. That, however,
is not a bug as it is documented in several Metalink notes. I do agree
with you that it is inconsistent and plain stupid. Oh, well....

--
http://www.mgogala.com

Michel Cadot wrote:
> <cybotto@yahoo.com> a écrit dans le message de news: 1130653256.986100.151560@g43g2000cwa.googlegroups. com...
> |
> | Comparing NULL = NULL, NULL <> NULL, NULL > NULL, etc. will always
> | result in false ...
> |
>
> This is not true, these conditions return UNKNOWN and not FALSE.
> Oracle works on a trivalue logic.

Just to supplement your otherwise perfect answer, Oracle SQL Reference
says "In a DECODE function, Oracle considers two nulls to be
equivalent. If expr is null, then Oracle returns the result of the
first search that is also null." I think this is an appropriate example
(from Oracle 9.2.0.1.0):

SQL> select decode(null, null, 'Null equals null in DECODE') from dual;

DECODE(NULL,NULL,'NULLEQUA
--------------------------
Null equals null in DECODE

That's one place I find Oracle treats null differently. Another place,
not quite related, is that MAXVALUE in range partition definition is
greater than null (Ref. J. Lewis "Practical Oracle8i", p.241).

Yong Huang