Suppress Wanring message

[Doug Ly]

Hi,
When I run this query using WinSQL to connect to a DB2 database, it gave me
the warning:
Error: SQLSTATE 01003: Null values were eliminated from the argument of a
column function.

(State:01003, Native Code: 0)
Is there anyway to suppress this message in my report? Thanks
By the way, could anybody tell me some popular DB2 clients to make queries
Thanks

[Rhino]

"Doug Ly" <de******@dev.null> wrote in message
news:c8*****************@newssvr22.news.prodigy.co m...

Hi,
When I run this query using WinSQL to connect to a DB2 database, it gave me the warning:
Error: SQLSTATE 01003: Null values were eliminated from the argument of a
column function.

(State:01003, Native Code: 0)
Is there anyway to suppress this message in my report? Thanks
As far as I know, you can't suppress this message directly with a command
like "suppress null warning" or something like that. You can prevent the
message indirectly though.

This warning occurs because you applied a column function like avg() to a
numeric column, like salary, in an expression like 'avg(salary)', e.g.
select avg(salary) from employee. The column functions, which include avg(),
sum(), min(), and max() (and some others) are all designed to ignore nulls
because nulls added to, subtracted from, divided by or multiplied by another
value always equals null, e.g. 57 + null = null; 57 - null = null; 57 *
null; and 57/null = null. If the column function didn't ignore nulls, then
the result of the column function would always be null if there was any null
in the column, even if all but one of the values were non-null. Therefore
the average of a million test scores would be null, even if 999,999 of the
values were non-null.

To prevent the warning from occurring, you need to do one or both of these
things:
1. prevent any nulls from being evaluated by the column function
2. treat any null that is evaluated by the column function as if it were a
non-null value

To prevent nulls from being evaluated by a column function, use predicates
that screen out the null values, e.g. select avg(salary) from employee where
salary is not null. This is the advice that Mark A gave you in your earlier
post on this same subject.

To treat a null value as if it was a non-null value, such as zero, use the
coalesce() scalar function within your avg() function, e.g. select
avg(coalesce(salary, 0)) from employee. This will tell DB2 to treat every
null that the avg() function finds as if it were a zero.

Be cautious about the consequences of using the coalesce function! While it
should eliminate the warning message, it will also treat nulls as zeros and
you may not want that in every case. For example, if the column that you
average contains test scores, treating nulls as zeroes via the coalesce()
function will have the effect of skewing the average score downwards.
Consider two cases:

Case 1. Several students failed to write the exam because they are ill from
food poisoning and couldn't attend the exam.

Case 2. Several students were partying and failed to study; they wrote the
exam but got every question wrong.

I think most people would prefer to give the students in case 1 a test score
of null, to indicate that they hadn't written the exam, and EXCLUDE them in
the calculation of the class average. I think most people would prefer to
give the students in Case 2 a zero and INCLUDE their marks in the
calculation of the class average.

If you use coalesce() you will treat both Case 1 and Case 2 students as if
they were Case 2 students and ALL of them will be treated as if they got
zero on the exam; the Case 1 students will pull the class average further
down than it would have been if Case 1 students were simply ignored. If the
coalesce() function wasn't used and the warning was simply noted and
ignored, the class average would be calculated correctly and the Case 1
students would NOT pull the average down.

Now you can make an informed decision about which approach to use.

By the way, could anybody tell me some popular DB2 clients to make queries

You haven't said which platform you are on. QMF has been popular on MVS and
OS/390 for many years. There is a workstation version of QMF but I don't
recall which operating systems it works on. I'm sure there are many other
popular programs for writing queries against DB2 databases. You could even
write your own with languages like Java.

Rhino

[Rhino]

"Doug Ly" <de******@dev.null> wrote in message
news:c8*****************@newssvr22.news.prodigy.co m...

Hi,
When I run this query using WinSQL to connect to a DB2 database, it gave me the warning:
Error: SQLSTATE 01003: Null values were eliminated from the argument of a
column function.

(State:01003, Native Code: 0)
Is there anyway to suppress this message in my report? Thanks
As far as I know, you can't suppress this message directly with a command
like "suppress null warning" or something like that. You can prevent the
message indirectly though.

This warning occurs because you applied a column function like avg() to a
numeric column, like salary, in an expression like 'avg(salary)', e.g.
select avg(salary) from employee. The column functions, which include avg(),
sum(), min(), and max() (and some others) are all designed to ignore nulls
because nulls added to, subtracted from, divided by or multiplied by another
value always equals null, e.g. 57 + null = null; 57 - null = null; 57 *
null; and 57/null = null. If the column function didn't ignore nulls, then
the result of the column function would always be null if there was any null
in the column, even if all but one of the values were non-null. Therefore
the average of a million test scores would be null, even if 999,999 of the
values were non-null.

