Re: Help with SQL

I'm an idiot.  thanks Peace,Lyall

  ----- Original Message -----
  From: "Daniel Fink"
  To: lyallbarbour@xxxxxxxxxxxxxxx
  Subject: Re: Help with SQL
  Date: Mon, 30 Jun 2008 10:59:48 -0600


  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
  >

-- 
Be Yourself @ mail.com!
Choose From 200+ Email Addresses
Get a Free Account at www.mail.com

Other related posts: