Postgres Pro
Downloads
Home   >   mailing lists

Thread: query-question

query-question

From
Alexander Blüm
Date:
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

Re: query-question

From
Pavel Stehule
Date:
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)
>

Re: query-question

From
Csaba Nagy
Date:
|| 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)

Re: query-question

From
Stephan Szabo
Date:
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

Re: query-question

From
Alexander Blüm
Date:
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

Re: query-question

From
Holger Marzen
Date:
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.