Re: problems with SELECT query results

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(at)joshuaneil(dot)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
>