将sql语句包含在select (.....) from dual中,就可以了。
逻辑说明:
单据表体的币制+表头开立日期 关联 汇率维护(原始货币代码、生效日期范围),获取转换率,
然后转换金额至美元后再进行汇总,得到 發票金額(USD) ;若关联不到汇率,则默认以1:1的汇率进行汇总。
获取指定日期最近的数据sql:
MS SQL SERVER, select * from table_name where abs(column_name-convert(datetime,'20081220',112)) in ( select min(abs(column_name-convert(datetime,'20081220',112))) from table_name) ORACLE select * from table_name where abs(column_name-to_date('20081220','yyyymmdd')) in ( select min(abs(column_name-to_date('20081220','yyyymmdd'))) from table_name )oracle中的存储过程:
CREATE OR REPLACE PROCEDURE COST_BILL_INSURE_TOTAL_CALC
/*************************************************************************
*************************进出保险统计表************************************
*************************AUTHOR:XIN***************************************/
(V_EMS_NO VARCHAR2,
V_I_E_MARK VARCHAR2,
V_INPUTER VARCHAR2,
V_REMARK OUT VARCHAR2) AS
BEGIN
--
DELETE FROM AIPTEK_COST_BILL_INSURE_TOTAL T
WHERE T.EMS_NO = V_EMS_NO
AND T.I_E_MARK = V_I_E_MARK;
--
INSERT INTO AIPTEK_COST_BILL_INSURE_TOTAL
(I_E_MARK,
BILL_NO,
INVOIC_NO,
G_NAME,
G_QTY,
G_TOTAL,
INSURE_G_TOTAL,
INSURE_RATE,
INSURE_COST,
TRAF_MODE,
TO_FROM_ADD,
TRADE_MODE,
CUS_CODE,
CREATE_DATE,
START_ADD,
TRAF_TOOLS,
EMS_NO,
INPUT_ER)
SELECT V_I_E_MARK,
M.BILL_NO,
M.INVOIC_NO,
MAX(M.G_NAME),
M.TOTAL_QTY,
SUM(M.G_TOTAL * NVL(I.TAX, 1)) USD,
SUM(M.G_TOTAL * NVL(I.TAX, 1)) * 1.1,
0.00015,
SUM(M.G_TOTAL * NVL(I.TAX, 1)) * 1.1 * 0.00015,
M.TRAF_MODE,
M.TO_FROM_ADD, --目的地
M.TRADE_MODE, --贸易方式
M.CUS_CODE, --报关行
M.CREATE_DATE, --开立日期
'吴江',
'',
V_EMS_NO,
V_INPUTER
FROM AIPTEK_VIEW_BILL_MANAGE M
LEFT JOIN (SELECT T.ORG_CURR, T.EFF_DATE, T.TAX FROM INFO_CORP_TAX T) I ON I.EFF_DATE IN
(SELECT M.CREATE_DATE -
(select min(abs(EFF_DATE -
M.CREATE_DATE))
from INFO_CORP_TAX)
FROM DUAL)
AND M.G_CURR =
I.ORG_CURR
WHERE M.EMS_NO = V_EMS_NO
AND M.I_E_MARK = V_I_E_MARK
AND M.Trade_Mode = '0615'
AND M.ID IS NOT NULL
GROUP BY M.OID,
M.BILL_NO,
M.INVOIC_NO,
M.TOTAL_QTY,
M.TRAF_MODE,
M.TO_FROM_ADD, --目的地
M.TRADE_MODE, --贸易方式
M.CUS_CODE, --报关行
M.CREATE_DATE; --开立日期
COMMIT;
EXCEPTION
WHEN OTHERS THEN
V_REMARK := SQLCODE || SUBSTR(SQLERRM, 1, 200) ||
'ERROR:执行存储过程COST_BILL_INSURE_TOTAL_CALC时出错';
ROLLBACK;
END COST_BILL_INSURE_TOTAL_CALC;