The below query works fine when i ran it in sql assistant.
sel cast(to_char(trim(extract( month from <table.date_column>)),'09')||'01'||to_char(trim(extract( year from <table.date_column>)),'0999') as date )
when I run the same query with insert as below in a stored procedure gives error as ' function 'TO_CHAR' does not exist'
insert into table (.............) as
sel (.....,cast(to_char(trim(extract( month from <table.date_column>)),'09')||'01'||to_char(trim(extract( year from <table.date_column>)),'0999') as date, ........ )
Please help me resolving it.
The to_char function is an ODBC extension and is being translated by the client driver.
Something like this should work:
cast(cast(cast(extract( month from table.date_column) as format '99') as CHAR(2)) ||'01'||cast(cast(extract( year from table.date_column) as format '9999') as CHAR(4)) as date format'mmddyyyy')
cast(cast(cast(table.date_column as format 'mmyyyy') as char(6)) as date format 'mmyyyy')
It looks like your system already has the Oracle UDF's installed, as it worked in SQL Assitant.
There may be some permission issues, or you may need to fully qualify the function call, check with your local handy dandy DBA.
TRIM returns VARCHAR. Even if you have TO_CHAR UDF (e.g. TD14), there is no specific function prototype for TO_CHAR that accepts VARCHAR arguments. So remove TRIM and pass the numeric value returned by EXTRACT.
Again, it is working in SQL Assistant only because the SQL you typed is not the SQL that is being sent to the database by the ODBC driver. Try it in BTEQ or in SQL Assistant with .NET provider and it will fail just as it does in the SP.