成品尾阶用料查询，还要查出最近采购单价和最低采购单价，还要查指定日期（默认为当天）的采购币别对应的海关汇率。这个工作量，简直难以言欲。而且报表工具还不具被写过程（可以在过程里写变量）的能力，只能写SELECT语句，我又不想写VIEW。。。
耗了今天一天的时间，最让我难以忍受不了的是网络那个叫卡，写程序都是远程登陆到服务器上进行，MD对服务器的ping值忽高忽低，害得我只好慢慢敲键盘。
发现：SYS_CONNECT_BY_PATH原来是不被Oracle9i以下的版本所支持的，下面代码里对尾阶的取法有点牵强，还得想办法把这个给取代掉。
汗...我都不想数用了几层子查询了。。
/* Formatted on 2006/10/28 16:24 (Formatter Plus v4.8.7) */
SELECT b.component_part_no, b.per_qty, b.attrition_rate, b.desp, b.comp_no,
b.part_desp, b.width, b.weight, b.source_code, b.basic_unit_code,
b.curr_code, b.nearest_price, b.min_price, b.exch_rate,
b.per_qty
* (1 + b.attrition_rate)
* b.exch_rate
* b.nearest_price AS ntd_amt_nearest,
b.per_qty
* (1 + b.attrition_rate)
* b.exch_rate
* b.min_price AS ntd_amt_min
FROM (SELECT a.*, asp_part.desp AS part_desp, asp_part.width,
asp_part.weight, asp_part.source_code,
asp_part.basic_unit_code,
jd_pkg_curr_price_exch.f_get_nearest_curr
(a.component_part_no,
a.comp_no
) AS curr_code,
jd_pkg_curr_price_exch.f_get_nearest_price
(a.component_part_no,
a.comp_no
) AS nearest_price,
jd_pkg_curr_price_exch.f_get_min_price
(a.component_part_no,
a.comp_no
) AS min_price,
jd_pkg_curr_price_exch.f_get_exch_by_date
(a.comp_no,
(jd_pkg_curr_price_exch.f_get_nearest_curr
(a.component_part_no,
a.comp_no
)
),
SYSDATE
) AS exch_rate
FROM (SELECT component_part_no, SUM (per_qty) AS per_qty,
NVL (attrition_rate, 0) AS attrition_rate, desp,
comp_no
FROM (SELECT parent_part_no, component_part_no,
item_no, per_qty, attrition_rate, desp,
effective_date, closed_date, comp_no
FROM asp_bom
WHERE SYSDATE BETWEEN effective_date
AND closed_date
AND comp_no = 'JD0201'
CONNECT BY PRIOR component_part_no =
parent_part_no
START WITH parent_part_no = '5201-3220101011')
WHERE component_part_no LIKE '1%'
GROUP BY component_part_no, attrition_rate, desp, comp_no) a,
asp_part
WHERE a.comp_no = asp_part.comp_no(+) AND a.component_part_no = asp_part.code(+)
ORDER BY a.component_part_no) b
| 
写了一超级变态的SQL查询
京ICP备05002321号