数据库里面的数据
编码 说明 数量 单位 单价
AG1002 单孔,斜面铝合金卷笔刀 1 只 0.260342
AG1002 单孔,斜面铝合金卷笔刀 11520 只 0.255214
AG1002 单孔,斜面铝合金卷笔刀 72000 只 0.250085
AG1002 单孔,斜面铝合金卷笔刀 144000 只 0.245043 我想要的报表的输出格式:
AG1002 单孔,斜面铝合金卷笔刀 1 只 0.260342
11520 只 0.255214
72000 只 0.250085
144000 只 0.245043 以下是本人写的,希望哪位高手帮帮我,
create or replace package body BFQUOTE is V_SEPERATE VARCHAR2(2) := CHR(9);
PROCEDURE PRO_MAIN(ERRBUF OUT VARCHAR2,
RETCODE OUT NUMBER,
P_ORG_ID NUMBER,
P_ITEM_FROM VARCHAR2,
P_ITEM_TO VARCHAR2
)IS
Begin
fnd_file.put_line(fnd_file.output,'编码'|| V_SEPERATE || '描述' || V_SEPERATE ||
'单位' || V_SEPERATE ||'数量'|| V_SEPERATE ||'价格' );
FOR c IN
(SELECT * FROM
(SELECT MSI.SEGMENT1 XM,
MSI.DESCRIPTION SM,
nvl(POL.QUANTITY,1)quantity,
POL.UNIT_MEAS_LOOKUP_CODE DW,
POL.UNIT_PRICE DJ
FROM PO_LINES_ALL POL,
MTL_SYSTEM_ITEMS MSI,
PO_HEADERS_ALL POH,
PO_VENDORS POV
WHERE POL.ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND POL.ITEM_ID>=P_ITEM_FROM AND POL.ITEM_ID<=P_ITEM_TO
AND poh.po_header_id = pol.po_header_id
AND poh.type_lookup_code = 'QUOTATION'
AND MSI.ORGANIZATION_ID=P_ORG_ID
union
SELECT MSI.SEGMENT1 XM,
MSI.DESCRIPTION SM,
Pll.Quantity quantity,
pll.unit_meas_lookup_code DW,
pll.price_override DJ
FROM PO_LINES_ALL POL,
Po_Line_Locations_All PLL,
MTL_SYSTEM_ITEMS MSI,
PO_HEADERS_ALL POH,
PO_VENDORS POV
WHERE POL.PO_LINE_ID = PLL.PO_LINE_ID
AND POH.PO_HEADER_ID = PLL.PO_HEADER_ID(+)
AND POL.ITEM_ID>=P_ITEM_FROM AND POL.ITEM_ID<=P_ITEM_TO
and pol.item_id = MSI.INVENTORY_ITEM_ID
and pll.po_header_id = poh.po_header_id
AND MSI.ORGANIZATION_ID = pll.ship_to_organization_id
and poh.type_lookup_code = 'QUOTATION'
AND MSI.ORGANIZATION_ID=P_ORG_ID) A)
loop
fnd_file.put_line(fnd_file.output,c.xm || V_SEPERATE || c.sm || V_SEPERATE ||
c.quantity|| V_SEPERATE ||c.dw|| V_SEPERATE ||c.dj ); END LOOP;
END PRO_MAIN;
end BFQUOTE;
编码 说明 数量 单位 单价
AG1002 单孔,斜面铝合金卷笔刀 1 只 0.260342
AG1002 单孔,斜面铝合金卷笔刀 11520 只 0.255214
AG1002 单孔,斜面铝合金卷笔刀 72000 只 0.250085
AG1002 单孔,斜面铝合金卷笔刀 144000 只 0.245043 我想要的报表的输出格式:
AG1002 单孔,斜面铝合金卷笔刀 1 只 0.260342
11520 只 0.255214
72000 只 0.250085
144000 只 0.245043 以下是本人写的,希望哪位高手帮帮我,
create or replace package body BFQUOTE is V_SEPERATE VARCHAR2(2) := CHR(9);
PROCEDURE PRO_MAIN(ERRBUF OUT VARCHAR2,
RETCODE OUT NUMBER,
P_ORG_ID NUMBER,
P_ITEM_FROM VARCHAR2,
P_ITEM_TO VARCHAR2
)IS
Begin
fnd_file.put_line(fnd_file.output,'编码'|| V_SEPERATE || '描述' || V_SEPERATE ||
'单位' || V_SEPERATE ||'数量'|| V_SEPERATE ||'价格' );
FOR c IN
(SELECT * FROM
(SELECT MSI.SEGMENT1 XM,
MSI.DESCRIPTION SM,
nvl(POL.QUANTITY,1)quantity,
POL.UNIT_MEAS_LOOKUP_CODE DW,
POL.UNIT_PRICE DJ
FROM PO_LINES_ALL POL,
MTL_SYSTEM_ITEMS MSI,
PO_HEADERS_ALL POH,
PO_VENDORS POV
WHERE POL.ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND POL.ITEM_ID>=P_ITEM_FROM AND POL.ITEM_ID<=P_ITEM_TO
AND poh.po_header_id = pol.po_header_id
AND poh.type_lookup_code = 'QUOTATION'
AND MSI.ORGANIZATION_ID=P_ORG_ID
union
SELECT MSI.SEGMENT1 XM,
MSI.DESCRIPTION SM,
Pll.Quantity quantity,
pll.unit_meas_lookup_code DW,
pll.price_override DJ
FROM PO_LINES_ALL POL,
Po_Line_Locations_All PLL,
MTL_SYSTEM_ITEMS MSI,
PO_HEADERS_ALL POH,
PO_VENDORS POV
WHERE POL.PO_LINE_ID = PLL.PO_LINE_ID
AND POH.PO_HEADER_ID = PLL.PO_HEADER_ID(+)
AND POL.ITEM_ID>=P_ITEM_FROM AND POL.ITEM_ID<=P_ITEM_TO
and pol.item_id = MSI.INVENTORY_ITEM_ID
and pll.po_header_id = poh.po_header_id
AND MSI.ORGANIZATION_ID = pll.ship_to_organization_id
and poh.type_lookup_code = 'QUOTATION'
AND MSI.ORGANIZATION_ID=P_ORG_ID) A)
loop
fnd_file.put_line(fnd_file.output,c.xm || V_SEPERATE || c.sm || V_SEPERATE ||
c.quantity|| V_SEPERATE ||c.dw|| V_SEPERATE ||c.dj ); END LOOP;
END PRO_MAIN;
end BFQUOTE;
编码说明数量单位单价
AG1002单孔,斜面铝合金卷笔刀1只0.260342
AG1002单孔,斜面铝合金卷笔刀11520只0.255214
AG1002单孔,斜面铝合金卷笔刀72000只0.250085
AG1002单孔,斜面铝合金卷笔刀144000只0.245043
------------------------------------------->
我想要的报表的输出格式:
AG1002单孔,斜面铝合金卷笔刀1只0.260342
11520只0.255214
72000只0.250085
144000只0.245043
-------------------------------------------<
实现以上样式可以使用以下sql:
select decode(rownum,1,t.型号,' ') as 型号,
t.数量,as 数量,
t.价格,as 价格
from tab t
如,数据:
型号 数量 价格
-------------------------------------------
AG1002单孔,斜面铝合金卷笔刀 1只 0.260342
AG1002单孔,斜面铝合金卷笔刀 11520只 0.255214AG1003单孔,斜面铝合金卷笔刀 72000只 0.250085
AG1003单孔,斜面铝合金卷笔刀 144000只 0.245043
要求实现:
型号 数量 价格
-------------------------------------------
AG1002单孔,斜面铝合金卷笔刀 1只 0.260342
11520只 0.255214AG1003单孔,斜面铝合金卷笔刀 72000只 0.250085
144000只 0.245043sql如下:
select decode(t1.rw,1,t1.型号,' ') as 型号,
t1.数量,as 数量,
t1.价格,as 价格
from
(select row_number() over(partition by t.型号 order by t.数量) as rw,
t.型号,
t.数量,as 数量,
t.价格,as 价格
from tab t) t1
就是:
下面的sql中的‘t1.rw’替换第一个回答sql中的‘rownum’。select decode(t1.rw,1,t1.型号,' ') as 型号,
t1.数量,as 数量,
t1.价格,as 价格
from
(select row_number() over(partition by t.型号 order by t.数量) as rw,
t.型号,
t.数量,as 数量,
t.价格,as 价格
from tab t) t1