Home » SQL & PL/SQL » SQL & PL/SQL » stopping Oracle seeing a field name as a function
stopping Oracle seeing a field name as a function [message #303985] Mon, 03 March 2008 10:46 Go to next message
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 #303986 is a reply to message #303985] Mon, 03 March 2008 10:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Rename your column.

Regards
Michel
Re: stopping Oracle seeing a field name as a function [message #303987 is a reply to message #303985] Mon, 03 March 2008 10:57 Go to previous messageGo to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

unfortunately, it is a system generated column (part of Clarity's scheduled jobs) Sad
Re: stopping Oracle seeing a field name as a function [message #303989 is a reply to message #303985] Mon, 03 March 2008 11:00 Go to previous messageGo to next message
pablolee
Messages: 2834
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 Wink )
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 #303990 is a reply to message #303987] Mon, 03 March 2008 11:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But to_date works:
SQL> insert into t (to_date) values (sysdate);

1 ligne(s) insérée(s).

SQL> select * from t where to_date <= sysdate;
03/03/08

1 lignes renvoyées 

Regards
Michel

[Edit: I'm not writing fast enough. Wink ]

[Updated on: Mon, 03 March 2008 11:05]

Report message to a moderator

Re: stopping Oracle seeing a field name as a function [message #303999 is a reply to message #303985] Mon, 03 March 2008 11:41 Go to previous message
Barbara Boehmer
Messages: 8620
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> 


Previous Topic: downloading records
Next Topic: ORA-00036: maximum number of r
Goto Forum:
  


Current Time: Fri Dec 02 14:15:19 CST 2016

Total time taken to generate the page: 0.11854 seconds