Обсуждение: query-question
hello, I'm facing an odd problem. I have a query: SELECT "Kürzel", "Autor(en)", "Titel" FROM "tblTitelangaben" WHERE "Titel"||"Kürzel"||"Monographie-Kürzel"||"Autor(en)" ILIKE '%er%'; this works like a charm. but if I add another ||"something" - which might be emtpy, I get no results.. why? WHERE "Titel"||"Kürzel"||"Monographie-Kürzel"||"Autor(en)"||"Herausgeber" ILIKE '%er%'; || is the same as "OR", is it not? 1 OR 0 = 1 1 OR 1 = 1 0 OR 0 = 0 right? therefore I should get the same, or more results... any hints are greatly appreciated! TIA, alex
Hello || isn't OR!! testdb011=> select 'aaaa'||'bbbbbbb'; ?column? ------------- aaaabbbbbbb This is SQL, not java or C. OR is OR in SQL :-> Pavel On Thu, 11 Sep 2003, [ISO-8859-1] Alexander Blüm wrote: > hello, > > I'm facing an odd problem. > I have a query: > > SELECT "Kürzel", "Autor(en)", "Titel" > FROM "tblTitelangaben" > WHERE "Titel"||"Kürzel"||"Monographie-Kürzel"||"Autor(en)" ILIKE '%er%'; > > this works like a charm. > > but if I add another ||"something" - which might be emtpy, I get no > results.. why? > > WHERE > "Titel"||"Kürzel"||"Monographie-Kürzel"||"Autor(en)"||"Herausgeber" > ILIKE '%er%'; > > || is the same as "OR", is it not? > > 1 OR 0 = 1 > 1 OR 1 = 1 > 0 OR 0 = 0 > right? > therefore I should get the same, or more results... > > any hints are greatly appreciated! > > TIA, > alex > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
|| is the string concatenation operator, so what you're doing is equivalent to using the concatenated values of those fields. I have no experience with ILIKE, but you might figure out yourself what's wrong... HTH, Csaba. On Thu, 2003-09-11 at 13:36, Alexander Blüm wrote: > hello, > > I'm facing an odd problem. > I have a query: > > SELECT "Kürzel", "Autor(en)", "Titel" > FROM "tblTitelangaben" > WHERE "Titel"||"Kürzel"||"Monographie-Kürzel"||"Autor(en)" ILIKE '%er%'; > > this works like a charm. > > but if I add another ||"something" - which might be emtpy, I get no > results.. why? > > WHERE > "Titel"||"Kürzel"||"Monographie-Kürzel"||"Autor(en)"||"Herausgeber" > ILIKE '%er%'; > > || is the same as "OR", is it not? > > 1 OR 0 = 1 > 1 OR 1 = 1 > 0 OR 0 = 0 > right? > therefore I should get the same, or more results... > > any hints are greatly appreciated! > > TIA, > alex > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
On Thu, 11 Sep 2003, [ISO-8859-1] Alexander Bl�m wrote: > hello, > > I'm facing an odd problem. > I have a query: > > SELECT "K�rzel", "Autor(en)", "Titel" > FROM "tblTitelangaben" > WHERE "Titel"||"K�rzel"||"Monographie-K�rzel"||"Autor(en)" ILIKE '%er%'; > > this works like a charm. > > but if I add another ||"something" - which might be emtpy, I get no > results.. why? > > WHERE > "Titel"||"K�rzel"||"Monographie-K�rzel"||"Autor(en)"||"Herausgeber" > ILIKE '%er%'; > > || is the same as "OR", is it not? No, it's string concatenation. Also, remember that NULLs are special a || NULL is NULL. You can use coalesce(field,"") to get an empty string if field is NULL. > 1 OR 0 = 1 > 1 OR 1 = 1 > 0 OR 0 = 0 > right? And even for OR, the above isn't true in SQL because of NULLs. 1 | 0 | N 1 1 | 1 | N 0 1 | 0 | N N N | N | N
Stephan Szabo wrote:
> On Thu, 11 Sep 2003, [ISO-8859-1] Alexander Bl?m wrote:
>
>
>>hello,
>>
>>I'm facing an odd problem.
>>I have a query:
>>
>>SELECT "K?rzel", "Autor(en)", "Titel"
>>FROM "tblTitelangaben"
>>WHERE "Titel"||"K?rzel"||"Monographie-K?rzel"||"Autor(en)" ILIKE '%er%';
>>
>>this works like a charm.
>>
>>but if I add another ||"something" - which might be emtpy, I get no
>>results.. why?
>>
>>WHERE
>>"Titel"||"K?rzel"||"Monographie-K?rzel"||"Autor(en)"||"Herausgeber"
>>ILIKE '%er%';
>>
>>|| is the same as "OR", is it not?
>
>
> No, it's string concatenation. Also, remember that NULLs are special
> a || NULL is NULL. You can use coalesce(field,"") to get an empty string
> if field is NULL.
>
>
>>1 OR 0 = 1
>>1 OR 1 = 1
>>0 OR 0 = 0
>>right?
>
>
> And even for OR, the above isn't true in SQL because of NULLs.
> 1 | 0 | N
> 1 1 | 1 | N
> 0 1 | 0 | N
> N N | N | N
>
>
THANK YOU VERY MUCH!!! ;))
that did the deed, exactly as intended!!
now I understand.
thanks again to all of you!
cheers,
alex
On Thu, 11 Sep 2003, Alexander Blüm wrote:
> hello,
>
> I'm facing an odd problem.
> I have a query:
>
> SELECT "Kürzel", "Autor(en)", "Titel"
> FROM "tblTitelangaben"
> WHERE "Titel"||"Kürzel"||"Monographie-Kürzel"||"Autor(en)" ILIKE '%er%';
>
> this works like a charm.
>
> but if I add another ||"something" - which might be emtpy, I get no
> results.. why?
>
> WHERE
> "Titel"||"Kürzel"||"Monographie-Kürzel"||"Autor(en)"||"Herausgeber"
> ILIKE '%er%';
>
> || is the same as "OR", is it not?
No. Any component must not be NULL. Use coalesce("Herausgeber",'') to
ensure it's never NULL.