To_Char Function

Hi, I am having some problems with this TO_CHAR function in my application. The reason i am using TO_CHAR is to get the date which is 2 months before today's date. I tried running my sql statement with TO_CHAR inside dbaccess and it runs well. But when I run my embedded sql statement with TO_CHAR inside my C Coding. It gave me this error: ================================================================================ -674 Routine cannot be resolved. You called a routine that does not exist in the database, you do not have permission to execute the routine, or you called the routine with too few or too many arguments. If a prepared statement invokes a user-defined routine and your application or another application drops the routine before the prepared statement is executed, you will receive this error. You might also see this error message if you write an expression that calls an SPL routine (stored procedure) that returns no values. For an SPL routine to be usable in an expression, the routine must return a value. Check that the name of the routine is correct, that you have execute permission, and that you specified the correct number of arguments to execute a routine. For a prepared statement that refers to the routine, make sure that the routine still exists when you execute the statement. ================================================================================ Anyone have a clue wat is going on? I am using IDS 10.00 FC8 Thanks in advance...

Show the code including the variable declarations. Art On Sat, Mar 21, 2009 at 5:46 AM, RON TAY <rontay79@yahoo.com.sg> wrote: > Hi, > > I am having some problems with this TO_CHAR function in my application. The > reason i am using TO_CHAR is to get the date which is 2 months before > today's > date. > > I tried running my sql statement with TO_CHAR inside dbaccess and it runs > well. > > But when I run my embedded sql statement with TO_CHAR inside my C Coding. > It > gave me this error: > > > > ================================================================================ > -674 Routine cannot be resolved. > > You called a routine that does not exist in the database, you do not have > permission to execute the routine, or you called the routine with too few > or > too many arguments. If a prepared statement invokes a user-defined routine > and > your application or another application drops the routine before the > prepared > statement is executed, you will receive this error. > > You might also see this error message if you write an expression that calls > an > SPL routine (stored procedure) that returns no values. For an SPL routine > to > be usable in an expression, the routine must return a value. > > Check that the name of the routine is correct, that you have execute > permission, and that you specified the correct number of arguments to > execute > a routine. For a prepared statement that refers to the routine, make sure > that > the routine still exists when you execute the statement. > > > ================================================================================ > > Anyone have a clue wat is going on? I am using IDS 10.00 FC8 > > Thanks in advance... > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. --0016364ef07c45a9560465ab4a60

Hi Art, Do you mean sql variables, if yes, I didn't declared any for the sql statment. =) I have included my sql statement tat has a problem. exec sql select column_1 from testdb@ol_test:table_1 where status="Issued" and date_time < TO_CHAR((Current-INTERVAL(2) Month to Month), '%Y%m000000') into temp_table with no log; This query runs ok in IDS 7.31.UC5 but not in our upgradded IDS 10.00.FC8

It's always good to quote the original message/post that you are replying to, at least in part. I had to search through my mail history to find what you were replying to. It's not just you, this is an annoying new trend on these forums. Many of is follow the forums in email not using the forum browsers, and there are enough posts that we do not save ongoing threads. Please quote. Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. Actually, I meant ESQL/C host variables referenced in the SQL, but as you say, this doesn't use any, so... Try this instead: exec sql select column_1 from testdb@ol_test:table_1 where status="Issued" and date_time < TO_CHAR((Current - UNIT(2) Month), '%Y%m000000') into temp_table with no log; Note that this query will still fail on the last day of any month that has more days than the month two months prior (ie April 30, August 31, etc.) since that day is an invalid date for that month. This one is better and will always work: exec sql select column_1 from testdb@ol_test:table_1 where status="Issued" and date_time < to_char( ((year(current) * 100000000) + (month(current) * 1000000) + 000000) ) into temp_table with no log; Art On Sun, Mar 22, 2009 at 9:36 PM, RON TAY <tayjt@stee.stengg.com> wrote: > Hi Art, > > Do you mean sql variables, if yes, I didn't declared any for the sql > statment. > =) > I have included my sql statement tat has a problem. > > exec sql select column_1 from testdb@ol_test:table_1 where status="Issued" > and > date_time < TO_CHAR((Current-INTERVAL(2) Month to Month), '%Y%m000000') > into > temp_table with no log; > > This query runs ok in IDS 7.31.UC5 but not in our upgradded IDS 10.00.FC8 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016364271540da72c0465cacbe9

That was weird. My mailer mucked that up something awful. OK, solution from my last post reposted here: Actually, I meant ESQL/C host variables referenced in the SQL, but as you say, this doesn't use any, so... Try this instead: exec sql select column_1 from testdb@ol_test:table_1 where status="Issued" and date_time < TO_CHAR((Current - UNIT(2) Month), '%Y%m000000') into temp_table with no log; Note that this query will still fail on the last day of any month that has more days than the month two months prior (ie April 30, August 31, etc.) since that day is an invalid date for that month. This one is better and will always work: exec sql select column_1 from testdb@ol_test:table_1 where status="Issued" and date_time < to_char( ((year(current) * 100000000) + (month(current) * 1000000) + 000000) ) into temp_table with no log; Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Sun, Mar 22, 2009 at 9:36 PM, RON TAY <tayjt@stee.stengg.com> wrote: > >> Hi Art, >> >> Do you mean sql variables, if yes, I didn't declared any for the sql >> statment. >> =) >> I have included my sql statement tat has a problem. >> >> exec sql select column_1 from testdb@ol_test:table_1 where >> status="Issued" and >> date_time < TO_CHAR((Current-INTERVAL(2) Month to Month), '%Y%m000000') >> into >> temp_table with no log; >> >> This query runs ok in IDS 7.31.UC5 but not in our upgradded IDS 10.00.FC8 >> >> >> >> ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > --0016364ef1cc5cb58b0465cad23e

That was weird. My mailer mucked that up something awful. OK, solution from my last post reposted here: Actually, I meant ESQL/C host variables referenced in the SQL, but as you say, this doesn't use any, so... Try this instead: exec sql select column_1 from testdb@ol_test:table_1 where status="Issued" and date_time < TO_CHAR((Current - UNIT(2) Month), '%Y%m000000') into temp_table with no log; Note that this query will still fail on the last day of any month that has more days than the month two months prior (ie April 30, August 31, etc.) since that day is an invalid date for that month. This one is better and will always work: exec sql select column_1 from testdb@ol_test:table_1 where status="Issued" and date_time < to_char( ((year(current) * 100000000) + (month(current) * 1000000) + 000000) ) into temp_table with no log; Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) ============================================================================== Hi Art, I think there was some syntax error problem with: select column_1 from testdb@ol_test:table_1 where status="Issued" and date_time < to_char(((year(current) * 100000000) + (month(current) * 1000000)+ 000000)) into temp_table with no log; Anyway, I have managed to solve the problem. Originally, it was using TO_CHAR((Current-INTERVAL(2) Month to Month), '%Y%m000000'), now I have change it to TO_CHAR((TODAY – 2 UNITS Month), '%Y%m000000') and it works. Quite weird to know that the original ones work in IDS 7.31 and not IDS 10.00 :s Lastly, thanks alot for your replies and advices...