Concatenation

[Larry Menard]

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"

[Bob Stearns]

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?

[Matt Emmerton]

"Bob Stearns" <rs**********@charter.net> wrote in message
news:en******************@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]

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" <me******@nospam.yahoo.com> wrote in message
news:Vc******************************@rogers.com.. .

"Bob Stearns" <rs**********@charter.net> wrote in message
news:en******************@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

[Bob Stearns]

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]

Ah, thanks again.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Bob Stearns" <rs**********@charter.net> wrote in message
news:s9*************@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]

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" <ro**@GoSpamYourself.com> wrote in message news:CI******************************@rogers.com.. .

Ah, thanks again.

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


"Bob Stearns" <rs**********@charter.net> wrote in message
news:s9*************@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.

[Knut Stolze]

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

[Serge Rielau]

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" <ro**@GoSpamYourself.com
<mailto:ro**@GoSpamYourself.com>> wrote in message
news:CI******************************@rogers.com.. .

> Ah, thanks again.
>
> --
> --------------------
> Larry Menard
> "Defender of Geese and of All Things Natural"
>
>
> "Bob Stearns" <rs**********@charter.net

<mailto:rs**********@charter.net>> wrote in message

> news:s9*************@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

[Larry Menard]

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" <sr*****@ca.ibm.com> wrote in message news:3t************@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" <ro**@GoSpamYourself.com
<mailto:ro**@GoSpamYourself.com>> wrote in message
news:CI******************************@rogers.com.. .

> Ah, thanks again.
>
> --
> --------------------
> Larry Menard
> "Defender of Geese and of All Things Natural"
>
>
> "Bob Stearns" <rs**********@charter.net

<mailto:rs**********@charter.net>> wrote in message

> news:s9*************@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

[Knut Stolze]

Larry Menard wrote:

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?
It doesn't because the operands (parameters) of your function did not take a
constant, special register or host variable when you executed the
statement.
(I'm trying to download the MTK but IBM's web site is not accepting my
postal code. Sigh...)

Just type something in there that works.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Larry Menard]

Even though the function creation is successful it says it can't find the function at runtime:

drop function g2_concat_like (char())
DB20000I The SQL command completed successfully.

create function g2_concat_like (in_char char(3)) returns char(50)
begin atomic
declare retval char(50);
set retval = 'some 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 ('ljm')
SQL0440N No authorized routine named "G2_CONCAT_LIKE" of type "FUNCTION"
having compatible arguments was found. SQLSTATE=42884

Just type something in there that works
Allow me to re-phrase my observation... *Nothing* works.
--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Knut Stolze" <st****@de.ibm.com> wrote in message news:dk**********@lc03.rz.uni-jena.de... Larry Menard wrote:

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?

It doesn't because the operands (parameters) of your function did not take a
constant, special register or host variable when you executed the
statement.

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

Just type something in there that works.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Matt Emmerton]

You need to cast the parameter explicitly -- ... LIKE
g2_contact_like(cast('ljm') as char(3)).

--
Matt
"Larry Menard" <ro**@GoSpamYourself.com> wrote in message
news:C_********************@rogers.com...
Even though the function creation is successful it says it can't find the
function at runtime:

drop function g2_concat_like (char())
DB20000I The SQL command completed successfully.

create function g2_concat_like (in_char char(3)) returns char(50)
begin atomic
declare retval char(50);
set retval = 'some 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 ('ljm')
SQL0440N No authorized routine named "G2_CONCAT_LIKE" of type "FUNCTION"
having compatible arguments was found. SQLSTATE=42884

Just type something in there that works
Allow me to re-phrase my observation... *Nothing* works.
--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Knut Stolze" <st****@de.ibm.com> wrote in message
news:dk**********@lc03.rz.uni-jena.de... Larry Menard wrote:

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?
It doesn't because the operands (parameters) of your function did not take

a constant, special register or host variable when you executed the
statement.

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

Just type something in there that works.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Larry Menard]

Hi, Matt.

Already tried that:

SELECT iam0.g_itemId FROM g2_ItemAttributesMap AS iam0, g2_ItemAttributesMap AS
iam1 WHERE iam1.g_parentSequence LIKE g2_concat_like (cast('ljm' as char(3)))

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

I just keep swapping back & forth between the two errors: SQL0132 and SQL0440.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Matt Emmerton" <me******@nospam.yahoo.com> wrote in message news:e5******************************@rogers.com.. .

