Error 9700 - With RTRIM routine ( IDS11.50 / Win2k

Hi, I have third party application that generates the following sql script and stops with error -9700 when tring to run it : " SELECT UNIQUE 'AUT',rtrim(concat(rtrim(age),concat(rtrim(dev),concat(rtrim(ncp), concat(rtrim(suf),concat(rtrim(naut),rtrim(nord))))))) FROM BKAUTC WHERE (age='84001' AND dev='995' AND ncp='23511400801' AND suf=' ') AND AGE ='84001' AND AGE !='84002'" when I try it manually on dbaccess I still have : " 9700: Routine (rtrim) ambiguous - more than one routine resolves togiven signature. Error in line 2Near character position 57 " Please, help ! How can this be fixed ? Best regards, HM

Use type casting si the engine knows which version of rtrim to use. Art On Mar 1, 2013 12:22 PM, "HM-NET" <h.moukouri@moukouri.net> wrote: > Hi, > > I have third party application that generates the following sql script > and stops with error -9700 > when tring to run it : > > " > SELECT UNIQUE > 'AUT',rtrim(concat(rtrim(age),concat(rtrim(dev),concat(rtrim(ncp), > concat(rtrim(suf),concat(rtrim(naut),rtrim(nord))))))) > FROM BKAUTC > WHERE (age='84001' AND dev='995' AND ncp='23511400801' AND suf=' ') > AND AGE ='84001' > AND AGE !='84002'> " > > when I try it manually on dbaccess I still have : > > " > 9700: Routine (rtrim) ambiguous - more than one routine resolves to> given signature. > Error in line 2> Near character position 57 > " > > Please, help ! How can this be fixed ? > > Best regards, > HM > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f46d0408395d2d1e3a04d6e06b59

If this is a third party app and he has no control over the SQL being generated, is there anything he can do? Also, got to love code generated SQL. That third party app really wants to make sure the age is '84001' and just for good measure, better check to make sure age != '84002' just in case '84002' == '84001' evaluated to true! Andrew -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Friday, March 01, 2013 11:35 AM To: ids@iiug.org Subject: Re: Error 9700 - With RTRIM routine ( IDS11.50.... [29652] Use type casting si the engine knows which version of rtrim to use. Art On Mar 1, 2013 12:22 PM, "HM-NET" <h.moukouri@moukouri.net> wrote: > Hi, > > I have third party application that generates the following sql script > and stops with error -9700 when tring to run it : > > " > SELECT UNIQUE > 'AUT',rtrim(concat(rtrim(age),concat(rtrim(dev),concat(rtrim(ncp), > concat(rtrim(suf),concat(rtrim(naut),rtrim(nord))))))) > FROM BKAUTC > WHERE (age='84001' AND dev='995' AND ncp='23511400801' AND suf=' ') > AND AGE ='84001' > AND AGE !='84002'> " > > when I try it manually on dbaccess I still have : > > " > 9700: Routine (rtrim) ambiguous - more than one routine resolves to> given signature. > Error in line 2> Near character position 57 > " > > Please, help ! How can this be fixed ? > > Best regards, > HM > > > > **************************************************************************** *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f46d0408395d2d1e3a04d6e06b59 **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Hi, There was a hint on dbaccess error. Removing the last rtrim (before nord) seems to solve the problem. I think I'll suggest this to the App developer. Best regards, HM Le 3/1/2013 18:22, HM-NET a écrit : > Hi, > > I have third party application that generates the following sql script > and stops with error -9700 > when tring to run it : > > " > SELECT UNIQUE > 'AUT',rtrim(concat(rtrim(age),concat(rtrim(dev),concat(rtrim(ncp), > concat(rtrim(suf),concat(rtrim(naut),rtrim(nord))))))) > FROM BKAUTC > WHERE (age='84001' AND dev='995' AND ncp='23511400801' AND suf=' ') > AND AGE ='84001' > AND AGE !='84002'> " > > when I try it manually on dbaccess I still have : > > " > 9700: Routine (rtrim) ambiguous - more than one routine resolves to> given signature. > Error in line 2> Near character position 57 > " > > Please, help ! How can this be fixed ? > > Best regards, > HM > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Unless the app, like Cognos, YellowFin, etc. allows him to modify the generated query? Hmm, query a VIEW with the underlying columns all cast to CHAR types instead! If the app insists on this tablename, rename the table and replace it with the VIEW. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Mar 1, 2013 at 12:53 PM, Andrew Ford <andrew@informix-dba.com>wrote: > If this is a third party app and he has no control over the SQL being > generated, is there anything he can do? > > Also, got to love code generated SQL. > > That third party app really wants to make sure the age is '84001' and just > for good measure, better check to make sure age != '84002' just in case > '84002' == '84001' evaluated to true! > > Andrew > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art > Kagel > Sent: Friday, March 01, 2013 11:35 AM > To: ids@iiug.org > Subject: Re: Error 9700 - With RTRIM routine ( IDS11.50.... [29652] > > Use type casting si the engine knows which version of rtrim to use. > > Art > On Mar 1, 2013 12:22 PM, "HM-NET" <h.moukouri@moukouri.net> wrote: > > > Hi, > > > > I have third party application that generates the following sql script > > and stops with error -9700 when tring to run it : > > > > " > > SELECT UNIQUE > > 'AUT',rtrim(concat(rtrim(age),concat(rtrim(dev),concat(rtrim(ncp), > > concat(rtrim(suf),concat(rtrim(naut),rtrim(nord))))))) > > FROM BKAUTC > > WHERE (age='84001' AND dev='995' AND ncp='23511400801' AND suf=' ') > > AND AGE ='84001' > > AND AGE !='84002'> > " > > > > when I try it manually on dbaccess I still have : > > > > " > > 9700: Routine (rtrim) ambiguous - more than one routine resolves to> > given signature. > > Error in line 2> > Near character position 57 > > " > > > > Please, help ! How can this be fixed ? > > > > Best regards, > > HM > > > > > > > > > > **************************************************************************** > *** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --f46d0408395d2d1e3a04d6e06b59 > > > **************************************************************************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec554dbc86ed4ab04d6e10163