Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Frustration...need help resolving ORA-01858 error (Read 5882 times)
YaBB Newbies


Posts: 1
Frustration...need help resolving ORA-01858 error
28.08.06 at 17:35:39
I am performing an evaluation of this software for my department and I am getting very frustrated with a particular query that I keep trying to run.   I need to get data from an oracle database using a YTD and Last 12 Months date range each month, for a particular business unit code.  I have entered the start dates and codes into excel and wish to reference these in my query, so the person running this will only have to update the start date and end date accordingly.  Here is my query
select  count(distinct log.cupid) as Count from knowledge.log, knowledge.userbase  
where actiontime >= to_date(&I2, 'mm/dd/yyyy')  
and actiontime <= to_date(&I3, 'mm/dd/yyyy')    
and userbase.cupid = log.CUPID  
and admcode=&C6
and (AFFLTNCODE=001);
I2  - Cell reference for the start date
I3  - Cell reference for the end date
C6 - Cell reference for the admin code
When I run this query I keep getting the following error
"ORA-01858: a non-numeric character was found where a numeric was expected"
I have attached a copy of the statement, I can see that the query is being built but the todate conversion doesn't add single quotes around the date value.  Please help, I am so close, I'm sure its some slight configuration issue in my syntax.
I have attached a copy of the error message.
Back to top

  IP Logged
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Re: Frustration...need help resolving ORA-01858 er
Reply #1 - 29.08.06 at 19:45:28
Sorry about your frustration. It may be a little tricky. To not make it more difficult I have adhered to how things work in Oracle's SQL*Plus. Please understand the differences between bind (e.g. :A1) and substitution variables (e.g. &A1). I am sure you will get along fine when knowing the differences.
Substitution variables: e.g. &A1
These are string replacement variables. SQL*XL will look at the value in the variable or if it is a cell it will use the contents of the cell. SQL*XL will string replace the value for the variable name. So if there is the value 1/Jan/2006 in A1 and your query is: select * from mytable where col = &A1, the following query is sent to the database:
select * from mytable where col = 1/Jan/2006.
In your example you are missing out on the single quotes around the subst variable. You should have used:
select * from mytable where col = to_date('&A1','dd/mon/yyyy')
When doing the substitution there is one thing you still have no control over. If A1 contains a date, SQL*XL will use the VB standard to convert to date. This is the date setting on your PC. If your dates look like mmm/dd/yy it will come out as Jan/01/06. Beware of that. To be 100% sure don't use a date in A1 but a string. You type a string in Excel by prefixing the value with a single quote, e.g.: '01/Jan/2006
To not be dependent on formatting which is troublesome in the case of dates especially, there is the bind variable. I suggest you give that a try.
A bind variable (e.g. :A1) binds the query to value. The query is sent to the database with the variables and their values separate so the database receives the value with data type.
select * from mytable where my_date_col = :A1;
I can now just type a date in A1 and it will work. The database will see A1 as a date and will not need to do any implicit conversions.  
The same applies to numbers and texts.
Hope this helps.
Back to top
« Last Edit: 29.08.06 at 19:51:12 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1