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
No rows when using parameters (Read 1151 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
No rows when using parameters
14.04.14 at 08:54:13
 
No rows when using parameters
 
When getting no rows from a query (select statement) that includes parameters it is usuful to inspect the data type of the parameter.
 
As an example, a SQL*XL user sent in the following query asking why there are no rows returned:
 
select * from mytable where col_year = :pyear and col_month=:pmonth
 
When running the query with hard coded values the query returns rows and when  using parameters (with values pyear 2014 and pmonth 03) it does not. The hard coded query is
 
select * from mytable where col_year = '2014' and col_month='03'
 
The reason that no rows are returned when using the pyear and pmonth bind variables is that these were used with parameter type of other. The driver assumed numeric data and probably the zero was dropped for the month number. Because a string '03' is different from '3' this may cause the problems.
 
The solution to the problem is to turn the parameter type into string. This forces the driver to take the string as a text and use that as a parameter value.
 
Another solution would be to use substitution variables instead. These can be regarded as string replace parameter. The value will be inserted where the parameter name appears in the SQL.  For example:
 
select * from mytable where col_year = '&pyear' and col_month=':pmonth'
Back to top
 
« Last Edit: 14.04.14 at 08:59:30 by Gerrit-Jan Linker »  

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