To prevent the warning from occurring, you need to do one or both of these
things:
1. prevent any nulls from being evaluated by the column function
2. treat any null that is evaluated by the column function as if it were a
non-null value

To prevent nulls from being evaluated by a column function, use predicates
that screen out the null values, e.g. select avg(salary) from employee where
salary is not null. This is the advice that Mark A gave you in your earlier
post on this same subject.

To treat a null value as if it was a non-null value, such as zero, use the
coalesce() scalar function within your avg() function, e.g. select
avg(coalesce(salary, 0)) from employee. This will tell DB2 to treat every
null that the avg() function finds as if it were a zero.

Be cautious about the consequences of using the coalesce function! While it
should eliminate the warning message, it will also treat nulls as zeros and
you may not want that in every case. For example, if the column that you
average contains test scores, treating nulls as zeroes via the coalesce()
function will have the effect of skewing the average score downwards.
Consider two cases:

Case 1. Several students failed to write the exam because they are ill from
food poisoning and couldn't attend the exam.

Case 2. Several students were partying and failed to study; they wrote the
exam but got every question wrong.

I think most people would prefer to give the students in case 1 a test score
of null, to indicate that they hadn't written the exam, and EXCLUDE them in
the calculation of the class average. I think most people would prefer to
give the students in Case 2 a zero and INCLUDE their marks in the
calculation of the class average.

If you use coalesce() you will treat both Case 1 and Case 2 students as if
they were Case 2 students and ALL of them will be treated as if they got
zero on the exam; the Case 1 students will pull the class average further
down than it would have been if Case 1 students were simply ignored. If the
coalesce() function wasn't used and the warning was simply noted and
ignored, the class average would be calculated correctly and the Case 1
students would NOT pull the average down.

Now you can make an informed decision about which approach to use.

By the way, could anybody tell me some popular DB2 clients to make queries

You haven't said which platform you are on. QMF has been popular on MVS and
OS/390 for many years. There is a workstation version of QMF but I don't
recall which operating systems it works on. I'm sure there are many other
popular programs for writing queries against DB2 databases. You could even
write your own with languages like Java.

Rhino

[Gert van der Kooij]

In article <c8*****************@newssvr22.news.prodigy.com> , Doug Ly
(de******@dev.null) says...

Hi,
When I run this query using WinSQL to connect to a DB2 database, it gave me
the warning:
Error: SQLSTATE 01003: Null values were eliminated from the argument of a
column function.

(State:01003, Native Code: 0)
Is there anyway to suppress this message in my report? Thanks
By the way, could anybody tell me some popular DB2 clients to make queries
Thanks

You could try this (wrapped) command from the commandline:

update CLI CONFIGURATION FOR SECTION <DBNAME> using
IGNOREWARNLIST="'01003'"
To display the current settings:

db2 get CLI CONFIGURATION

Hope this helps

[Doug Ly]

Man, what a detail help... I appreciate a lot.
I'm running Windows XP professional. And I'm using WinSQL to make SQL query.
But it is too limited. I've also tried the IBM DB2 client and it crashed
like hell.

Thanks

[Rhino]

"Gert van der Kooij" <ge**@invalid.nl> wrote in message
news:MP************************@news.xs4all.nl...

In article <c8*****************@newssvr22.news.prodigy.com> , Doug Ly
(de******@dev.null) says...

Hi,
When I run this query using WinSQL to connect to a DB2 database, it gave me the warning:
Error: SQLSTATE 01003: Null values were eliminated from the argument of a column function.

(State:01003, Native Code: 0)
Is there anyway to suppress this message in my report? Thanks
By the way, could anybody tell me some popular DB2 clients to make queries Thanks

You could try this (wrapped) command from the commandline:

update CLI CONFIGURATION FOR SECTION <DBNAME> using
IGNOREWARNLIST="'01003'"

I wasn't aware of this command; I was obviously wrong when I said you can't
suppress the message.

Rhino

[Gert van der Kooij]

In article <bb*********************@news20.bellglobal.com>, Rhino
(rh****@NOSPAM.sympatico.ca) says...

You could try this (wrapped) command from the commandline:

update CLI CONFIGURATION FOR SECTION <DBNAME> using
IGNOREWARNLIST="'01003'"

I wasn't aware of this command; I was obviously wrong when I said you can't
suppress the message.

This command isn't going to work either, the '=' isn't supposed to be
there :)

[Rhino]

"Doug Ly" <de******@dev.null> wrote in message
news:Ok*******************@newssvr22.news.prodigy. com...

