Re: problems with SELECT query results

[Richard Huxton <dev(at)archonet(dot)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