You need to cast the parameter explicitly -- ... LIKE
g2_contact_like(cast('ljm') as char(3)).

--
Matt
"Larry Menard" <ro**@GoSpamYourself.com> wrote in message
news:C_********************@rogers.com...
Even though the function creation is successful it says it can't find the
function at runtime:

drop function g2_concat_like (char())
DB20000I The SQL command completed successfully.

create function g2_concat_like (in_char char(3)) returns char(50)
begin atomic
declare retval char(50);
set retval = 'some 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 ('ljm')
SQL0440N No authorized routine named "G2_CONCAT_LIKE" of type "FUNCTION"
having compatible arguments was found. SQLSTATE=42884

Just type something in there that works

Allow me to re-phrase my observation... *Nothing* works.
--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"


"Knut Stolze" <st****@de.ibm.com> wrote in message
news:dk**********@lc03.rz.uni-jena.de...

Larry Menard wrote:

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?

It doesn't because the operands (parameters) of your function did not take

a

constant, special register or host variable when you executed the
statement.

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

Just type something in there that works.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Serge Rielau]

Larry Menard wrote:

Hi, Matt.

Already tried that:
SELECT iam0.g_itemId FROM g2_ItemAttributesMap AS iam0,
g2_ItemAttributesMap AS
iam1 WHERE iam1.g_parentSequence LIKE g2_concat_like (cast('ljm' as
char(3)))

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
I just keep swapping back & forth between the two errors: SQL0132 and
SQL0440.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Matt Emmerton" <me******@nospam.yahoo.com
<mailto:me******@nospam.yahoo.com>> wrote in message
news:e5******************************@rogers.com.. .

> You need to cast the parameter explicitly -- ... LIKE
> g2_contact_like(cast('ljm') as char(3)).
>
> --
> Matt
> "Larry Menard" <ro**@GoSpamYourself.com

<mailto:ro**@GoSpamYourself.com>> wrote in message

> news:C_********************@rogers.com...
> Even though the function creation is successful it says it can't

find the

> function at runtime:
>
> drop function g2_concat_like (char())
> DB20000I The SQL command completed successfully.
>
> create function g2_concat_like (in_char char(3)) returns char(50)
> begin atomic
> declare retval char(50);
> set retval = 'some 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 ('ljm')
> SQL0440N No authorized routine named "G2_CONCAT_LIKE" of type "FUNCTION"
> having compatible arguments was found. SQLSTATE=42884
>
>

>> Just type something in there that works

>
> Allow me to re-phrase my observation... *Nothing* works.
> --
> --------------------
> Larry Menard
> "Defender of Geese and of All Things Natural"
>
>
> "Knut Stolze" <st****@de.ibm.com <mailto:st****@de.ibm.com>> wrote in

message

> news:dk**********@lc03.rz.uni-jena.de...

>> Larry Menard wrote:
>>
>>> 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?
>>
>> It doesn't because the operands (parameters) of your function did not take

> a

>> constant, special register or host variable when you executed the
>> statement.
>>
>>> (I'm trying to download the MTK but IBM's web site is not

accepting my >>> postal code. Sigh...)
>>
>> Just type something in there that works.
>>
>> --
>> Knut Stolze
>> DB2 Information Integration Development
>> IBM Germany

>
>

SQL Functions look like sbqueries. The docs have room for improvements.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Larry Menard]

Thanks for everyone's help.

I hope it will be taken as constructive input when I suggest that IBM
might be in a better position to implement the required 'improvements' if
their executives and bean-counters concentrated less on trimming off their
own arms and legs and more on improving the product.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3t************@individual.net...

Larry Menard wrote:

Hi, Matt.
Already tried that:
SELECT iam0.g_itemId FROM g2_ItemAttributesMap AS iam0,
g2_ItemAttributesMap AS
iam1 WHERE iam1.g_parentSequence LIKE g2_concat_like (cast('ljm' as
char(3)))

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

I just keep swapping back & forth between the two errors: SQL0132 and
SQL0440.

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Matt Emmerton" <me******@nospam.yahoo.com
<mailto:me******@nospam.yahoo.com>> wrote in message
news:e5******************************@rogers.com.. .

> You need to cast the parameter explicitly -- ... LIKE
> g2_contact_like(cast('ljm') as char(3)).
>
> --
> Matt
> "Larry Menard" <ro**@GoSpamYourself.com

