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.

Check for nulls. Remember that 'foo' || NULL yeilds NULL, so if one of
your fields is null, the whole string will be null.

You might also consider doing the select into a temporary table, and
they copying out the result as a CSV file- which looks to be what you're
trying to do. Or, alternatively, just select the rows desired and have
the client program convert things to CSV. Note that both of these will
also handle NULLs correctly.

Brian