Concatenation
in [DB2]
|
From: Larry Menard on 10 Nov 2005 16:33 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" <srielau(a)ca.ibm.com> wrote in message news:3thlt4Fs634bU1(a)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" <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
From: Serge Rielau on 10 Nov 2005 18:22 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
From: Larry Menard on 10 Nov 2005 19:45 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" <srielau(a)ca.ibm.com> wrote in message news:3ti31vFsudckU1(a)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
From: Larry Menard on 11 Nov 2005 21:44
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" <root(a)GoSpamYourself.com> wrote in message news:ALWdne0pmJYQdO7enZ2dnUVZ_sudnZ2d(a)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" <srielau(a)ca.ibm.com> wrote in message news:3ti31vFsudckU1(a)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 |