Message-ID: <465C5671.50907@archonet.com>
Date: Tue, 29 May 2007 17:36:01 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: Joshua <joshua@joshuaneil.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: problems with SELECT query results
References: <465C4B0B.4070906@joshuaneil.com>
In-Reply-To: <465C4B0B.4070906@joshuaneil.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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