Is it not valid to use concatenation in a WHERE ... LIKE clause?

SELECT iam0.g_itemId
FROM g2_ItemAttributesMap AS iam0,
g2_ItemAttributesMap AS iam1
WHERE iam1.g_parentSequence LIKE iam0.g_parentSequence || iam0.g_itemId || '/%'

SQL0440N No authorized routine named "||" of type "FUNCTION" having
compatible arguments was found. SQLSTATE=42884

Environment is DB2 LUW, V8.2.2, Windows XP SP2.

Thanks.
---
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"

Larry Menard wrote:
> Is it not valid to use concatenation in a WHERE ... LIKE clause?
>
>
> SELECT iam0.g_itemId
> FROM g2_ItemAttributesMap AS iam0,
> g2_ItemAttributesMap AS iam1
> WHERE iam1.g_parentSequence LIKE iam0.g_parentSequence ||
> iam0.g_itemId || '/%'
>
> SQL0440N No authorized routine named "||" of type "FUNCTION" having
> compatible arguments was found. SQLSTATE=42884
>
>
> Environment is DB2 LUW, V8.2.2, Windows XP SP2.
>
> Thanks.
> ---
> --------------------
> Larry Menard
> "Defender of Geese and of All Things Natural"
>
>
What is the data type of iam0.g_itemId?

"Bob Stearns" <rstearns1241@charter.net> wrote in message
news:enacf.61780$RG4.1798@fe05.lga...
> Larry Menard wrote:
> > Is it not valid to use concatenation in a WHERE ... LIKE clause?
> >
> >
> > SELECT iam0.g_itemId
> > FROM g2_ItemAttributesMap AS iam0,
> > g2_ItemAttributesMap AS iam1
> > WHERE iam1.g_parentSequence LIKE iam0.g_parentSequence ||
> > iam0.g_itemId || '/%'
> >
> > SQL0440N No authorized routine named "||" of type "FUNCTION" having
> > compatible arguments was found. SQLSTATE=42884
> >
> >
> > Environment is DB2 LUW, V8.2.2, Windows XP SP2.
> >
> > Thanks.
> > ---
> > --------------------
> > Larry Menard
> > "Defender of Geese and of All Things Natural"
> >
> >
> What is the data type of iam0.g_itemId?

See:
http://publib.boulder.ibm.com/infoce...n/r0000751.htm

You can't do this. The <pattern> argument of a LIKE operator is pretty
restrictive -- it must be a host variable, a constant, a special register, a
scalar function result, or a concatenation of any of these.

In your case, you're using two column values (not allowed) and a string
constant (allowed).

--
Matt Emmerton

Thanks, guys. I had checked the doc for CONCAT , but not LIKE.

(FYI Bob, the datatype of 'iam0.g_itemId' is INTEGER.)

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"


"Matt Emmerton" <memmerto@nospam.yahoo.com> wrote in message
news:VcudnaHCLdbf0OzenZ2dnUVZ_vmdnZ2d@rogers.com.. .
>
> "Bob Stearns" <rstearns1241@charter.net> wrote in message
> news:enacf.61780$RG4.1798@fe05.lga...
>> Larry Menard wrote:
>> > Is it not valid to use concatenation in a WHERE ... LIKE clause?
>> >
>> >
>> > SELECT iam0.g_itemId
>> > FROM g2_ItemAttributesMap AS iam0,
>> > g2_ItemAttributesMap AS iam1
>> > WHERE iam1.g_parentSequence LIKE iam0.g_parentSequence ||
>> > iam0.g_itemId || '/%'
>> >
>> > SQL0440N No authorized routine named "||" of type "FUNCTION"
>> > having
>> > compatible arguments was found. SQLSTATE=42884
>> >
>> >
>> > Environment is DB2 LUW, V8.2.2, Windows XP SP2.
>> >
>> > Thanks.
>> > ---
>> > --------------------
>> > Larry Menard
>> > "Defender of Geese and of All Things Natural"
>> >
>> >
>> What is the data type of iam0.g_itemId?
>
> See:
> http://publib.boulder.ibm.com/infoce...n/r0000751.htm
>
> You can't do this. The <pattern> argument of a LIKE operator is pretty
> restrictive -- it must be a host variable, a constant, a special register,
> a
> scalar function result, or a concatenation of any of these.
>
> In your case, you're using two column values (not allowed) and a string
> constant (allowed).
>
> --
> Matt Emmerton
>
>
>

