Hello,
Thank you all for your assistance. I did end up finding NULL in the
'onorder' column which should have been zero's.... this was in a test
table that happened to have some NULL in it for one reason or another
but I should not find this in the production version of the table.
I also appreciate you pointing out the COALESCE function. I will find
that helpful in future work.
I look forward to hopefully assisting you guys with some of your
PostgreSQL dilemmas in the future!
Thanks again!!!
-Joshua
Richard Huxton wrote:
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.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
