Concatenation
in [DB2]
|
From: Larry Menard on 8 Nov 2005 17:37 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"
From: Bob Stearns on 8 Nov 2005 18:20 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?
From: Matt Emmerton on 8 Nov 2005 20:14 "Bob Stearns" <rstearns1241(a)charter.net> wrote in message news:enacf.61780$RG4.1798(a)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/infocenter/db2help/topic/com.ibm.db2.udb.doc/admin/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
From: Larry Menard on 8 Nov 2005 20:51 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(a)nospam.yahoo.com> wrote in message news:VcudnaHCLdbf0OzenZ2dnUVZ_vmdnZ2d(a)rogers.com... > > "Bob Stearns" <rstearns1241(a)charter.net> wrote in message > news:enacf.61780$RG4.1798(a)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/infocenter/db2help/topic/com.ibm.db2.udb.doc/admin/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 > > >
From: Bob Stearns on 9 Nov 2005 00:55
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. |