Larry Menard wrote:

> Thanks, guys. I had checked the doc for CONCAT , but not LIKE.
>
> (FYI Bob, the datatype of 'iam0.g_itemId' is INTEGER.)
>
That is why the error message about concatenation is appearing; it is
not defined on integers, only character types. You would later, after
using something like char(iam0.g_itemId) in the concatenation, find the
limitation on LIKE.

Ah, thanks again.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"


"Bob Stearns" <rstearns1241@charter.net> wrote in message
news:s9gcf.61$Ae3.37@fe06.lga...
> Larry Menard wrote:
>
>> Thanks, guys. I had checked the doc for CONCAT , but not LIKE.
>>
>> (FYI Bob, the datatype of 'iam0.g_itemId' is INTEGER.)
>>
> That is why the error message about concatenation is appearing; it is not
> defined on integers, only character types. You would later, after using
> something like char(iam0.g_itemId) in the concatenation, find the
> limitation on LIKE.

I'm still having a heck of a time trying to come up with an equivalent statement that works in DB2.

The original statement (which works in other RDBMSs) is:

SELECT iam0.g_itemId
FROM g2_ItemAttributesMap AS iam0,
g2_ItemAttributesMap AS iam1
WHERE iam1.g_parentSequence LIKE iam0.g_parentSequence || iam0.g_itemId || '/%'

The doc says the only things that can be contained in the LIKE predicate are:
a.. A constant
b.. A special register
c.. A host variable
d.. A scalar function whose operands are any of the above
e.. An expression concatenating any of the above

So I tried creating various UDFs that return the string that the original query is trying to build in the LIKE predicate. For example:

create function g2_concat_like () returns varchar(255)
begin atomic
declare retval varchar(255);
set retval = 'test string for the LIKE predicate %';
return retval;
end@
DB20000I The SQL command completed successfully.

SELECT iam0.g_itemId
FROM g2_ItemAttributesMap AS iam0,
g2_ItemAttributesMap AS iam1
WHERE iam1.g_parentSequence LIKE g2_concat_like ()
SQL0132N A LIKE predicate or POSSTR scalar function is not valid because the
first operand is not a string expression or the second operand is not a
string. SQLSTATE=42824

The table schema is:

describe table g2_ItemAttributesMap

Column Type Type
name schema name Length Scale Nulls
------------------------------ --------- ------------------ -------- ----- ------
G_ITEMID SYSIBM INTEGER 4 0 No
G_VIEWCOUNT SYSIBM INTEGER 4 0 Yes
G_ORDERWEIGHT SYSIBM INTEGER 4 0 Yes
G_PARENTSEQUENCE SYSIBM VARCHAR 255 0 No

4 record(s) selected.



Can anyone show me a way to make an equivalent query work on DB2 or tell me what I'm doing wrong now??

Thanks.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"


"Larry Menard" <root@GoSpamYourself.com> wrote in message news:CI2dnb2u-9vQa-zenZ2dnUVZ_sudnZ2d@rogers.com...
> Ah, thanks again.
>
> --
> --------------------
> Larry Menard
> "Defender of Geese and of All Things Natural"
>
>
> "Bob Stearns" <rstearns1241@charter.net> wrote in message
> news:s9gcf.61$Ae3.37@fe06.lga...
>> Larry Menard wrote:
>>
>>> Thanks, guys. I had checked the doc for CONCAT , but not LIKE.
>>>
>>> (FYI Bob, the datatype of 'iam0.g_itemId' is INTEGER.)
>>>
>> That is why the error message about concatenation is appearing; it is not
>> defined on integers, only character types. You would later, after using
>> something like char(iam0.g_itemId) in the concatenation, find the
>> limitation on LIKE.
>
>

Larry Menard wrote:

> I'm still having a heck of a time trying to come up with an equivalent
> statement that works in DB2.
>
> The original statement (which works in other RDBMSs) is:
>
> SELECT iam0.g_itemId
> FROM g2_ItemAttributesMap AS iam0,
> g2_ItemAttributesMap AS iam1
> WHERE iam1.g_parentSequence LIKE iam0.g_parentSequence || iam0.g_itemId
> || '/%'
>
> The doc says the only things that can be contained in the LIKE
> predicate are:
> a.. A constant
> b.. A special register
> c.. A host variable
> d.. A scalar function whose operands are any of the above
> e.. An expression concatenating any of the above

