Concatenation
in [DB2]
|
From: Knut Stolze on 10 Nov 2005 12:08 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
From: Larry Menard on 10 Nov 2005 12:39 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" <stolze(a)de.ibm.com> wrote in message news:dkvuqa$utp$1(a)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
From: Matt Emmerton on 10 Nov 2005 13:52 You need to cast the parameter explicitly -- ... LIKE g2_contact_like(cast('ljm') as char(3)). -- Matt "Larry Menard" <root(a)GoSpamYourself.com> wrote in message news:C_qdnYO-R4siGO7eRVn-pA(a)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" <stolze(a)de.ibm.com> wrote in message news:dkvuqa$utp$1(a)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
From: Larry Menard on 10 Nov 2005 14:05 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" <memmerto(a)nospam.yahoo.com> wrote in message news:e5OdnQ9GvspHC-7enZ2dnUVZ_v2dnZ2d(a)rogers.com... > You need to cast the parameter explicitly -- ... LIKE > g2_contact_like(cast('ljm') as char(3)). > > -- > Matt > "Larry Menard" <root(a)GoSpamYourself.com> wrote in message > news:C_qdnYO-R4siGO7eRVn-pA(a)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" <stolze(a)de.ibm.com> wrote in message > news:dkvuqa$utp$1(a)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 > >
From: Serge Rielau on 10 Nov 2005 14:38
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" <memmerto(a)nospam.yahoo.com > <mailto:memmerto(a)nospam.yahoo.com>> wrote in message > news:e5OdnQ9GvspHC-7enZ2dnUVZ_v2dnZ2d(a)rogers.com... > > You need to cast the parameter explicitly -- ... LIKE > > g2_contact_like(cast('ljm') as char(3)). > > > > -- > > Matt > > "Larry Menard" <root(a)GoSpamYourself.com > <mailto:root(a)GoSpamYourself.com>> wrote in message > > news:C_qdnYO-R4siGO7eRVn-pA(a)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" <stolze(a)de.ibm.com <mailto:stolze(a)de.ibm.com>> wrote in > message > > news:dkvuqa$utp$1(a)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 |