Re: Incorrect Query
От | Sharon Cowling |
---|---|
Тема | Re: Incorrect Query |
Дата | |
Msg-id | 200205082148.g48LmON27868@lambton.sslnz.com обсуждение исходный текст |
Ответ на | Incorrect Query (Sharon Cowling <sharon.cowling@sslnz.com>) |
Список | 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 >
В списке pgsql-novice по дате отправления: