学习代码查询

--column day2 new_value 2 noprint;

--SELECT to_char(sysdate-1,'yyyymmdd') day2 from dual;

--column day6 new_value 6 noprint

--select store_name||'店' day6 from store_queue where store_no=&&1;

set linesize 300

--set colsep |;

set feedback off;

set trimout off;

set trimspool on;

set recsep off;

set underline off;

set feed off;

set feedback off;

set verify off

set pagesize 10000

col cdate noprint new_value cdate

col c0 hea '采购' for a10

col c18 hea '大组' for 9999

col c19 hea '小组' for 9999

col c1 hea '货号' for 99999999999

col c00 hea 'DM快讯' for 9999

col c2 hea '商品描述' for a40

col c3 hea '含税售价' for 99999.99

col c4 hea 'DMS' for 9999.99

col c5 hea '库存数量' for 99999.99

col c6 hea '库存天数' for 99999

col c7 hea '最后销售日期' for a10

col c8 hea '最后到货日期' for a10

col c9 hea '停销天数' for 9999

col c10 hea '在线订货量' for 99999.99

col c11 hea '供应商号' for 9999999999

col c12 hea '描述' for a60

col c13 hea '楼面主管反馈' for a10

col c14 hea '采购反馈' for a10

col c15 hea '商品状态' for a10

col c16 hea '是否KVI' for a10

col c17 hea '商品类型' for a20

spool zxp.prn

select

b.group_name c0,

a.supplier c11,

--s.second_supp_name c12,

--decode(a.mmail_no,null,0,a.mmail_no) c00,

a.item c1,

a.descr c2,

a.unit_retail c3,

--a.dms c4,

a.stock_on_hand c5,

--a.stock_on_hand/a.dms c6,

--avg(a.stock_on_hand)/avg(a.dms) c6,

a.status c15,

--a.kvi_ind c16,

a.item_type c17,

round(sysdate-a.last_sold) c9,

--a.on_order c10,

to_char(a.last_sold,'YYYY-MM-DD') c7,

decode(to_char(a.last_received,'YYYY-MM-DD'),null,'0000-00-00',to_char(a.last_received,'YYYY-MM-DD')) c8

from

erp_item_loc a,

groups b,

erp_supplier_loc s

where

a.group_no=b.group_no and

a.supplier=s.second_supp and

--a.status='A' and

a.location in(&1) and

a.subclass!=113 and

a.location=s.location and

--and a.status='A'

--and a.status='C'

--a.item_type='普通商品' and

--a.item_type='联营商品' and

--a.supplier in(21011454) and

--a.group_no in(301,302,303,304,305,306,307,308,309,310)

--and a.stock_on_hand<>0

a.stock_on_hand!=0

--and a.supplier not in(21000103,21013587,21013968,21018426)

--and a.stock_on_hand>=0

--and a.item in(select item from ordloc where location=100200 and estimated_instock_date)

and (sysdate-a.last_sold>=90 and a.last_received is not null)

--and (sysdate-a.last_sold>=90)

--and a.last_sold is not null

group by

a.group_no,

b.group_name,

a.item,

a.descr,

a.stock_on_hand,

a.status,

--a.kvi_ind,

a.item_type,

a.supplier,

--s.second_supp_name,

a.unit_retail,

--a.dms,

a.last_sold,

a.on_order,

a.last_received

order by

a.group_no,c11;

spool off;

exit;