Query with many CASE statements - optimization
- by Nemanja Vujacic
Hi guys,
I have one very dirty query that per sure can be optimized because there are so many CASE statements in it!
SELECT
(CASE pa.KplusTable_Id WHEN 1 THEN sp.sp_id
WHEN 2 THEN fw.fw_id
WHEN 3 THEN s.sw_Id
WHEN 4 THEN id.ia_id END) as Deal_Id,
max(CASE pa.KplusTable_Id WHEN 1 THEN sp.Trans_Id
WHEN 2 THEN fw.Trans_Id
WHEN 3 THEN s.Trans_Id
WHEN 4 THEN id.Trans_Id END) as TransId_CurrentMax
INTO #MaxRazlicitOdNull
FROM #PotencijalniAktuelni pa LEFT JOIN kplus_sp sp (nolock) on sp.sp_id=pa.Deal_Id AND pa.KplusTable_Id=1
LEFT JOIN kplus_fw fw (nolock) on fw.fw_id=pa.Deal_Id AND pa.KplusTable_Id=2
LEFT JOIN dev_sw s (nolock) on s.sw_Id=pa.Deal_Id AND pa.KplusTable_Id=3
LEFT JOIN kplus_ia id (nolock) on id.ia_id=pa.Deal_Id AND pa.KplusTable_Id=4
WHERE isnull(CASE pa.KplusTable_Id WHEN 1 THEN sp.BROJ_TIKETA
WHEN 2 THEN fw.BROJ_TIKETA
WHEN 3 THEN s.tiket
WHEN 4 THEN id.BROJ_TIKETA END, '')<>''
GROUP BY CASE pa.KplusTable_Id WHEN 1 THEN sp.sp_id
WHEN 2 THEN fw.fw_id
WHEN 3 THEN s.sw_Id
WHEN 4 THEN id.ia_id END
Because I have same condition couple times, do you have idea how to optimize query, make it simpler and better. All suggestions are welcome!
TnX in advance!
Nemanja
