Re: Query Which is not supposted to list NULLS is listing - Mailing list pgsql-general

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
> > > 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
>