Query Which is not supposted to list NULLS is listing NULLS also

shreedhar wrote:

SELECT jassignedid, JobComments FROM tblJobIncharge WHERE JobComments IS
NOT NULL AND projectid >= 50 AND projectid <= 100

Which is not supposed to list NULLS is listing NULLS also.

I can't see anything wrong with your query. Can you give some output to
show what the problem is? The results should not contain and row where
JobComments IS NULL AFAICT.

Maybe I missed something in your question so please give some output.

HTH,

--

Jean-Christian Imbeault

Hello,

I am attaching an output with this mail. I am sure that I am not inserting
'' for jobcomments in any case.

Sreedhar
----- Original Message -----
From: "Jean-Christian Imbeault" <jc@mega-bucks.co.jp>
To: "shreedhar" <shreedhar@lucidindia.net>
Cc: "Postgre General" <pgsql-general@postgresql.org>
Sent: Tuesday, May 27, 2003 1:15 PM
Subject: Re: [GENERAL] Query Which is not supposted to list NULLS is listing

shreedhar wrote:

Hello,

I am attaching an output with this mail. I am sure that I am not

inserting

'' for jobcomments in any case.

A simple check would be:

SELECT jobcomment from tblJobIncharge where id=51 AND jobcomment IS NOT
NULL;

If you get back a row then jobcomment is not null and probably has the
empty string in it ...

To make sure that jobcomment is not '' you can try this query:

SELECT jassignedid, ascii(JobComments) FROM tblJobIncharge WHERE
JobComments IS NOT NULL AND projectid >= 50 AND projectid <= 100

If jobcomment is '' then you will get '0' in the job comment field, if
it is really null then you will get '' (i.e. nothing).

So if you get a 0 it means that you have job comment fields with the
empty string '' in them, they are not null ...

HTH,

Jean-Christian Imbeault

Try seeing what happens with:

SELECT jassignedid, coalesce(JobComments,'X')
FROM tblJobIncharge
WHERE JobComments IS NOT NULL
AND projectid >= 50 AND projectid <= 100

That'll show you any results that in that column that are really null as 'X'.

Or even:

SELECT jassignedid, '>>' || JobComments || '<<'
FROM tblJobIncharge
WHERE JobComments IS NOT NULL
AND projectid >= 50 AND projectid <= 100

Which not only will show you real nulls (since null in a || operation gives
null) but also indicate the string between the '>>' and '<<'.

--
Nigel J. Andrews

On Tue, 27 May 2003, shreedhar wrote:

On Tue, May 27, 2003 at 02:13:55PM +0530, shreedhar wrote:

Hello,

I am attaching an output with this mail. I am sure that I am not inserting
'' for jobcomments in any case.

Please note that '' is not null. It's an empty string. Oracle gets
this wrong, so if you're used to the Oracle syntax, that might be
your problem.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

You may have spaces instead of nulls in the column. Try

SELECT jassignedid, JobComments, length(JobComments)
FROM tblJobIncharge
WHERE JobComments IS NOT NULL
AND projectid >= 50 AND projectid <= 100;

The length of the column will tell how many spaces there are if there are
any.

Actually I expected the following WHERE clause to work which I believe does
work in Oracle. This did not work.

WHERE RTRIM(JobComments) IS NOT NULL

If you have blanks you could try the following:

UPDATE tblJobIncharge
SET JobComments = NULL
WHERE LENGTH(RTRIM(JobComments)) = 0;

Vincent Hikida,
Member of Technical Staff - Urbana Software, Inc.
"A Personalized Learning Experience"

www.UrbanaSoft.com

----- Original Message -----
From: "Nigel J. Andrews" <nandrews@investsystems.co.uk>
To: "shreedhar" <shreedhar@lucidindia.net>
Cc: "Jean-Christian Imbeault" <jc@mega-bucks.co.jp>; "Postgre General"
<pgsql-general@postgresql.org>
Sent: Tuesday, May 27, 2003 2:31 AM
Subject: Re: [GENERAL] Query Which is not supposted to list NULLS is listing

Try seeing what happens with:

SELECT jassignedid, coalesce(JobComments,'X')
FROM tblJobIncharge
WHERE JobComments IS NOT NULL
AND projectid >= 50 AND projectid <= 100

That'll show you any results that in that column that are really null as

'X'.

Or even:

SELECT jassignedid, '>>' || JobComments || '<<'
FROM tblJobIncharge
WHERE JobComments IS NOT NULL
AND projectid >= 50 AND projectid <= 100

Which not only will show you real nulls (since null in a || operation

gives

null) but also indicate the string between the '>>' and '<<'.

--
Nigel J. Andrews

On Tue, 27 May 2003, shreedhar wrote:

Hello,

I am attaching an output with this mail. I am sure that I am not

inserting

'' for jobcomments in any case.

Sreedhar
----- Original Message -----
From: "Jean-Christian Imbeault" <jc@mega-bucks.co.jp>
To: "shreedhar" <shreedhar@lucidindia.net>
Cc: "Postgre General" <pgsql-general@postgresql.org>
Sent: Tuesday, May 27, 2003 1:15 PM
Subject: Re: [GENERAL] Query Which is not supposted to list NULLS is

listing

shreedhar wrote:

SELECT jassignedid, JobComments FROM tblJobIncharge WHERE

JobComments IS

NOT NULL AND projectid >= 50 AND projectid <= 100

Which is not supposed to list NULLS is listing NULLS also.

I can't see anything wrong with your query. Can you give some output

to