From: Richard Huxton <dev@archonet.com>
To: Joshua <joshua@joshuaneil.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: problems with SELECT query results
Date: Tue, 29 May 2007 17:36:01 +0100
Message-ID: <465C5671.50907@archonet.com> (raw)
In-Reply-To: <465C4B0B.4070906@joshuaneil.com>
References: <465C4B0B.4070906@joshuaneil.com>
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
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: dev@archonet.com, joshua@joshuaneil.com
Subject: Re: problems with SELECT query results
In-Reply-To: <465C5671.50907@archonet.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