Thread: problems with SELECT query results
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. Could someone give me an explanation or tell me why I am receiving blank rows here and there as a query result. If you need further clarification of this problem or have any questions for me to arrive at a conclusion please feel free to send them to me and I will be more than happy to open a dialog in order to solve this problem. Thanks in advance for assistance. Cordially, Joshua Neil
> 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%'
You could select columns and build the string in your application ?
> 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
Because one of your fields is probably NULL, and NULL || anything stays
NULL.
You have probably been misled to believe they are "blanks" because they
don't display as "NULL" but as "".
I set psql to display NULL as NULL.
If these columns can, must, or should not contain NULLs depends on your
application... it's for you to chose.
Use COALESCE, add NOT NULL constraints, grab the columns and build the
string in your application, you chose.
I checked the table and found that none of my fields in the SELECT statement contain NULLs. Any other suggestions? PFC wrote: >> 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%' > > You could select columns and build the string in your application ? > >> 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 > > Because one of your fields is probably NULL, and NULL || anything > stays NULL. > > You have probably been misled to believe they are "blanks" because > they don't display as "NULL" but as "". > I set psql to display NULL as NULL. > > If these columns can, must, or should not contain NULLs depends on > your application... it's for you to chose. > Use COALESCE, add NOT NULL constraints, grab the columns and build > the string in your application, you chose. > > > --No virus found in this incoming message. > Checked by AVG Free Edition. > Version: 7.5.446 / Virus Database: 269.8.1/822 - Release Date: > 5/28/2007 11:40 AM > >
Joshua wrote: > I checked the table and found that none of my fields in the SELECT > statement contain NULLs. > > Any other suggestions? Please post the queries you used. -- Richard Huxton Archonet Ltd
Joshua wrote: > I checked the table and found that none of my fields in the SELECT > statement contain NULLs. > > Any other suggestions? Why are you using such constructions in your query: ',' || ',' || ',' ? May be this set of commas makes you think that some of your fields are empty? Do you have empty fields in following query? 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%' P.S. If you really need so many commas - use them in a single block.... ',,,' > PFC wrote: >>> 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%' >> >> You could select columns and build the string in your application ? >> >>> 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 >> >> Because one of your fields is probably NULL, and NULL || anything >> stays NULL. >> >> You have probably been misled to believe they are "blanks" >> because they don't display as "NULL" but as "". >> I set psql to display NULL as NULL. >> >> If these columns can, must, or should not contain NULLs depends >> on your application... it's for you to chose. >> Use COALESCE, add NOT NULL constraints, grab the columns and >> build the string in your application, you chose. >> >> >> --No virus found in this incoming message. >> Checked by AVG Free Edition. >> Version: 7.5.446 / Virus Database: 269.8.1/822 - Release Date: >> 5/28/2007 11:40 AM >> >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
Joshua wrote: > 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! Please do not top-post, and be sure to acknowledge both groups to whom you multi-posted, pgsql.sql and pgsql.general, for their help. (I don't know if there were others.) A more effective way to post to multiple groups is to cross-post, send to all groups in the single message as I did here, which unifies the threads so that all answers appear to each reader. Multi-posting (sending to each group separately) is frustrating because it fragments the conversation. -- Lew