Home » SQL & PL/SQL » SQL & PL/SQL » pass a value through a query
pass a value through a query [message #212065] Wed, 03 January 2007 12:19 Go to next message
avion
Messages: 11
Registered: December 2006
Junior Member
Hi,

I'm trying to pass a parameter value through a query. I want to return data from the table between two dates. This is what I have:

select * from musicians
where dob = '$value' and dob = '$value'


SQL Error: ORA-01858: a non-numeric character was found where a numeric was expected

I've tried converting the dob to string using to_char but that doesn't work either. If I change the query to a string column then the query works..

Any ideas?

Thanks
Re: pass a value through a query [message #212067 is a reply to message #212065] Wed, 03 January 2007 12:21 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
use BETWEEN & TO_DATE
Re: pass a value through a query [message #212068 is a reply to message #212065] Wed, 03 January 2007 12:21 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Use ampersand &, not dollar $ sign.
SQL> select ename from emp where deptno = &dept_number;
Enter value for dept_number: 10
old   1: select ename from emp where deptno = &dept_number
new   1: select ename from emp where deptno = 10

ENAME
----------
CLARK
KING
MILLER

SQL>


[EDIT] Huh, right ... where did I put that BETWEEN stuff?
SQL> select ename from emp
  2  where hiredate between to_date('&d1', 'dd.mm.yyyy')
  3                     and to_date('&d2', 'dd.mm.yyyy');
Enter value for d1: 01.01.1987
old   2: where hiredate between to_date('&d1', 'dd.mm.yyyy')
new   2: where hiredate between to_date('01.01.1987', 'dd.mm.yyyy')
Enter value for d2: 31.12.1987
old   3:                    and to_date('&d2', 'dd.mm.yyyy')
new   3:                    and to_date('31.12.1987', 'dd.mm.yyyy')

ENAME
----------
SCOTT
ADAMS

SQL>

[Updated on: Wed, 03 January 2007 12:24]

Report message to a moderator

Re: pass a value through a query [message #212075 is a reply to message #212065] Wed, 03 January 2007 12:45 Go to previous message
avion
Messages: 11
Registered: December 2006
Junior Member
Thanks! I was close, but not close enough Smile
Previous Topic: Extract Year
Next Topic: Sql query performance
Goto Forum:
  


Current Time: Thu Dec 08 08:09:34 CST 2016

Total time taken to generate the page: 0.05936 seconds