<mailto:ro**@GoSpamYourself.com>> wrote in message

> news:C_********************@rogers.com...
> Even though the function creation is successful it says it can't

find the

> function at runtime:
>
> drop function g2_concat_like (char())
> DB20000I The SQL command completed successfully.
>
> create function g2_concat_like (in_char char(3)) returns char(50)
> begin atomic
> declare retval char(50);
> set retval = 'some 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 ('ljm')
> SQL0440N No authorized routine named "G2_CONCAT_LIKE" of type

"FUNCTION"

> having compatible arguments was found. SQLSTATE=42884
>
>
>> Just type something in there that works
>
> Allow me to re-phrase my observation... *Nothing* works.
> --
> --------------------
> Larry Menard
> "Defender of Geese and of All Things Natural"
>
>
> "Knut Stolze" <st****@de.ibm.com <mailto:st****@de.ibm.com>> wrote in

message

> news:dk**********@lc03.rz.uni-jena.de...
>> Larry Menard wrote:
>>
>>> 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?
>>
>> It doesn't because the operands (parameters) of your function did not

take

> a
>> constant, special register or host variable when you executed the
>> statement.
>>
>>> (I'm trying to download the MTK but IBM's web site is not

accepting my

>>> postal code. Sigh...)
>>
>> Just type something in there that works.
>>
>> --
>> Knut Stolze
>> DB2 Information Integration Development
>> IBM Germany
>
>

SQL Functions look like sbqueries. The docs have room for improvements.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

Larry Menard wrote:

Thanks for everyone's help.

I hope it will be taken as constructive input when I suggest that IBM
might be in a better position to implement the required 'improvements' if
their executives and bean-counters concentrated less on trimming off their
own arms and legs and more on improving the product.

Since when do executives read newsgroups, Larry?
You talking to the legs man.

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

[Larry Menard]

I hear you, Serge, and I do appreciate the help you guys offer. (Remember I did it myself for quite a while too. ;-)

I've notified the IBM "ERC Helpdesk" about the problem with updating my profile, so I still haven't been able to download the MTK. However, I think I might not need it. I might have found a solution. Instead of:

AND iam1.g_parentSequence LIKE iam0.g_parentSequence || iam0.g_itemId || '/%'

... this seems to work:

AND substr(iam1.g_parentSequence, 1, length(iam0.g_parentSequence) + 5) =
iam0.g_parentSequence
|| cast(iam0.g_itemId as char(4))
|| '/%'

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Serge Rielau" <sr*****@ca.ibm.com> wrote in message news:3t************@individual.net...

Larry Menard wrote:

Thanks for everyone's help.

I hope it will be taken as constructive input when I suggest that IBM
might be in a better position to implement the required 'improvements' if
their executives and bean-counters concentrated less on trimming off their
own arms and legs and more on improving the product.

Since when do executives read newsgroups, Larry?
You talking to the legs man.

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

[Larry Menard]

Actually, an even better solution, in case anyone else is wondering:

AND LOCATE(iam0.g_parentSequence || CHAR(iam0.g_itemId) || '/',
iam1.g_parentSequence,
1) > 0

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Larry Menard" <ro**@GoSpamYourself.com> wrote in message news:AL******************************@rogers.com.. .
I hear you, Serge, and I do appreciate the help you guys offer. (Remember I did it myself for quite a while too. ;-)

I've notified the IBM "ERC Helpdesk" about the problem with updating my profile, so I still haven't been able to download the MTK. However, I think I might not need it. I might have found a solution. Instead of:

AND iam1.g_parentSequence LIKE iam0.g_parentSequence || iam0.g_itemId || '/%'

... this seems to work:

AND substr(iam1.g_parentSequence, 1, length(iam0.g_parentSequence) + 5) =
iam0.g_parentSequence
|| cast(iam0.g_itemId as char(4))
|| '/%'

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Serge Rielau" <sr*****@ca.ibm.com> wrote in message news:3t************@individual.net...

Larry Menard wrote:

Thanks for everyone's help.

I hope it will be taken as constructive input when I suggest that IBM
might be in a better position to implement the required 'improvements' if
their executives and bean-counters concentrated less on trimming off their
own arms and legs and more on improving the product.

Since when do executives read newsgroups, Larry?
You talking to the legs man.

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