optimalize ugly view
Hello,
i had to made some ugly view and i'd like to ask, how can be optimalized ? I tried to use left joins, bud processing takes more time then ugly one :)
Here is the first version:
Select * from
(SELECT k.id, k.stroj, k.order1 as ProdOrder, k.partnumber1 as PartNr, k.ST1_value1 as ST1,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST1' and k.stroj=operace_view.stroj) as ST1_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST1' and k.stroj=operace_view.stroj) as ST1_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST1' and k.stroj=operace_view.stroj) as ST1_sap,
k.ST2_value1 as ST2,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST2' and k.stroj=operace_view.stroj) as ST2_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST2' and k.stroj=operace_view.stroj) as ST2_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST2' and k.stroj=operace_view.stroj) as ST2_sap,
k.ST3_value1 as ST3,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST3' and k.stroj=operace_view.stroj) as ST3_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST3' and k.stroj=operace_view.stroj) as ST3_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST3' and k.stroj=operace_view.stroj) as ST3_sap,
k.ST4_value1 as ST4,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST4' and k.stroj=operace_view.stroj) as ST4_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST4' and k.stroj=operace_view.stroj) as ST4_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST4' and k.stroj=operace_view.stroj) as ST4_sap,
k.ST5_value1 as ST5,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST5' and k.stroj=operace_view.stroj) as ST5_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST5' and k.stroj=operace_view.stroj) as ST5_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST5' and k.stroj=operace_view.stroj) as ST5_sap,
k.ST6_value1 as ST6,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST6' and k.stroj=operace_view.stroj) as ST6_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST6' and k.stroj=operace_view.stroj) as ST6_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST6' and k.stroj=operace_view.stroj) as ST6_sap,
k.ST7_value1 as ST7,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST7' and k.stroj=operace_view.stroj) as ST7_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST7' and k.stroj=operace_view.stroj) as ST7_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST7' and k.stroj=operace_view.stroj) as ST7_sap,
k.ST8_value1 as ST8,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST8' and k.stroj=operace_view.stroj) as ST8_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST8' and k.stroj=operace_view.stroj) as ST8_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST8' and k.stroj=operace_view.stroj) as ST8_sap,
k.ST9_value1 as ST9,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST9' and k.stroj=operace_view.stroj) as ST9_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST9' and k.stroj=operace_view.stroj) as ST9_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST9' and k.stroj=operace_view.stroj) as ST9_sap,
k.ST10_value1 as ST10,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST10' and k.stroj=operace_view.stroj) as ST10_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST10' and k.stroj=operace_view.stroj) as ST10_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST10' and k.stroj=operace_view.stroj) as ST10_sap,
k.NOKcelkem_value1 as NOKcelkem, k.OKcelkem_value1 as OKcelkem, smena, timeStamp
from kanban k
INNER JOIN operace_view ov on ov.stroj=k.stroj) data1 group by data1.id
union all
Select * from(SELECT k.id, k.stroj, k.order2, k.partnumber2, k.ST1_value2,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST1' and k.stroj=operace_view.stroj) as ST1_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST1' and k.stroj=operace_view.stroj) as ST1_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST1' and k.stroj=operace_view.stroj) as ST1_sap,
k.ST2_value2,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST2' and k.stroj=operace_view.stroj) as ST2_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST2' and k.stroj=operace_view.stroj) as ST2_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST2' and k.stroj=operace_view.stroj) as ST2_sap,
k.ST3_value2,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST3' and k.stroj=operace_view.stroj) as ST3_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST3' and k.stroj=operace_view.stroj) as ST3_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST3' and k.stroj=operace_view.stroj) as ST3_sap,
k.ST4_value2,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST4' and k.stroj=operace_view.stroj) as ST4_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST4' and k.stroj=operace_view.stroj) as ST4_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST4' and k.stroj=operace_view.stroj) as ST4_sap,
k.ST5_value2,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST5' and k.stroj=operace_view.stroj) as ST5_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST5' and k.stroj=operace_view.stroj) as ST5_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST5' and k.stroj=operace_view.stroj) as ST5_sap,
k.ST6_value2,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST6' and k.stroj=operace_view.stroj) as ST6_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST6' and k.stroj=operace_view.stroj) as ST6_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST6' and k.stroj=operace_view.stroj) as ST6_sap,
k.ST7_value2,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST7' and k.stroj=operace_view.stroj) as ST7_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST7' and k.stroj=operace_view.stroj) as ST7_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST7' and k.stroj=operace_view.stroj) as ST7_sap,
k.ST8_value2,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST8' and k.stroj=operace_view.stroj) as ST8_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST8' and k.stroj=operace_view.stroj) as ST8_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST8' and k.stroj=operace_view.stroj) as ST8_sap,
k.ST9_value2,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST9' and k.stroj=operace_view.stroj) as ST9_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST9' and k.stroj=operace_view.stroj) as ST9_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST9' and k.stroj=operace_view.stroj) as ST9_sap,
k.ST10_value2,
(select operace_view.operace from operace_view where operace_view.pozice = 'ST10' and k.stroj=operace_view.stroj) as ST10_oper,
(select operace_view.kod_vady from operace_view where operace_view.pozice = 'ST10' and k.stroj=operace_view.stroj) as ST10_vada,
(select operace_view.sap_popis from operace_view where operace_view.pozice = 'ST10' and k.stroj=operace_view.stroj) as ST10_sap,
k.NOKcelkem_value2, k.OKcelkem_value2, smena, timeStamp
from kanban k
INNER JOIN operace_view ov on ov.stroj=k.stroj) data2 group by data2.id