Re: Incorrect Query - Mailing list pgsql-novice

Hi,

Sorry for bothering the list, had a bad day yesterday, acheived the desired effect in Java code.

Regards,

Sharon Cowling



> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Sharon Cowling
> Sent: Thursday, 9 May 2002 08:01
> To: Joshua b. Jore
> Cc: Pgsql-Novice (E-mail)
> Subject: Re: [NOVICE] Incorrect Query
>
>
> Hi,
>
> > Why not just use?
> >
> > WHERE person_id = ''
> > OR (firstname = initcap('sharon')
> >     AND
> >     lastname = initcap('cowling')
> >    )
> >
>
> Because the user may enter either just the firstname or just
> the lastname, so the above won't work if I just enter
> 'cowling' and there are multiple entries of 'cowling' in the
> database, it returns 0 rows.
>
>
> select person_id, initcap(firstname), initcap(lastname), dob,
> street, city
> from person5
> where person_id = ''
> OR (firstname = initcap('')
> AND
> lastname = initcap('cowling')
> );
>
>  person_id | initcap | initcap | dob | street | city
> -----------+---------+---------+-----+--------+------
> (0 rows)
>
> select person_id, initcap(firstname), initcap(lastname), dob,
> street, city
> from person5
> where
> person_id = ''
> or
> firstname = initcap('')
> or
> lastname = initcap('cowling')
> or
> (
>    firstname = initcap('sharon')
>    and
>    lastname = initcap('')
> )
>
>  person_id | initcap | initcap |    dob     | street  |    city
> -----------+---------+---------+------------+---------+------------
>       1018 | Katrina | Cowling | 07/07/1976 | Long St | Wellington
>        858 | Sharon  | Cowling | 16/10/1979 | A Grove | Wellington
>
> Regards,
>
> Sharon Cowling
>
>
> > -----Original Message-----
> > From: Joshua b. Jore [mailto:josh@greentechnologist.org]
> > Sent: Thursday, 9 May 2002 01:39
> > To: Sharon Cowling
> > Cc: Pgsql-Novice (E-mail)
> > Subject: Re: [NOVICE] Incorrect Query
> >
> >
> > Sharon,
> > You were abusing parentheses and confusing the issue. Don't
> > do that, it
> > just makes the query less readable.
> >
> > Here is what you actually wrote:
> >
> > where
> > person_id = ''
> > or
> > (
> >    (
> >        firstname = initcap('sharon')
> >    )
> >    or
> >    (
> >        lastname = initcap('cowling')
> >    )
> > )
> > or
> > (
> >    (
> >        firstname = initcap('sharon')
> >        and
> >        lastname = initcap('cowling')
> >    )
> > )
> >
> > Which is simplified to (removing parentheses where redundant but
> > retaining all the logic). Check out that first bit where
> you match on
> > fname or lname. That last bit doesn't even do anything
> since the other
> > name expressions covered it already.
> >
> > where
> > person_id = ''
> > or
> > firstname = initcap('sharon')
> > or
> > lastname = initcap('cowling')
> > or
> > (
> >    firstname = initcap('sharon')
> >    and
> >    lastname = initcap('cowling')
> > )
> >
> > Why not just use?
> >
> > WHERE person_id = ''
> > OR (firstname = initcap('sharon')
> >     AND
> >     lastname = initcap('cowling')
> >    )
> >
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>