The problem is that you can't use columns on the right side of the LIKE
predicate. Everything on the right must be pre-determined, i.e. constant,
during the query execution and columns do not contain the same value for
each row. So you're out of luck.

> Can anyone show me a way to make an equivalent query work on DB2 or
> tell me what I'm doing wrong now??

I'll have to stick to other means like rolling your own function. Once I
wrote a UDF that provides regular expression support:
http://www-128.ibm.com/developerwork...301stolze.html

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Larry Menard wrote:
> I'm still having a heck of a time trying to come up with an
> equivalent statement that works in DB2.
>
> The original statement (which works in other RDBMSs) is:
>
>
> SELECT iam0.g_itemId
> FROM g2_ItemAttributesMap AS iam0,
> g2_ItemAttributesMap AS iam1
> WHERE iam1.g_parentSequence LIKE iam0.g_parentSequence ||
> iam0.g_itemId || '/%'
>
>
> The doc says the only things that can be contained in the LIKE
> predicate are:
>
> * A constant
> * A special register
> * A host variable
> * A scalar function whose operands are any of the above
> * An expression concatenating any of the above
>
>
> So I tried creating various UDFs that return the string that the
> original query is trying to build in the LIKE predicate. For example:
>
>
> create function g2_concat_like () returns varchar(255)
> begin atomic
> declare retval varchar(255);
> set retval = 'test string for the LIKE predicate %';
> return retval;
> end@
> DB20000I The SQL command completed successfully.
>
> SELECT iam0.g_itemId
> FROM g2_ItemAttributesMap AS iam0,
> g2_ItemAttributesMap AS iam1
> WHERE iam1.g_parentSequence LIKE g2_concat_like ()
> SQL0132N A LIKE predicate or POSSTR scalar function is not valid
> because the
> first operand is not a string expression or the second operand is not a
> string. SQLSTATE=42824
>
>
> The table schema is:
>
>
> describe table g2_ItemAttributesMap
>
> Column Type Type
> name schema name Length
> Scale Nulls
> ------------------------------ --------- ------------------ --------
> ----- ------
> G_ITEMID SYSIBM INTEGER
> 4 0 No
> G_VIEWCOUNT SYSIBM INTEGER
> 4 0 Yes
> G_ORDERWEIGHT SYSIBM INTEGER
> 4 0 Yes
> G_PARENTSEQUENCE SYSIBM VARCHAR
> 255 0 No
>
> 4 record(s) selected.
>
>
>
>
> Can anyone show me a way to make an equivalent query work on DB2 or
> tell me what I'm doing wrong now??
>
> Thanks.
>
> --
> --------------------
> Larry Menard
> "Defender of Geese and of All Things Natural"
>
>
> "Larry Menard" <root@GoSpamYourself.com
> <mailto:root@GoSpamYourself.com>> wrote in message
> news:CI2dnb2u-9vQa-zenZ2dnUVZ_sudnZ2d@rogers.com...
> > Ah, thanks again.
> >
> > --
> > --------------------
> > Larry Menard
> > "Defender of Geese and of All Things Natural"
> >
> >
> > "Bob Stearns" <rstearns1241@charter.net
> <mailto:rstearns1241@charter.net>> wrote in message
> > news:s9gcf.61$Ae3.37@fe06.lga...
> >> Larry Menard wrote:
> >>
> >>> Thanks, guys. I had checked the doc for CONCAT , but not LIKE.
> >>>
> >>> (FYI Bob, the datatype of 'iam0.g_itemId' is INTEGER.)
> >>>
> >> That is why the error message about concatenation is appearing; it
> is not
> >> defined on integers, only character types. You would later, after using
> >> something like char(iam0.g_itemId) in the concatenation, find the
> >> limitation on LIKE.
> >
> >
Larry,

LIKE in DB2 does not support having a non-constant expression in the
pattern. (fullstop).
If you download the migration toolkit for SQL Server it provides a LIKE
UDF which does the job.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

But the doc for the LIKE predicate says:

The expression can be specified by:

