Re: Help with SQL

From: Daniel Fink <daniel.fink_at_optimaldba.com>
Date: Mon, 30 Jun 2008 10:59:48 -0600
Message-ID: <48691104.1010705@optimaldba.com>

Lyall,

The problem is that CURRENT is an oracle reserved word. You can use it as a literal (like "Current" for a column heading), but you cannot use it within a statement where it is referenced.

SQL> select sysdate "Current" from dual;

Current

---

30-JUN-08
SQL> select sysdate current from dual;
select sysdate current from dual

               *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

Just give it a slightly different name and it will work

SQL> edit
Wrote file afiedt.buf

  1* select sysdate current_date from dual SQL> / CURRENT_D

---

30-JUN-08

-- 
Daniel Fink

Oracle Performance, Diagnosis, Data Recovery and Training

OptimalDBA    http://www.optimaldba.com
Oracle Blog   http://optimaldba.blogspot.com

Lost Data?    http://www.ora600.nl/introduction.htm



Lyall Barbour wrote:


> Sorry all,

>   Been awhile since i've coded.  Hopefully, someone will see my error right away, or, maybe, just know a better way to do this.

>   I want to calculate up the monies for monthly "buckets" and i am getting it with the SQL below, but the column names in the view are giving me problem

>

> Wrote file afiedt.buf

>   1  Select blah.vendor

>   2  from (

>   3  select ph.vndr_name Vendor,  <------------------------------------------ WORKS GOOD

>   4         decode(to_char(pl.curr_prom_dt,'mm/yyyy'),

>   5                    to_char(sysdate, 'mm/yyyy'), to_char(pl.qty_ord*iim.ex_whse_cost),

>   6                    '0') "Current",

>   




> SQL> ed

> Wrote file afiedt.buf

>   1  Select blah.vendor, blah.current

>   2  from (

>   3  select ph.vndr_name Vendor,

>   4         decode(to_char(pl.curr_prom_dt,'mm/yyyy'),

>   5                    to_char(sysdate, 'mm/yyyy'), to_char(pl.qty_ord*iim.ex_whse_cost),

>   6                    '0') "Current",  <-- CAN'T SELECT THIS COLUMN

> SQL> /

> Select blah.vendor, blah.current

>                          *

> ERROR at line 1:

> ORA-01747: invalid user.table.column, table.column, or column specification

>

> SQL> ed

> Wrote file afiedt.buf

>   1  Select blah.vendor, blah.current

>   2  from (

>   3  select ph.vndr_name Vendor,

>   4         decode(to_char(pl.curr_prom_dt,'mm/yyyy'),

>   5                    to_char(sysdate, 'mm/yyyy'), to_char(pl.qty_ord*iim.ex_whse_cost),

>   6                    '0') Current,  <-- TRY TO MAKE IT LIKE vendor ABOVE AND IT'S ... WELL ... GAY FOR LACK OF A BETTER DESCRIPTION IN MY FRUSTRATED STATE.

> SQL> /

> Select blah.vendor, blah.current

>                          *

> ERROR at line 1:

> ORA-01747: invalid user.table.column, table.column, or column specification

>

> SQL> 

>

> Thanks in advance for your help.

>

> Lyall

>   


--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jun 30 2008 - 11:59:48 CDT