Home I would like to know if the query can be simplified further (or) any other way to rewrite the SQL query

Questions

I would like to know if the query can be simplified further (or) any other way to rewrite the SQL query

March 29, 2022

SELECT id_col,order_col,
sum(nvl(CASE WHEN INDEX = 2 AND trim(order_list) NOT IN('', ' ') THEN order_list END,0)) OVER (partition BY order_col) AS total_qty, 
sum(nvl(CASE WHEN INDEX = 3 AND trim(order_list) NOT IN('',' ') THEN order_list END,0)) OVER (partition BY order_col) AS total_amt,
sum(nvl(CASE WHEN order_list LIKE '21235%' THEN replace(order_list,'21235=','')END,0)) 
OVER (partition BY order_col) AS sav_001,     
sum(nvl(CASE WHEN order_list LIKE '21236%' THEN replace(order_list,'21236=','')
     END,0)) OVER (partition BY order_col) AS sav_002,
sum(nvl(CASE WHEN order_list LIKE '21237%' THEN replace(order_list,'21237=','')
     END,0)) OVER (partition BY order_col)AS sav_003
FROM  tbl_001  T1
qualify row_number() over (partition by order_col order by date desc) = 1

The above query is taking long time to run. Is there anyway we can re-write the above query and improve the performance?

>Solution :

The NVL(CASE WHEN cond THEN val END, 0) is the same as CASE WHEN cond THEN val ELSE 0 END and even further windowed SUM omits NULLS so simple CASE WHEN cond THEN val END:

SELECT id_col,order_col,
sum(CASE WHEN INDEX = 2 AND trim(order_list) != '' THEN order_list END) 
  OVER (partition BY order_col) AS total_qty, 
sum(CASE WHEN INDEX = 3 AND trim(order_list) != '' THEN order_list END)
  OVER (partition BY order_col) AS total_amt,
sum(CASE WHEN order_list LIKE '21235%' THEN replace(order_list,'21235=','')END) 
  OVER (partition BY order_col) AS sav_001,     
sum(CASE WHEN order_list LIKE '21236%' THEN replace(order_list,'21236=','')
     END) OVER (partition BY order_col) AS sav_002,
sum(CASE WHEN order_list LIKE '21237%' THEN replace(order_list,'21237=','')
     END) OVER (partition BY order_col)AS sav_003
FROM  tbl_001  T1
qualify row_number() over (partition by order_col order by date desc) = 1;