Deleting records with either null or zero. 2

[EdRev]

My record has 12 columns for the months (jan-dec)
if I want to delete all records that have either 0 or null values on all 12 columns, is this how I do it:

delete FROM table WHERE not(Nvl(Jan,0) != 0
or Nvl(Feb,0) != 0
or Nvl(Mar,0) != 0
or Nvl(Apr,0) != 0
or Nvl(May,0) != 0
or Nvl(Jun,0) != 0
or Nvl(Jul,0) != 0
or Nvl(Aug,0) != 0
or Nvl(Sep,0) != 0
or Nvl(Oct,0) != 0
or Nvl(Nov,0) != 0
or Nvl(Dec,0) != 0)

Any help will be greatly appreciated.
/

 

[jee]

Why the double negative? Why not

delete FROM table
WHERE Nvl(Jan,0) = 0
and Nvl(Feb,0) = 0
and Nvl(Mar,0) = 0
and Nvl(Apr,0) = 0
and Nvl(May,0) = 0
and Nvl(Jun,0) = 0
and Nvl(Jul,0) = 0
and Nvl(Aug,0) = 0
and Nvl(Sep,0) = 0
and Nvl(Oct,0) = 0
and Nvl(Nov,0) = 0
and Nvl(Dec,0) = 0

 

[EdRev]

Because when I did:
select * FROM table
WHERE Nvl(Jan,0) = 0
and Nvl(Feb,0) = 0
and Nvl(Mar,0) = 0
and Nvl(Apr,0) = 0
and Nvl(May,0) = 0
and Nvl(Jun,0) = 0
and Nvl(Jul,0) = 0
and Nvl(Aug,0) = 0
and Nvl(Sep,0) = 0
and Nvl(Oct,0) = 0
and Nvl(Nov,0) = 0
and Nvl(Dec,0) = 0

It selected records with numeric value (non-zero) on any of Jan-dec column

 

[SantaMufasa]

Jee, I believe it will evaluate much quicker with this code:
delete FROM table
WHERE Jan||Feb||Mar||Apr||May||Jun||Jul||Aug||Sep||Oct||
Nov||Dec = 0;

Let me know.

Dave Hunt
(Dave@Dasages.com)
801-733-5333

 

[EdRev]

Sorry jee, I did not notice that you removed the !.
I tried it and it worked. Thanks.

SantaMufasa, does your code evaluate to this:

delete from table where sum(Jan - DEc) = 0?
because if it does, it wouldn't work for me because some months offset each other and I wouldn't want to delete them.

 

[SantaMufasa]

EdRev,

My code just strings together the contents of your 12 columns, then compares the resulting string's algebraic value to zero. This seems to meet your original specifications: "...to delete all records that have either 0 or null values on all 12 columns." But, no, it is not quite the same as "...sum(Jan - DEc) = 0".

As an example, let's assume your 12 columns are each NULL except Feb, Mar, and Sep, which each contain '0'. The concatenation results in '000' (since NULLs consume no space). The algebraic converstion of '000' results in zero, which equals zero, thus deleting that record. If, however, Mar contains a value of '1', then the concatenation results in '010', which algebraic value is '10', which does not equal zero, thus that row remains.

I'm not sure what you mean by, "...some months offset each other and I wouldn't want to delete them."

I do have a tiny refinement to my earlier code to handle the case where each of the 12 columns contain NULL:

delete FROM table
WHERE nvl(Jan||Feb||Mar||Apr||May||Jun||Jul||Aug||Sep||Oct||
Nov||Dec,0) = 0;

Please advise what you think,
Dave Hunt

 

[HereistheAnswer]

SantaMufasa, I think it is more appropriate:

delete FROM table
WHERE to_number(Jan||Feb||Mar||Apr||May||Jun||Jul||Aug||Sep||Oct||
Nov||Dec,0) = 0;

What do you say?

 

[SantaMufasa]

HereIs...
This is the order of events for evaluating the "WHERE" in our two code examples:
(My code) "...WHERE nvl(Jan||Feb||Mar||Apr||May||Jun||Jul||Aug||Sep||Oct||
Nov||Dec,0) = 0;"

1) Concatenate 12 expressions, yielding character string.
2) Compare Step 1's character string to NULL.
a) if NULL, return numeric 0
b) if NOT NULL, return concatenated character string
3) Convert (implicitly) results of Step 2 TO_NUMBER.
4) Compare TO_NUMBER results of Step 3 to 0.

(Your code) "...WHERE to_number(Jan||Feb||Mar||Apr||May||Jun||Jul||Aug||Sep||Oct||
Nov||Dec,0) = 0;"

1) Concatenate 12 expressions, yielding character string.
2) Convert (explicitly) results of Step 1 TO_NUMBER.
3) Compare TO_NUMBER results of Step 2 to 0.

Because of Oracle's implicit mode conversions, our code behaves similarly; but your code still fails when all 12 original expressions are NULL. If you change your code to read:

"WHERE nvl(to_number(Jan||Feb||Mar||Apr||May||Jun||Jul||Aug||Sep||Oct||
Nov||Dec,0),0) = 0;"

...then it works.

Dave

 

[HereistheAnswer]

yeah that's right Santa....I thought after writing that, if all the column value contains null, then my code will fail.
Thank u Santa.