Re: Query Which is not supposted to list NULLS is listing

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(at)investsystems(dot)co(dot)uk>
To: "shreedhar" <shreedhar(at)lucidindia(dot)net>
Cc: "Jean-Christian Imbeault" <jc(at)mega-bucks(dot)co(dot)jp>; "Postgre General"
<pgsql-general(at)postgresql(dot)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(at)mega-bucks(dot)co(dot)jp>
> > To: "shreedhar" <shreedhar(at)lucidindia(dot)net>
> > Cc: "Postgre General" <pgsql-general(at)postgresql(dot)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
> > > 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.
> > >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>