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
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
--- Brian Hurt <bhurt@janestcapital.com> wrote: > Check for nulls. Remember that 'foo' || NULL yeilds NULL, so if one of > your fields is null, the whole string will be null. Also, if you know the nulls will be present, and you want a way to handle them is a predetermined way, you can wrap your fields like so: COALESCE( yourfield, 'oops value is null') or some such. http://www.postgresql.org/docs/8.2/interactive/functions-conditional.html#AEN13107 I hope this helps. Regards, Richard Broersma Jr.
Hello, I took your advice into consideration and checked the table. There are no NULLs in any of the fields that are in my SELECT statement. Any other suggestions? Brian Hurt 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. > > > 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 > > >
Joshua,
Please don't be offended by this very novice suggestion, but
when you check for NULL values, are you writing the query correctly
as:
'SELECT * FROM slparts WHERE onhand IS NULL OR qm5 IS NULL OR lsm4 IS
NULL OR onorder IS NULL OR binone IS NULL or backorderqty IS NULL OR
onhold IS NULL or qtyperjob IS NULL OR ordermax IS NULL;'
Because this query would not work:
'SELECT * FROM slparts WHERE onhand = NULL OR qm5 = NULL OR lsm4 =
NULL OR onorder = NULL OR binone = NULL or backorderqty = NULL OR
onhold = NULL or qtyperjob = NULL OR ordermax = NULL;'
Again, if this was something you were totally comfortable with and
aware of, I apologize for covering novice SQL territory - but it's a
mistake I made several times while writing my first queries checking
for null values.
-Mike
On 5/29/07, Joshua <joshua@joshuaneil.com> wrote:
> Hello,
>
> I took your advice into consideration and checked the table. There are
> no NULLs in any of the fields that are in my SELECT statement.
>
> Any other suggestions?
>
> Brian Hurt 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.
> >
> >
> > 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
> >
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>