pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedproblems with SELECT query results
5+ messages / 4 participants
[nested] [flat]
* problems with SELECT query results
@ 2007-05-29 15:47 Joshua <joshua@joshuaneil.com>
2007-05-29 16:36 ` Re: problems with SELECT query results Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 5+ messages in thread
From: Joshua @ 2007-05-29 15:47 UTC (permalink / raw)
To: pgsql-sql
Hello,
I am new to this list and have been working with PostgreSQL since January.
Here is my problem, I hope someone here has some experience or can point
me in the right direction. I am writing the following query for a C#
program I am writing:
SELECT 'PV.LINEITEM:' || partnum || ',' || round(onhand) || ',' ||
round(qm5) || ',' || round(lsm4) || ',' || ',' || ',' || round(onorder)
|| ',' || ',' || ',' || binone || ',' || ',' || round(backorderqty) ||
',' || ',' || round(onhold) || ',' || ',' || ',' || ',' || ',' || ',' ||
',' || round(qtyperjob) || ',' || round(ordermax) AS gmrim FROM slparts
WHERE vendor LIKE 'CH%'
The query does work and I am getting results from the database. There
are values for all 'partnum' in the database, however, the query results
include blank fields here and there in between the returned records. Why
am I receiving blank fields for 'gmrim'???? This absolutely defies logic
and I cannot find any rhyme or reason for this problem. I cannot have
any blank rows in the query, and again the database is completely
populated with values.
Could someone give me an explanation or tell me why I am receiving blank
rows here and there as a query result. If you need further clarification
of this problem or have any questions for me to arrive at a conclusion
please feel free to send them to me and I will be more than happy to
open a dialog in order to solve this problem.
Thanks in advance for assistance.
Cordially,
Joshua Neil
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: problems with SELECT query results
2007-05-29 15:47 problems with SELECT query results Joshua <joshua@joshuaneil.com>
@ 2007-05-29 16:36 ` Richard Huxton <dev@archonet.com>
2007-05-29 17:46 ` Re: problems with SELECT query results Joshua <joshua@joshuaneil.com>
0 siblings, 1 reply; 5+ messages in thread
From: Richard Huxton @ 2007-05-29 16:36 UTC (permalink / raw)
To: Joshua <joshua@joshuaneil.com>; +Cc: pgsql-sql
Joshua wrote:
> Hello,
>
> I am new to this list and have been working with PostgreSQL since January.
>
> Here is my problem, I hope someone here has some experience or can point
> me in the right direction. I am writing the following query for a C#
> program I am writing:
>
> SELECT 'PV.LINEITEM:' || partnum || ',' || round(onhand) || ',' ||
> round(qm5) || ',' || round(lsm4) || ',' || ',' || ',' || round(onorder)
> || ',' || ',' || ',' || binone || ',' || ',' || round(backorderqty) ||
> ',' || ',' || round(onhold) || ',' || ',' || ',' || ',' || ',' || ',' ||
> ',' || round(qtyperjob) || ',' || round(ordermax) AS gmrim FROM slparts
> WHERE vendor LIKE 'CH%'
>
> The query does work and I am getting results from the database. There
> are values for all 'partnum' in the database, however, the query results
> include blank fields here and there in between the returned records. Why
> am I receiving blank fields for 'gmrim'???? This absolutely defies logic
> and I cannot find any rhyme or reason for this problem. I cannot have
> any blank rows in the query, and again the database is completely
> populated with values.
I'm not sure it is - I think you've got a NULL somewhere.
Since NULL means "unknown" ('text' || NULL) = NULL
Wrap all your column-references in COALESCE: e.g. COALESCE(partnum,'')
and see if that solves it. If so, go back and find rows WHERE partnum IS
NULL and correct them. Then set the NOT NULL constraint on the relevant
columns.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: problems with SELECT query results
2007-05-29 15:47 problems with SELECT query results Joshua <joshua@joshuaneil.com>
2007-05-29 16:36 ` Re: problems with SELECT query results Richard Huxton <dev@archonet.com>
@ 2007-05-29 17:46 ` Joshua <joshua@joshuaneil.com>
2007-05-29 19:33 ` Re: problems with SELECT query results Lew <lew@nospam.lewscanon.com>
2007-05-29 20:35 ` Re: problems with SELECT query results Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 2 replies; 5+ messages in thread
From: Joshua @ 2007-05-29 17:46 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
Hello,
Thank you all for your assistance. I did end up finding NULL in the
'onorder' column which should have been zero's.... this was in a test
table that happened to have some NULL in it for one reason or another
but I should not find this in the production version of the table.
I also appreciate you pointing out the COALESCE function. I will find
that helpful in future work.
I look forward to hopefully assisting you guys with some of your
PostgreSQL dilemmas in the future!
Thanks again!!!
-Joshua
Richard Huxton wrote:
> Joshua wrote:
>> Hello,
>>
>> I am new to this list and have been working with PostgreSQL since
>> January.
>>
>> Here is my problem, I hope someone here has some experience or can
>> point me in the right direction. I am writing the following query for
>> a C# program I am writing:
>>
>> SELECT 'PV.LINEITEM:' || partnum || ',' || round(onhand) || ',' ||
>> round(qm5) || ',' || round(lsm4) || ',' || ',' || ',' ||
>> round(onorder) || ',' || ',' || ',' || binone || ',' || ',' ||
>> round(backorderqty) || ',' || ',' || round(onhold) || ',' || ',' ||
>> ',' || ',' || ',' || ',' || ',' || round(qtyperjob) || ',' ||
>> round(ordermax) AS gmrim FROM slparts WHERE vendor LIKE 'CH%'
>>
>> The query does work and I am getting results from the database. There
>> are values for all 'partnum' in the database, however, the query
>> results include blank fields here and there in between the returned
>> records. Why am I receiving blank fields for 'gmrim'???? This
>> absolutely defies logic and I cannot find any rhyme or reason for
>> this problem. I cannot have any blank rows in the query, and again
>> the database is completely populated with values.
>
> I'm not sure it is - I think you've got a NULL somewhere.
>
> Since NULL means "unknown" ('text' || NULL) = NULL
>
> Wrap all your column-references in COALESCE: e.g. COALESCE(partnum,'')
> and see if that solves it. If so, go back and find rows WHERE partnum
> IS NULL and correct them. Then set the NOT NULL constraint on the
> relevant columns.
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: problems with SELECT query results
2007-05-29 15:47 problems with SELECT query results Joshua <joshua@joshuaneil.com>
2007-05-29 16:36 ` Re: problems with SELECT query results Richard Huxton <dev@archonet.com>
2007-05-29 17:46 ` Re: problems with SELECT query results Joshua <joshua@joshuaneil.com>
@ 2007-05-29 19:33 ` Lew <lew@nospam.lewscanon.com>
1 sibling, 0 replies; 5+ messages in thread
From: Lew @ 2007-05-29 19:33 UTC (permalink / raw)
To: pgsql-general@postgresql.org
Joshua wrote:
> Thank you all for your assistance. I did end up finding NULL in the
> 'onorder' column which should have been zero's.... this was in a test
> table that happened to have some NULL in it for one reason or another
> but I should not find this in the production version of the table.
>
> I also appreciate you pointing out the COALESCE function. I will find
> that helpful in future work.
>
> I look forward to hopefully assisting you guys with some of your
> PostgreSQL dilemmas in the future!
Please do not top-post, and be sure to acknowledge both groups to whom you
multi-posted, pgsql.sql and pgsql.general, for their help. (I don't know if
there were others.)
A more effective way to post to multiple groups is to cross-post, send to all
groups in the single message as I did here, which unifies the threads so that
all answers appear to each reader.
Multi-posting (sending to each group separately) is frustrating because it
fragments the conversation.
--
Lew
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: problems with SELECT query results
2007-05-29 15:47 problems with SELECT query results Joshua <joshua@joshuaneil.com>
2007-05-29 16:36 ` Re: problems with SELECT query results Richard Huxton <dev@archonet.com>
2007-05-29 17:46 ` Re: problems with SELECT query results Joshua <joshua@joshuaneil.com>
@ 2007-05-29 20:35 ` Richard Broersma Jr <rabroersma@yahoo.com>
1 sibling, 0 replies; 5+ messages in thread
From: Richard Broersma Jr @ 2007-05-29 20:35 UTC (permalink / raw)
To: Joshua <joshua@joshuaneil.com>; Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
--- Joshua <joshua@joshuaneil.com> wrote:
> Hello,
>
> Thank you all for your assistance. I did end up finding NULL in the
> 'onorder' column which should have been zero's.... this was in a test
> table that happened to have some NULL in it for one reason or another
> but I should not find this in the production version of the table.
If this should be allowed to have nulls, you should add a not null constraint to your table to
prevent this sort of thing from happening. If a field should have zero's instead of nulls, you
should also add a default value of zero for this column.
ALTER TABLE Slparts
ALTER COLUMN onorder SET NOT NULL,
ALTER COLUMN onorder SET DEFAULT 0;
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-05-29 20:35 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-29 15:47 problems with SELECT query results Joshua <joshua@joshuaneil.com>
2007-05-29 16:36 ` Richard Huxton <dev@archonet.com>
2007-05-29 17:46 ` Joshua <joshua@joshuaneil.com>
2007-05-29 19:33 ` Lew <lew@nospam.lewscanon.com>
2007-05-29 20:35 ` Richard Broersma Jr <rabroersma@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox