From: Joshua <joshua@joshuaneil.com>
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: problems with SELECT query results
Date: Tue, 29 May 2007 12:46:45 -0500
Message-ID: <465C6705.5000505@joshuaneil.com> (raw)
In-Reply-To: <465C5671.50907@archonet.com>
References: <465C4B0B.4070906@joshuaneil.com>
<465C5671.50907@archonet.com>
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.
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: joshua@joshuaneil.com, dev@archonet.com
Subject: Re: problems with SELECT query results
In-Reply-To: <465C6705.5000505@joshuaneil.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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