Linker IT Software
Google
Web www.oraxcel.com
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
ORA-01858 and date parameters (Read 4390 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
ORA-01858 and date parameters
13.09.06 at 21:20:44
 
ORA-01858 and date parameters
 
Derrick from the USA asked the following question:
 
Question:
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 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.  
 
Answer:
You give the answer yourself! You are missing the single quotes.
 
When you use substitution variables as you are the values in the parameters are simply substituted in your SQL statement. You should have used: actiontime >= to_date('&I2', 'mm/dd/yyyy')  
 
You can also use the other type of parameter, bind parameters.  It would be sufficient to use: actiontime >= :I2
 
Examples:
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);  
 
or with bind variables
 
select  count(distinct log.cupid) as Count from knowledge.log, knowledge.userbase  
where actiontime >= :I2
and actiontime <= :I3
and userbase.cupid = log.CUPID    
and admcode=:C6  
and (AFFLTNCODE=001);
Back to top
 
 

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