Man, what a detail help... I appreciate a lot.
Sorry, I get a bit long-winded sometimes ;-) I'm glad my reply helped you
understand the situation.
I'm running Windows XP professional. And I'm using WinSQL to make SQL query. But it is too limited. I've also tried the IBM DB2 client and it crashed
like hell.
Which type of DB2 are you using? Personal Edition? Is your database also on
Windows XP or is your client on XP while your database is on Linux or
mainframe or some other platform?

Which version of DB2 are you using? V8.1 is the current version for Windows.

What DB2 client did you use? Do you mean the Command Center? What errors did
you get from your client? The Command Center usually works pretty well and
shouldn't break very often. Is it possible that you've made a mistake
installing it? It sounds like you are fairly new to DB2 and mistakes are
common for newbies. If you post as much information about your errors as
possible, people here can probably help you.

Your choice of a client really depends mostly on who is going to use the
client, what they're going to try to do with it, and how much money you're
willing to spend. You say the WinSQL is "too limited"; what is it not doing
that you need? That might give people an idea of what client will meet your
needs. But I'm not sure you really need another client; the stuff that comes
with DB2 is pretty good already.

Rhino

Thanks

[Doug L]

Hi there (Rhino),

The DB2 database we're using right now is UDB V8.0 I believe. It resides
on an IBM RISC 6000 server. I mainly use Lotus Approach ( if you ever
heard of it :-) ) to make reports. Sometime it is very difficult to make
customized reports in Approach so I have to use winSQL which supports SQL
query language. Those kinds of reports normally require the grant total or
average or summary function, grouping, sorting that SQL query is the best
fit.

But when I go along with WinSQL, I know it only supports ODBC in general.
To get the full strength of DB2, I guess your suggestion of using the
IBM DB2 client is correct.
Now, I reckon there are 2 versions of DB2 clients posted on IBM website.
one says "UDB Personal Developer version" and the other one just says "UDB
client". I wonder which one should I pick to serve my purpose.

Thanks again!!!

[Rhino]

"Doug L" <dm**@apollo.net> wrote in message
news:pa****************************@apollo.net...

Hi there (Rhino),

The DB2 database we're using right now is UDB V8.0 I believe. It resides
on an IBM RISC 6000 server. I mainly use Lotus Approach ( if you ever
heard of it :-) ) to make reports. Sometime it is very difficult to make
customized reports in Approach so I have to use winSQL which supports SQL
query language. Those kinds of reports normally require the grant total or
average or summary function, grouping, sorting that SQL query is the best
fit.
As it turns out, I *have* heard of Lotus Approach. In fact, I have it
(Release 9) on my computer. I have only used it a very few times though. I
just fired it up and it took me several minutes just to figure out how to
find a DB2 table with it because it had been so long since I last used it.

The Find/sort Assistant in Approach has a fair number of capabilities in
terms of 'WHERE' conditions and sorts but doesn't give you access to the DB2
column and scalar functions. However, I see from the Help that Approach has
many of its own functions that are similar to the DB2 functions. Some of the
Approach functions have the same names as their DB2 counterparts (e.g. Year,
Upper) and some have different names but similar funtionality (e.g.
Approach's combine() is similar to DB2's concat()). Then again, there are
some DB2 functions that don't appear to be in Approach at all (e.g.
Regression functions). Therefore, it may simply be a matter of getting more
familiar with Approach to do what you want to do.

I assume you're an end user of some kind, as opposed to a developer or DBA.
If that's right, is there any possibility of requesting an Approach course
from your training department? Or could you get someone there who is more
familiar with Approach to tutor you or at least answer your questions? If
not, maybe you could find an Approach tutorial somewhere online.

But when I go along with WinSQL, I know it only supports ODBC in general.
To get the full strength of DB2, I guess your suggestion of using the
IBM DB2 client is correct.
Now, I reckon there are 2 versions of DB2 clients posted on IBM website.
one says "UDB Personal Developer version" and the other one just says "UDB
client". I wonder which one should I pick to serve my purpose.
If you find that Approach simply won't do what you need to do, no matter how
fluent you are with it, then it's probably time to try the DB2 client.
Either of the ones you mentioned should work. You said before you were
having problems with the DB2 client so it might be wise to get one of your
DBAs to install it for you, just to make sure that a bad install doesn't
mess you up. Then, you'll need to sit down and learn how to use the Command
Center. Its design is different than Lotus Approach but it shouldn't be too
hard to learn if you get one of the DBAs to sit down with you for a half
hour and show you the basics of setting up your defaults (like the statement
delimiter), connecting to the database, and writing, saving and retrieving a
query. Then, use the SQL Getting Started and SQL Reference manuals that come
with DB2 and learn the elements of DB2 that you need to know.
Thanks again!!!

My pleasure. You obviously want to "expand your horizons" so that you can do
more with DB2 so I want you to get a start in the right direction.

Rhino