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

ahmed salah 3,216 Reputation points
2021-05-17T01:09:42.78+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,255 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,516 questions
{count} votes

Accepted answer
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-05-18T01:34:32.143+00:00

    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.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-05-17T09:13:17.917+00:00

    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.