sommererdbeere
Programmer
hi,
this is similiar on what i've asked before, but this time, it is a set that cause the problems. I need to have this sql as clean as possible in order import to crystal. i cannot do suppress if duplicates function in crystal, because there are many orders and each order has the same set of number of (100, 300, 400, 421). i have included my sql, outcome. thank u thank u for those who help.. THIS IS VERY URGENT as i need to turn in TOMM.......trillion thanks..
database: oracle 8
sql:
select
so.ordno soordno,
col.ordno colordno,
sp.partno sppn ,
col.rev_qty_due colrevq,
so.rev_qty_due sorevq,
so.complete_qty socompq ,
pcbh.costid pcbhcostid,
substr(COST_HISTORY_API.Get_Cost(so.ordno, '*', '*',pcbh.costid),1,15) act_cost,
sp.*, pcbh.*
from PART_COST pcbh, SALES sp,
shop_ord so, customer_order col
where
sp.partno(+) = pcbh.partno and
sp.partno = so.partno and
sp.partno = col.partno and
col.STATE != 'Cancelled' and
so.state= 'Closed' and
sp.partno = '4610' and
col.ORDER_NO = '14' and
so.order_no = '1877' and
pcbh.HIST_NO =
(select max (pc.HIST_NO)
from part_cost_hist pc
where pc.partno = pcbh.partno and
pc.contract = pcbh.contract and
pc.configuration_id = pcbh.configuration_id and
TRUNC(pc.copy_date) <= to_date(to_char(so.revised_start_date, 'YYYYMMDD'), 'YYYYMMDD'))
outcome:
soordno colordno sppn colrevq sorevq socompq pcbhcostid
1877 14 4610 5700 298 288 110
1877 14 4610 5700 298 288 300
1877 14 4610 5700 298 288 400
1877 14 4610 5700 298 288 421
1877 14 4610 19800 298 288 110
1877 14 4610 19800 298 288 300
1877 14 4610 19800 298 288 400
1877 14 4610 19800 298 288 421
i want sth like this:
soordno colordno sppn colrevq sorevq socompq pcbhcostid
1877 14 4610 5700 298 288 110
19800 300
400
421
p.s.. also, SALES sp is a govern table. since there are partno that shop_ord has it, but customer_order doesn't have it or vice versa. so the table, SALES , act as a governing table meaning that as long as this table has the part, i need to pull everything out.
thankssssssss for anyone of ur experts' help.. thanku thank u
this is similiar on what i've asked before, but this time, it is a set that cause the problems. I need to have this sql as clean as possible in order import to crystal. i cannot do suppress if duplicates function in crystal, because there are many orders and each order has the same set of number of (100, 300, 400, 421). i have included my sql, outcome. thank u thank u for those who help.. THIS IS VERY URGENT as i need to turn in TOMM.......trillion thanks..
database: oracle 8
sql:
select
so.ordno soordno,
col.ordno colordno,
sp.partno sppn ,
col.rev_qty_due colrevq,
so.rev_qty_due sorevq,
so.complete_qty socompq ,
pcbh.costid pcbhcostid,
substr(COST_HISTORY_API.Get_Cost(so.ordno, '*', '*',pcbh.costid),1,15) act_cost,
sp.*, pcbh.*
from PART_COST pcbh, SALES sp,
shop_ord so, customer_order col
where
sp.partno(+) = pcbh.partno and
sp.partno = so.partno and
sp.partno = col.partno and
col.STATE != 'Cancelled' and
so.state= 'Closed' and
sp.partno = '4610' and
col.ORDER_NO = '14' and
so.order_no = '1877' and
pcbh.HIST_NO =
(select max (pc.HIST_NO)
from part_cost_hist pc
where pc.partno = pcbh.partno and
pc.contract = pcbh.contract and
pc.configuration_id = pcbh.configuration_id and
TRUNC(pc.copy_date) <= to_date(to_char(so.revised_start_date, 'YYYYMMDD'), 'YYYYMMDD'))
outcome:
soordno colordno sppn colrevq sorevq socompq pcbhcostid
1877 14 4610 5700 298 288 110
1877 14 4610 5700 298 288 300
1877 14 4610 5700 298 288 400
1877 14 4610 5700 298 288 421
1877 14 4610 19800 298 288 110
1877 14 4610 19800 298 288 300
1877 14 4610 19800 298 288 400
1877 14 4610 19800 298 288 421
i want sth like this:
soordno colordno sppn colrevq sorevq socompq pcbhcostid
1877 14 4610 5700 298 288 110
19800 300
400
421
p.s.. also, SALES sp is a govern table. since there are partno that shop_ord has it, but customer_order doesn't have it or vice versa. so the table, SALES , act as a governing table meaning that as long as this table has the part, i need to pull everything out.
thankssssssss for anyone of ur experts' help.. thanku thank u