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

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

From
"shreedhar"
Date:
Hi All,
 
MyTable Structrue is
 

CREATE TABLE tbljobincharge (

jassignedid int4 DEFAULT nextval('"tbljobincharge_jassignedid_seq"'::text) NOT NULL,

projectid int4 NOT NULL,

accountid int4 NOT NULL,

completeddate timestamp,

jobcompleted bool DEFAULT 'f' NOT NULL,

jobcomments text,

satisid int4,

priorityid int4 DEFAULT 0 NOT NULL,

active bool DEFAULT 't' NOT NULL,

remarks text,

noofunits int4 DEFAULT 0 NOT NULL,

jobassigneddate timestamp,

assignedby int4 )

But Query

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. Is there any alternative to write a query to list 'not null' result. I am attaching result set of query for you reference.

Thanks And Regards

Sreedhar

 

 

Our greatest happiness in life does not depend on the condition of life in which chance has placed us, but is always the result of good conscience, good health, occupation, and freedom in all just pursuits

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

From
Jean-Christian Imbeault
Date:
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


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

From
"shreedhar"
Date:
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.
>
> HTH,
>
> --
>
> Jean-Christian Imbeault
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

Attachment

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

From
Jean-Christian Imbeault
Date:
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


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

From
"Nigel J. Andrews"
Date:
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.
> >



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

From
Andrew Sullivan
Date:
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


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

From
"Vincent Hikida"
Date:
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
>