a.. A constant
b.. A special register
c.. A host variable
d.. A scalar function whose operands are any of the above
e.. An expression concatenating any of the above

Does the UDF I wrote not qualify as a "scalar function"? If not, why not?

(I'm trying to download the MTK but IBM's web site is not accepting my postal code. Sigh...)

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"


"Serge Rielau" <srielau@ca.ibm.com> wrote in message news:3tgu9jFsu55gU2@individual.net...
> Larry Menard wrote:
>> I'm still having a heck of a time trying to come up with an
>> equivalent statement that works in DB2.
>>
>> The original statement (which works in other RDBMSs) is:
>>
>>
>> SELECT iam0.g_itemId
>> FROM g2_ItemAttributesMap AS iam0,
>> g2_ItemAttributesMap AS iam1
>> WHERE iam1.g_parentSequence LIKE iam0.g_parentSequence ||
>> iam0.g_itemId || '/%'
>>
>>
>> The doc says the only things that can be contained in the LIKE
>> predicate are:
>>
>> * A constant
>> * A special register
>> * A host variable
>> * A scalar function whose operands are any of the above
>> * An expression concatenating any of the above
>>
>>
>> So I tried creating various UDFs that return the string that the
>> original query is trying to build in the LIKE predicate. For example:
>>
>>
>> create function g2_concat_like () returns varchar(255)
>> begin atomic
>> declare retval varchar(255);
>> set retval = 'test string for the LIKE predicate %';
>> return retval;
>> end@
>> DB20000I The SQL command completed successfully.
>>
>> SELECT iam0.g_itemId
>> FROM g2_ItemAttributesMap AS iam0,
>> g2_ItemAttributesMap AS iam1
>> WHERE iam1.g_parentSequence LIKE g2_concat_like ()
>> SQL0132N A LIKE predicate or POSSTR scalar function is not valid
>> because the
>> first operand is not a string expression or the second operand is not a
>> string. SQLSTATE=42824
>>
>>
>> The table schema is:
>>
>>
>> describe table g2_ItemAttributesMap
>>
>> Column Type Type
>> name schema name Length
>> Scale Nulls
>> ------------------------------ --------- ------------------ --------
>> ----- ------
>> G_ITEMID SYSIBM INTEGER
>> 4 0 No
>> G_VIEWCOUNT SYSIBM INTEGER
>> 4 0 Yes
>> G_ORDERWEIGHT SYSIBM INTEGER
>> 4 0 Yes
>> G_PARENTSEQUENCE SYSIBM VARCHAR
>> 255 0 No
>>
>> 4 record(s) selected.
>>
>>
>>
>>
>> Can anyone show me a way to make an equivalent query work on DB2 or
>> tell me what I'm doing wrong now??
>>
>> Thanks.
>>
>> --
>> --------------------
>> Larry Menard
>> "Defender of Geese and of All Things Natural"
>>
>>
>> "Larry Menard" <root@GoSpamYourself.com
>> <mailto:root@GoSpamYourself.com>> wrote in message
>> news:CI2dnb2u-9vQa-zenZ2dnUVZ_sudnZ2d@rogers.com...
>> > Ah, thanks again.
>> >
>> > --
>> > --------------------
>> > Larry Menard
>> > "Defender of Geese and of All Things Natural"
>> >
>> >
>> > "Bob Stearns" <rstearns1241@charter.net
>> <mailto:rstearns1241@charter.net>> wrote in message
>> > news:s9gcf.61$Ae3.37@fe06.lga...
>> >> Larry Menard wrote:
>> >>
>> >>> Thanks, guys. I had checked the doc for CONCAT , but not LIKE.
>> >>>
>> >>> (FYI Bob, the datatype of 'iam0.g_itemId' is INTEGER.)
>> >>>
>> >> That is why the error message about concatenation is appearing; it
>> is not
>> >> defined on integers, only character types. You would later, after using
>> >> something like char(iam0.g_itemId) in the concatenation, find the
>> >> limitation on LIKE.
>> >
>> >
> Larry,
>
> LIKE in DB2 does not support having a non-constant expression in the
> pattern. (fullstop).
> If you download the migration toolkit for SQL Server it provides a LIKE
> UDF which does the job.
>
> Cheers
> Serge
> --
> Serge Rielau
> DB2 SQL Compiler Development
> IBM Toronto Lab