query-question

[Alexander Blüm]

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 ma*******@postgresql.org)

[Pavel Stehule]

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 ma*******@postgresql.org)

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Csaba Nagy]

|| 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 ma*******@postgresql.org)

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Csaba Nagy]

|| 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 ma*******@postgresql.org)

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Stephan Szabo]

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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Alexander Blüm]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Stephan Szabo]

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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Alexander Blüm]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Holger Marzen]

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.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Holger Marzen]

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.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org