679
社区成员
发帖
与我相关
我的任务
分享
select C.sequenceno,A.contractid,A.invoiceno,A.fromcountry,A.destination,
A.handbookno,A.importcustom,A.exportcustom,A.forwardername,
A.hktruckno,A.chtruckno,A.containerno,A.tradepat,A.bargainclass,
A.piece,A.packingname,A.grossweight,A.netweight,
A.ciftotal,A.fobtotal,A.shipcost,A.insurancecost,A.repaircost,
A.fxnum,A.palletqtycount,A.palletweightcount,
B.shipno,B.itemno,decode(B.itemdesc,null,' ',B.itemdesc) as itemdesc,
B.quantity,
decode(B.shipmentchr2,null,' ',B.shipmentchr2) as shipmentchr2,
B.itempacking,B.itemnetweight,
B.itemgross,B.originalcountry,B.boxno,B.shipclass,
B.itemgroup,B.autogroupno,B.quantity * B.unitprice as itemprice,
C.customcode,C.chitemname,C.unit,B.unitprice,C.unitlaborprice,
B.curr
,B.ITEMPRICE_MANUAL
from
ct_t_invoice A,(select * from ct_t_shipmenttemp where userid='ADMIN') B,ct_t_contractitem C
where
A.invoiceno=B.invoiceno AND A.contractid=C.contractid AND B.itemno=C.itemno
AND A.invoiceno='45145000049' order by B.itemgroup,B.autogroupno
select t.sequenceno,
t.customcode,
t.chitemname,
sum(t.quantity) as quantity,
t.unit,
sum(itemnetweight) as itemnetweight,
t.originalcountry,
t.unitprice,
sum(t.quantity * t.unitprice) itemtotalprice,
t.curr
from (select A.invoiceno,
A.contractid,
B.itemno,
B.quantity,
B.itempacking,
B.itemgroup,
B.autogroupno,
B.itemgross,
B.unitweight * B.quantity as itemnetweight,
decode(trim(B.shipmentchr2),
null,
0,
'',
0,
to_number(B.shipmentchr2)) as shipmentchr2,
decode(sign(B.ITEMPRICE_MANUAL - 0),
1,
B.ITEMPRICE_MANUAL,
B.quantity * B.unitprice) as itemprice,
C.sequenceno,
C.chitemname,
B.unitprice,
B.curr,
C.CUSTOMCODE,
c.unit,
b.originalcountry
from ct_t_invoice A, ct_t_shipment B, ct_t_contractitem C
where A.invoiceno = B.invoiceno
AND A.INVOICENO='45145000049'
and A.contractid = C.contractid
and B.itemno = C.itemno
order by B.itemgroup, B.autogroupno
) t
group by sequenceno,customcode,chitemname,unit,originalcountry,unitprice,curr,itemgroup,autogroupno
order by itemgroup, autogroupno;
-- m 是你原来 java 中的语句,一点没动 ,你接着向下看
with m as (
select t.sequenceno,
t.customcode,
t.chitemname,
sum(t.quantity) as quantity,
t.unit,
sum(itemnetweight) as itemnetweight,
t.originalcountry,
t.unitprice,
sum(t.quantity * t.unitprice) itemtotalprice,
t.curr
from (select A.invoiceno,
A.contractid,
B.itemno,
B.quantity,
B.itempacking,
B.itemgroup,
B.autogroupno,
B.itemgross,
B.unitweight * B.quantity as itemnetweight,
decode(trim(B.shipmentchr2),
null,
0,
'',
0,
to_number(B.shipmentchr2)) as shipmentchr2,
decode(sign(B.ITEMPRICE_MANUAL - 0),
1,
B.ITEMPRICE_MANUAL,
B.quantity * B.unitprice) as itemprice,
C.sequenceno,
C.chitemname,
B.unitprice,
B.curr,
C.CUSTOMCODE,
c.unit,
b.originalcountry
from ct_t_invoice A, ct_t_shipment B, ct_t_contractitem C
where A.invoiceno = B.invoiceno
AND A.INVOICENO='45145000049'
and A.contractid = C.contractid
and B.itemno = C.itemno
order by B.itemgroup, B.autogroupno
) t
group by sequenceno,customcode,chitemname,unit,originalcountry,unitprice,curr,itemgroup,autogroupno
order by itemgroup, autogroupno
), -- 原 java 中的语句,到这里。
order_table as ( -- 这个是你在 VB 中的语句,现在整理下,只剩下 no 和一个自定义的 序号
select t.sequenceno , rownum rn from (
select C.sequenceno
from
ct_t_invoice A,(select * from ct_t_shipmenttemp where userid='ADMIN') B,ct_t_contractitem C
where
A.invoiceno=B.invoiceno AND A.contractid=C.contractid AND B.itemno=C.itemno
AND A.invoiceno='45145000049' order by B.itemgroup,B.autogroupno
) t
)
select m.* from m inner join order_table o on m.sequenceno = o.sequenceno
order by o.rn