Question 1Please compose the SQL statement (Oracle standard) that will generate the following result based on the given source tables (Hints are available upon request):Expected Result
Level ASSEMBLY ITEM ID SEGMENT1 COMPONENT ITEM ID SEGMENT1 1 QUANTITY PER ASSEMBLY REFERENCE PRICE
1 180 373257-00000 178 373257-10000 1
*2 178 373257-10000 146 00TR00-00016 2 0.3
*2 178 373257-10000 198 00TR00-00106 2 0.5
*2 178 373257-10000 148 01RS03-01020 1
*2 178 373257-10000 150 03PB01-00443 1
1 180 373257-00000 176 373257-20000 1
*2 176 373257-20000 142 01QZ00-00168 1
*2 176 373257-20000 144 03CN10-00018 1 3.2
1 180 373257-00000 192 373257-30000 1
*2 192 373257-30000 152 08PG14-00021 1
*2 192 373257-30000 154 08PG14-00028 1
*2 192 373257-30000 58 MS02-0002-01 0.001 5
*2 192 373257-30000 156 MS02-0006-01 0.00075 4Source Table
Table 1: MTL_SYSTEM_ITEMS
INVENTORY_ITEM_ID SEGMENT1
180 373257-00000
280 373258-10000
178 373257-10000
176 373257-20000
192 373257-30000
146 00TR00-00016
198 00TR00-00106
142 01QZ00-00168
148 01RS03-01020
144 03CN10-00018
150 03PB01-00443
152 08PG14-00021
58 MS02-0002-01 Table 2: BOMBV_BOM_COMPONENTS
ASSEMBLY_ITEM_ID COMPONENT_ITEM_ID QUANTITY_PER_ASSEMBLY
180 178 1
178 146 2
178 198 2
178 148 1
178 150 1
180 176 1
176 142 1
176 144 1
180 192 1
192 152 1
192 154 1
192 58 0.001
192 156 0.00075
280 178 1
280 144 1Table 3: INVENTORY_REF_PRICE
INVENTORY_ITEM_ID REFERENCE_PRICE
146 0.3
198 0.5
144 3.2
58 5
156 4Hint 1: self-join
Use “start with connect by” clauseHint 2: left
Use LPAD() built-in to display level.
Level ASSEMBLY ITEM ID SEGMENT1 COMPONENT ITEM ID SEGMENT1 1 QUANTITY PER ASSEMBLY REFERENCE PRICE
1 180 373257-00000 178 373257-10000 1
*2 178 373257-10000 146 00TR00-00016 2 0.3
*2 178 373257-10000 198 00TR00-00106 2 0.5
*2 178 373257-10000 148 01RS03-01020 1
*2 178 373257-10000 150 03PB01-00443 1
1 180 373257-00000 176 373257-20000 1
*2 176 373257-20000 142 01QZ00-00168 1
*2 176 373257-20000 144 03CN10-00018 1 3.2
1 180 373257-00000 192 373257-30000 1
*2 192 373257-30000 152 08PG14-00021 1
*2 192 373257-30000 154 08PG14-00028 1
*2 192 373257-30000 58 MS02-0002-01 0.001 5
*2 192 373257-30000 156 MS02-0006-01 0.00075 4Source Table
Table 1: MTL_SYSTEM_ITEMS
INVENTORY_ITEM_ID SEGMENT1
180 373257-00000
280 373258-10000
178 373257-10000
176 373257-20000
192 373257-30000
146 00TR00-00016
198 00TR00-00106
142 01QZ00-00168
148 01RS03-01020
144 03CN10-00018
150 03PB01-00443
152 08PG14-00021
58 MS02-0002-01 Table 2: BOMBV_BOM_COMPONENTS
ASSEMBLY_ITEM_ID COMPONENT_ITEM_ID QUANTITY_PER_ASSEMBLY
180 178 1
178 146 2
178 198 2
178 148 1
178 150 1
180 176 1
176 142 1
176 144 1
180 192 1
192 152 1
192 154 1
192 58 0.001
192 156 0.00075
280 178 1
280 144 1Table 3: INVENTORY_REF_PRICE
INVENTORY_ITEM_ID REFERENCE_PRICE
146 0.3
198 0.5
144 3.2
58 5
156 4Hint 1: self-join
Use “start with connect by” clauseHint 2: left
Use LPAD() built-in to display level.
msi.INVENTORY_ITEM_ID 'ASSEMBLY ITEM ID',
msi.segment1 'SEGMENT1',
bbc.COMPONENT_ITEM_ID 'COMPONENT ITEM ID',
msi2.segment1 'SEGMENT1 1',
msi.QUANTITY_PER_ASSEMBLY 'QUANTITY PER ASSEMBLY',
msi.QUANTITY_PER_ASSEMBLY*irp.REFERENCE_PRICE 'REFERENCE PRICE'
from BOMBV_BOM_COMPONENTS bbc,
MTL_SYSTEM_ITEMS msi,
TL_SYSTEM_ITEMS msi2,
INVENTORY_REF_PRICE irp where bbc.ASSEMBLY_ITEM_ID=msi.INVENTORY_ITEM_ID
and bbc.COMPONENT_ITEM_ID=msi2.INVENTORY_ITEM_ID
and irp.INVENTORY_ITEM_ID=msi2.INVENTORY_ITEM_ID
connect by prior
bbc.COMPONENT_ITEM_ID=bbc.ASSEMBLY_ITEM_ID
start with bbc.ASSEMBLY_ITEM_ID=180
select lpad(level,level,'*'),a2.assembly_item_id,a1.segment1,a2.component_item_id,a3.segment1
from BOMBV_BOM_COMPONENTS a2,
MTL_SYSTEM_ITEMS a1,
MTL_SYSTEM_ITEMS a3
where a2.assembly_item_id = a1.inventory_item_id(+)
and a2.component_item_id = a3.inventory_item_id(+)
start with a2.assembly_item_id =180
connect by a2.ASSEMBLY_ITEM_ID = prior a2.COMPONENT_ITEM_ID
不全,大致这个意思。下班没时间了
另外,不明白结果的排序规则
用连接得到ID对应的描述和价格
select LPAD('*', a.lvl - 1) || a.lvl "level",
a.ASSEMBLY_ITEM_ID "ASSEMBLY ITEM ID",
b.SEGMENT1 "SEGMENT1",
a.COMPONENT_ITEM_ID "COMPONENT ITEM ID",
c.SEGMENT1 "SEGMENT1 1",
a.QUANTITY_PER_ASSEMBLY "QUANTITY PER ASSEMBLY",
d.REFERENCE_PRICE "REFERENCE PRICE"
from (select level lvl,
ASSEMBLY_ITEM_ID,
COMPONENT_ITEM_ID,
QUANTITY_PER_ASSEMBLY
from BOMBV_BOM_COMPONENTS
start with ASSEMBLY_ITEM_ID = 180
connect by prior COMPONENT_ITEM_ID = ASSEMBLY_ITEM_ID) a,
MTL_SYSTEM_ITEMS b,
MTL_SYSTEM_ITEMS c,
INVENTORY_REF_PRICE d
where a.ASSEMBLY_ITEM_ID = b.INVENTORY_ITEM_ID
and a.COMPONENT_ITEM_ID = c.INVENTORY_ITEM_ID
and a.COMPONENT_ITEM_ID = d.INVENTORY_ITEM_ID(+)