Concatenation
in [DB2]
|
From: Larry Menard on 9 Nov 2005 08:14 Ah, thanks again. -- -------------------- Larry Menard "Defender of Geese and of All Things Natural" "Bob Stearns" <rstearns1241(a)charter.net> wrote in message news:s9gcf.61$Ae3.37(a)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.
From: Larry Menard on 9 Nov 2005 21:35 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(a)GoSpamYourself.com> wrote in message news:CI2dnb2u-9vQa-zenZ2dnUVZ_sudnZ2d(a)rogers.com... > Ah, thanks again. > > -- > -------------------- > Larry Menard > "Defender of Geese and of All Things Natural" > > > "Bob Stearns" <rstearns1241(a)charter.net> wrote in message > news:s9gcf.61$Ae3.37(a)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. > >
From: Knut Stolze on 10 Nov 2005 05:50 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/developerworks/db2/library/techarticle/0301stolze/0301stolze.html -- Knut Stolze DB2 Information Integration Development IBM Germany
From: Serge Rielau on 10 Nov 2005 07:55 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(a)GoSpamYourself.com > <mailto:root(a)GoSpamYourself.com>> wrote in message > news:CI2dnb2u-9vQa-zenZ2dnUVZ_sudnZ2d(a)rogers.com... > > Ah, thanks again. > > > > -- > > -------------------- > > Larry Menard > > "Defender of Geese and of All Things Natural" > > > > > > "Bob Stearns" <rstearns1241(a)charter.net > <mailto:rstearns1241(a)charter.net>> wrote in message > > news:s9gcf.61$Ae3.37(a)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
From: Larry Menard on 10 Nov 2005 11:44
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(a)ca.ibm.com> wrote in message news:3tgu9jFsu55gU2(a)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(a)GoSpamYourself.com >> <mailto:root(a)GoSpamYourself.com>> wrote in message >> news:CI2dnb2u-9vQa-zenZ2dnUVZ_sudnZ2d(a)rogers.com... >> > Ah, thanks again. >> > >> > -- >> > -------------------- >> > Larry Menard >> > "Defender of Geese and of All Things Natural" >> > >> > >> > "Bob Stearns" <rstearns1241(a)charter.net >> <mailto:rstearns1241(a)charter.net>> wrote in message >> > news:s9gcf.61$Ae3.37(a)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 |