Help with SQL
- From: "Lyall Barbour" <lyallbarbour@xxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 30 Jun 2008 11:37:29 -0500
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
