hubbsshaun
IS-IT--Management
Hi all,
I have written a piece of SQL code that is retrieving active stock numbers from our inventory system and is indicating across the columns if the item is stocked in each of our warehouses. The code currently works, but I am hoping to find a more elegant solution and possibly one that executes more quickly. Any help is greatly appreciated.
Thanks.
Shaun
I have written a piece of SQL code that is retrieving active stock numbers from our inventory system and is indicating across the columns if the item is stocked in each of our warehouses. The code currently works, but I am hoping to find a more elegant solution and possibly one that executes more quickly. Any help is greatly appreciated.
Thanks.
Shaun
Code:
select distinct
sitm.sitm_stock_no,
case when cen.sitm_status = 'A' then 'X' else null end as cen,
case when ssc.sitm_status = 'A' then 'X' else null end as ssc,
case when ach.sitm_status = 'A' then 'X' else null end as ach,
case when fmc.sitm_status = 'A' then 'X' else null end as fmc,
case when plc.sitm_status = 'A' then 'X' else null end as plc,
case when rgh.sitm_status = 'A' then 'X' else null end as rgh,
case when can.sitm_status = 'A' then 'X' else null end as can,
case when ccmha.sitm_status = 'A' then 'X' else null end as ccmha,
case when cgh.sitm_status = 'A' then 'X' else null end as cgh,
case when hrh.sitm_status = 'A' then 'X' else null end as hrh,
case when wht.sitm_status = 'A' then 'X' else null end as wht,
case when sscd.sitm_status = 'A' then 'X' else null end as sscd,
case when lab.sitm_status = 'A' then 'X' else null end as lab
from stockitem sitm
left join ( select sitm_stock_no, sitm_status from stockitem where sitm_status = 'A' and sitm_sapc = 6 )
cen on cen.sitm_stock_no = sitm.sitm_stock_no
left join ( select sitm_stock_no, sitm_status from stockitem where sitm_status = 'A' and sitm_sapc = 29 )
ssc on ssc.sitm_stock_no = sitm.sitm_stock_no
left join ( select sitm_stock_no, sitm_status from stockitem where sitm_status = 'A' and sitm_sapc = 3430 )
ach on ach.sitm_stock_no = sitm.sitm_stock_no
left join ( select sitm_stock_no, sitm_status from stockitem where sitm_status = 'A' and sitm_sapc = 9 )
fmc on fmc.sitm_stock_no = sitm.sitm_stock_no
left join ( select sitm_stock_no, sitm_status from stockitem where sitm_status = 'A' and sitm_sapc = 7 )
plc on plc.sitm_stock_no = sitm.sitm_stock_no
left join ( select sitm_stock_no, sitm_status from stockitem where sitm_status = 'A' and sitm_sapc = 8 )
rgh on rgh.sitm_stock_no = sitm.sitm_stock_no
left join ( select sitm_stock_no, sitm_status from stockitem where sitm_status = 'A' and sitm_sapc = 170 )
can on can.sitm_stock_no = sitm.sitm_stock_no
left join ( select sitm_stock_no, sitm_status from stockitem where sitm_status = 'A' and sitm_sapc = 149 )
ccmha on ccmha.sitm_stock_no = sitm.sitm_stock_no
left join ( select sitm_stock_no, sitm_status from stockitem where sitm_status = 'A' and sitm_sapc = 150 )
cgh on cgh.sitm_stock_no = sitm.sitm_stock_no
left join ( select sitm_stock_no, sitm_status from stockitem where sitm_status = 'A' and sitm_sapc = 171 )
hrh on hrh.sitm_stock_no = sitm.sitm_stock_no
left join ( select sitm_stock_no, sitm_status from stockitem where sitm_status = 'A' and sitm_sapc = 109 )
wht on wht.sitm_stock_no = sitm.sitm_stock_no
left join ( select sitm_stock_no, sitm_status from stockitem where sitm_status = 'A' and sitm_sapc = 89 )
sscd on sscd.sitm_stock_no = sitm.sitm_stock_no
left join ( select sitm_stock_no, sitm_status from stockitem where sitm_status = 'A' and sitm_sapc = 69 )
lab on lab.sitm_stock_no = sitm.sitm_stock_no
where sitm.sitm_status = 'A'