How to rewrite sql query after where condition with another query more arranged ?

Question

I work on sql server I need to rewrite statement after where statement so how to rewrite

I don't understand what is writing after where

so can you help me how to write it alternative

  SELECT top 1 *
      FROM extractreports.dbo.TblTemp T with(nolock) JOIN extractreports.dbo.Tbl_Temp TT with(nolock) ON T.GlobalPnID=TT.GlobalPnID 
    join (Select Max(GPNP.GroupID) GroupID,GPNP.GlobalPnID from dbo.GlobalPartNumberPortions GPNP with(nolock) group by GPNP.GlobalPnID ) K  on k.GroupID=t.GroupID  And K.GlobalPnID =T.GlobalPnID
    join extractreports.[dbo].[PCDataConfiguration] p with(nolock) on p.partnumber=t.[key]
    Left JOIN extractreports.dbo.Tbl_TempCount tc with(nolock) ON t.GlobalPnID =Tc.GlobalPnID 
     LEFT JOIN dbo.GlobalPartNumberPortionException gpnE WITH(NOLOCK) ON t.GlobalPnID =gpne.GlobalPnId

    WHERE 

    TT.Counts >=TT.RealCount AND  (tc.GlobalPnID IS NULL or

    (tc.Counts =0 or

    (tc.Counts >0  AND t.[Key] LIKE CASE WHEN gpnE.HasRange =1 THEN gpnE.ExceptionRange ELSE gpnE.ExceptionSignature end 

    AND 1= CASE WHEN  gpnE.HasRange =0 THEN 1 ELSE /*******/ CASE WHEN gpnE.Exception LIKE N'%~%' THEN  [PC].[FN_PartCheckRange](T.PortionMapIds,gpnE.Exception,gpnE.PortionNumbers,gpnE.FromValue,gpnE.ToValue) ELSE 1 END /*******/END 

    ))
    )

what i need it rewrite statement as below :

WHERE 

    TT.Counts >=TT.RealCount AND  (tc.GlobalPnID IS NULL or

    (tc.Counts =0 or

    (tc.Counts >0  AND t.[Key] LIKE CASE WHEN gpnE.HasRange =1 THEN gpnE.ExceptionRange ELSE gpnE.ExceptionSignature end 

    AND 1= CASE WHEN  gpnE.HasRange =0 THEN 1 ELSE /*******/ CASE WHEN gpnE.Exception LIKE N'%~%' THEN  [PC].[FN_PartCheckRange](T.PortionMapIds,gpnE.Exception,gpnE.PortionNumbers,gpnE.FromValue,gpnE.ToValue) ELSE 1 END /*******/END 

    ))
    )

really i don't understand what after where condition so can you help me to understand what written after where

condition
or
rewrite it with another syntax or logic ?

this actually i need to rewrite it

 WHERE 

    TT.Counts >=TT.RealCount AND  (tc.GlobalPnID IS NULL or

    (tc.Counts =0 or

    (tc.Counts >0  AND t.[Key] LIKE CASE WHEN gpnE.HasRange =1 THEN gpnE.ExceptionRange ELSE gpnE.ExceptionSignature end 

    AND 1= CASE WHEN  gpnE.HasRange =0 THEN 1 ELSE /*******/ CASE WHEN gpnE.Exception LIKE N'%~%' THEN  [PC].[FN_PartCheckRange](T.PortionMapIds,gpnE.Exception,gpnE.PortionNumbers,gpnE.FromValue,gpnE.ToValue) ELSE 1 END /*******/END 

    ))
    )

only i need to rewrite statement after where to be more readable and high performance

Answer 1

This reply should have been posted in the comment, but it exceeded the number of words in the comment.

When the SQL statement has multiple or statements, you can consider using union or union all instead to improve speed. SQL statements using or often cannot be optimized, resulting in slower speeds. But this is not fixed, and sometimes it will be faster to use or. The specific situation is subject to testing.

As far as the readability of the code is concerned, if you really need so many filter conditions, the code can hardly be simplified.

Performance optimization issues, to be honest, we need to test according to our actual situation and some rules. However, it is often useful to build an index on the conditional column behind where (when there are multiple conditional columns, you can try compound index and covering index).

When you encounter a similar problem again, you can actually publish your needs and data directly, and the people on the forum will provide you with a variety of solutions, and you can choose the best method.Because when we can only see the code without understanding your actual needs and data, it may be difficult to give you good suggestions.

To use union all instead of or, please refer to the following code:

;WITH cte
as(SELECT top 1 *
FROM extractreports.dbo.TblTemp T with(nolock) JOIN extractreports.dbo.Tbl_Temp TT with(nolock) ON T.GlobalPnID=TT.GlobalPnID 
join (SELECT Max(GPNP.GroupID) GroupID,GPNP.GlobalPnID from dbo.GlobalPartNumberPortions GPNP with(nolock) group by GPNP.GlobalPnID ) K  
on k.GroupID=t.GroupID and  K.GlobalPnID =T.GlobalPnID
join extractreports.[dbo].[PCDataConfiguration] p with(nolock) on p.partnumber=t.[key]
left join extractreports.dbo.Tbl_TempCount tc with(nolock) on t.GlobalPnID =Tc.GlobalPnID 
left join dbo.GlobalPartNumberPortionException gpnE with(nolock) on t.GlobalPnID =gpne.GlobalPnId
WHERE TT.Counts>=TT.RealCount)


SELECT * FROM cte WHERE GlobalPnID is null
UNION ALL
SELECT * FROM cte WHERE Counts =0
UNION ALL
SELECT * FROM cte 
WHERE (Counts >0  and [Key] like 
CASE WHEN HasRange =1 THEN ExceptionRange 
ELSE ExceptionSignature END  
and 1= CASE WHEN  HasRange =0 THEN 1
        WHEN Exception like  N'%~%' 
        THEN  [FN_PartCheckRange](PortionMapIds,Exception,PortionNumbers,FromValue,ToValue) 
        ELSE 1 
        END 
 )

If you do not have duplicate column names in several tables, you can refer to the above code. If there are duplicate column names, you need to name the aliases for the duplicate column names in cte.Because cte does not allow duplicate column names.

Answer 2

Hi @ahmed salah ,

WHERE   
TT.Counts>=TT.RealCount and   
(tc.GlobalPnID is null or  
tc.Counts =0 or  
(tc.Counts >0  and t.[Key] like   
CASE WHEN gpnE.HasRange =1 THEN gpnE.ExceptionRange   
ELSE gpnE.ExceptionSignature END    
and 1= CASE WHEN  gpnE.HasRange =0 THEN 1  
       WHEN gpnE.Exception like  N'%~%'   
       THEN  [PC].[FN_PartCheckRange](T.PortionMapIds,gpnE.Exception,gpnE.PortionNumbers,gpnE.FromValue,gpnE.ToValue)   
       ELSE 1   
       END   
))  

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.