stopping Oracle seeing a field name as a function [message #303985] |
Mon, 03 March 2008 10:46  |
matpj
Messages: 115 Registered: March 2006 Location: London, UK
|
Senior Member |
|
|
One of my tables has a field called to_date.
when I try and use it in an expression I have problems (obviously as to_date is an Oraclce function)
is there a way of preventing Oracle seeing it as a function.
tha particular line in my code is:
nbir.to_date >= tp.prstart and
nbir.to_date <= tp.prfinish-1
can anybody suggest anything?
thanks in advance,
Matt
|
|
|
|
|
Re: stopping Oracle seeing a field name as a function [message #303989 is a reply to message #303985] |
Mon, 03 March 2008 11:00   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
Well for a start it is an astoundingly bad idea to name a column after a function (or any other object for that matter) but let's assume that you had no hand in this matter for the moment.
Are you saying that Oracle is actually throwing an error and thereby stopping you from performing your query?
Not a problem on my system (other than the fact that it is a stupid idea )
SQL> drop table idiotic_name;
Table dropped.
SQL> drop table foo;
Table dropped.
SQL>
SQL> create table idiotic_name(to_date date, lname varchar2(30));
Table created.
SQL> create table foo(start_date date, end_date date, val number);
Table created.
SQL> insert into idiotic_name (to_date, lname)
2 values (sysdate-2, 'James');
1 row created.
SQL> insert into idiotic_name (to_date, lname)
2 values (sysdate, 'Turner');
1 row created.
SQL> insert into foo(start_date, end_date, val)
2 values(sysdate-3, sysdate -1, 1);
1 row created.
SQL> insert into foo(start_date, end_date, val)
2 values(sysdate, sysdate +2, 2);
1 row created.
SQL>
SQL> select *
2 from idiotic_name
3 join foo
4 on to_date >= start_date and to_date <= end_date;
TO_DATE LNAME START_DAT END_DATE VAL
--------- ------------------------------ --------- --------- ----------
01-MAR-08 James 29-FEB-08 02-MAR-08 1
03-MAR-08 Turner 03-MAR-08 05-MAR-08 2
SQL> select banner from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
|
|
|
|
Re: stopping Oracle seeing a field name as a function [message #303999 is a reply to message #303985] |
Mon, 03 March 2008 11:41  |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
You need to post your Oracle version and a copy and paste of a reproducible test case, complete with the error or whatever results you are getting. In addition to the tests that the others have already provided, I have used your exact syntax below with no error. It may be that the problem is not caused by what you think.
SCOTT@orcl_11g> SELECT banner FROM v$version
2 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
SCOTT@orcl_11g> CREATE TABLE nbir
2 (to_date DATE)
3 /
Table created.
SCOTT@orcl_11g> INSERT INTO nbir VALUES (SYSDATE)
2 /
1 row created.
SCOTT@orcl_11g> CREATE TABLE tp
2 (prstart DATE,
3 prfinish DATE)
4 /
Table created.
SCOTT@orcl_11g> INSERT INTO tp VALUES (SYSDATE - 1, SYSDATE + 1)
2 /
1 row created.
SCOTT@orcl_11g> SELECT *
2 FROM nbir, tp
3 WHERE nbir.to_date >= tp.prstart
4 and nbir.to_date <= tp.prfinish-1
5 /
TO_DATE PRSTART PRFINISH
--------- --------- ---------
03-MAR-08 02-MAR-08 04-MAR-08
SCOTT@orcl_11g>
|
|
|