Re: problems with SELECT query results

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