Predicate equivalence: concat vs. AND

[MeBuggyYouJane]

Looked everywhere (Date, Celko, Elmasri, this ng, c.d.t, etc), but can't
find any discussion of the following:

Proof that these predicates will (or not) return the same rows

where (col1 concat col2 concat col3 ...) =<SomeSuitableString>

where col1 =itsPartOfSomeSuitableString and
col2 =itsPartOfSomeSuitableString and
col3 =itsPartOfSomeSuitableString ...

short of exhaustive enumeration, of course. A bit of scribbling with
some data indicates, Yes. But that's not the general case.

The latter is preferable, for obvious reasons.

[Serge Rielau]

MeBuggyYouJane wrote:

Looked everywhere (Date, Celko, Elmasri, this ng, c.d.t, etc), but can't
find any discussion of the following:

Proof that these predicates will (or not) return the same rows

where (col1 concat col2 concat col3 ...) =<SomeSuitableString>

I don't think this is true if any of the columns is a VARCHAR.
And of course it won't be true for complex collations
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Knut Stolze]

MeBuggyYouJane wrote:

Looked everywhere (Date, Celko, Elmasri, this ng, c.d.t, etc), but can't
find any discussion of the following:

Proof that these predicates will (or not) return the same rows

where (col1 concat col2 concat col3 ...) =<SomeSuitableString>

where col1 =itsPartOfSomeSuitableString and
col2 =itsPartOfSomeSuitableString and
col3 =itsPartOfSomeSuitableString ...

short of exhaustive enumeration, of course. A bit of scribbling with
some data indicates, Yes. But that's not the general case.

Smells very much like an exercise to me.

The answer is obvious once you consider ordering. (That should be enough of
a hint.)

p.s: What kind of operator is "=>" supposed to be?

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Knut Stolze]

MeBuggyYouJane wrote:

Looked everywhere (Date, Celko, Elmasri, this ng, c.d.t, etc), but can't
find any discussion of the following:

Proof that these predicates will (or not) return the same rows

where (col1 concat col2 concat col3 ...) =<SomeSuitableString>

where col1 =itsPartOfSomeSuitableString and
col2 =itsPartOfSomeSuitableString and
col3 =itsPartOfSomeSuitableString ...

short of exhaustive enumeration, of course. A bit of scribbling with
some data indicates, Yes. But that's not the general case.

The latter is preferable, for obvious reasons.

Oh, and "WHERE ..." is not a predicate. It is a WHERE clause, which consists
of the keyword WHERE followed by a predicate.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany