Обсуждение: Query Which is not supposted to list NULLS is listing NULLS also
Hi All,
MyTable Structrue is
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 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
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: > > > > 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 >
Вложения
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:
> 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.
> >
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
> > > 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
>