Help with SQL

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",
  7         decode(to_char(pl.curr_prom_dt,'mm/yyyy'),
  8                    to_char(add_months(sysdate, -1), 'mm/yyyy'), 
to_char(pl.qty_ord*iim.ex_whse_cost),
  9                    '0') "LastMonth",
 10         decode(to_char(pl.curr_prom_dt,'mm/yyyy'),
 11                    to_char(add_months(sysdate, -2), 'mm/yyyy'), 
to_char(pl.qty_ord*iim.ex_whse_cost),
 12                    '0') "2MonthsPrior",
 13         decode(to_char(pl.curr_prom_dt,'mm/yyyy'),
 14                    to_char(add_months(sysdate, -3), 'mm/yyyy'), 
to_char(pl.qty_ord*iim.ex_whse_cost),
 15                    '0') "3MonthsPrior",
 16         decode(to_char(pl.curr_prom_dt,'mm/yyyy'),
 17                    to_char(add_months(sysdate, -4), 'mm/yyyy'), 
to_char(pl.qty_ord*iim.ex_whse_cost),
 18                    '0') "4MonthsPrior",
 19         decode(to_char(pl.curr_prom_dt,'mm/yyyy'),
 20                    to_char(add_months(sysdate, -5), 'mm/yyyy'), 
to_char(pl.qty_ord*iim.ex_whse_cost),
 21                    '0') "5MonthsPrior"
 22  from po_line pl, po_hdr ph, ic_item_mast iim
 23  where pl.po_cd = ph.po_cd
 24  and   iim.item_cd = pl.item_cd
 25* and   pl.curr_prom_dt > '01-JAN-08') blah
SQL> /
VENDOR
------------------------------
MONDAY IL
.
.Skip a bunch
.  
GUANGXI SANHUAN ENTERPRISE
68 rows selected.
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
  7         decode(to_char(pl.curr_prom_dt,'mm/yyyy'),
  8                    to_char(add_months(sysdate, -1), 'mm/yyyy'), 
to_char(pl.qty_ord*iim.ex_whse_cost),
  9                    '0') "LastMonth",
 10         decode(to_char(pl.curr_prom_dt,'mm/yyyy'),
 11                    to_char(add_months(sysdate, -2), 'mm/yyyy'), 
to_char(pl.qty_ord*iim.ex_whse_cost),
 12                    '0') "2MonthsPrior",
 13         decode(to_char(pl.curr_prom_dt,'mm/yyyy'),
 14                    to_char(add_months(sysdate, -3), 'mm/yyyy'), 
to_char(pl.qty_ord*iim.ex_whse_cost),
 15                    '0') "3MonthsPrior",
 16         decode(to_char(pl.curr_prom_dt,'mm/yyyy'),
 17                    to_char(add_months(sysdate, -4), 'mm/yyyy'), 
to_char(pl.qty_ord*iim.ex_whse_cost),
 18                    '0') "4MonthsPrior",
 19         decode(to_char(pl.curr_prom_dt,'mm/yyyy'),
 20                    to_char(add_months(sysdate, -5), 'mm/yyyy'), 
to_char(pl.qty_ord*iim.ex_whse_cost),
 21                    '0') "5MonthsPrior"
 22  from po_line pl, po_hdr ph, ic_item_mast iim
 23  where pl.po_cd = ph.po_cd
 24  and   iim.item_cd = pl.item_cd
 25* and   pl.curr_prom_dt > '01-JAN-08') blah
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.
  7         decode(to_char(pl.curr_prom_dt,'mm/yyyy'),
  8                    to_char(add_months(sysdate, -1), 'mm/yyyy'), 
to_char(pl.qty_ord*iim.ex_whse_cost),
  9                    '0') "LastMonth",
 10         decode(to_char(pl.curr_prom_dt,'mm/yyyy'),
 11                    to_char(add_months(sysdate, -2), 'mm/yyyy'), 
to_char(pl.qty_ord*iim.ex_whse_cost),
 12                    '0') "2MonthsPrior",
 13         decode(to_char(pl.curr_prom_dt,'mm/yyyy'),
 14                    to_char(add_months(sysdate, -3), 'mm/yyyy'), 
to_char(pl.qty_ord*iim.ex_whse_cost),
 15                    '0') "3MonthsPrior",
 16         decode(to_char(pl.curr_prom_dt,'mm/yyyy'),
 17                    to_char(add_months(sysdate, -4), 'mm/yyyy'), 
to_char(pl.qty_ord*iim.ex_whse_cost),
 18                    '0') "4MonthsPrior",
 19         decode(to_char(pl.curr_prom_dt,'mm/yyyy'),
 20                    to_char(add_months(sysdate, -5), 'mm/yyyy'), 
to_char(pl.qty_ord*iim.ex_whse_cost),
 21                    '0') "5MonthsPrior"
 22  from po_line pl, po_hdr ph, ic_item_mast iim
 23  where pl.po_cd = ph.po_cd
 24  and   iim.item_cd = pl.item_cd
 25* and   pl.curr_prom_dt > '01-JAN-08') blah
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

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


Other related posts: