question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked MelissaMa-msft edited

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

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    
            
       dbo.GlobalPartNumberPortions Po WITH(NOLOCK)
                     INNER JOIN dbo.GlobalPartNumber GOl WITH(NOLOCK) ON GOl.GlobalPnId = Po.GlobalPnId  AND Po.GroupId = 1
                     INNER JOIN Parts.Nop_PartsFamily pf WITH(NOLOCK) ON GOl.FamilyId = pf.PartFamilyID 
                     INNER JOIN dbo.GlobalPartNumberPortions Po2 WITH(NOLOCK) ON GOl.GlobalPnId = Po2.GlobalPnId  AND Po2.GroupId = 2
        
        
        
             WHERE   @PartNumber LIKE CONCAT(LTRIM(RTRIM(CASE WHEN Po.PortionKey=N'Blank' THEN '' 
                                                  WHEN CHARINDEX('[', PO.PortionKey) >0 then replace(PO.PortionKey,N'[',N'[[') 
                                                  ELSE Po.PortionKey END))
                                            ,LTRIM(RTRIM(CASE WHEN Po2.PortionKey=N'Blank' THEN '' 
                                                  WHEN CHARINDEX('[', PO2.PortionKey) >0 then replace(PO2.PortionKey,N'[',N'[[') 
                                                  ELSE Po2.PortionKey END))
                                            , '%')

what i need it rewrite statement as below :

 WHERE   @PartNumber LIKE CONCAT(LTRIM(RTRIM(CASE WHEN Po.PortionKey=N'Blank' THEN '' 
                                              WHEN CHARINDEX('[', PO.PortionKey) >0 then replace(PO.PortionKey,N'[',N'[[') 
                                              ELSE Po.PortionKey END))
                                        ,LTRIM(RTRIM(CASE WHEN Po2.PortionKey=N'Blank' THEN '' 
                                              WHEN CHARINDEX('[', PO2.PortionKey) >0 then replace(PO2.PortionKey,N'[',N'[[') 
                                              ELSE Po2.PortionKey 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   @PartNumber LIKE CONCAT(LTRIM(RTRIM(CASE WHEN Po.PortionKey=N'Blank' THEN '' 
                                                  WHEN CHARINDEX('[', PO.PortionKey) >0 then replace(PO.PortionKey,N'[',N'[[') 
                                                  ELSE Po.PortionKey END))
                                            ,LTRIM(RTRIM(CASE WHEN Po2.PortionKey=N'Blank' THEN '' 
                                                  WHEN CHARINDEX('[', PO2.PortionKey) >0 then replace(PO2.PortionKey,N'[',N'[[') 
                                                  ELSE Po2.PortionKey END))
                                            , '%') 



sql-server-generalsql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Show some examples of PortionKey and @PartNumber values. How do you want to filter the rows?


0 Votes 0 ·

portion key is RXQ6R8
@PartNumber is RXQ6R8M2WSA-1020S

0 Votes 0 ·

1 Answer

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered
 WHERE @PartNumber LIKE (
     CONCAT(
         CASE LTRIM(RTRIM(Po.PortionKey)) WHEN N'Blank' THEN '' ELSE REPLACE(LTRIM(RTRIM(Po.PortionKey)), N'[', N'[[') END, 
         CASE LTRIM(RTRIM(Po2.PortionKey)) WHEN N'Blank' THEN '' ELSE REPLACE(LTRIM(RTRIM(Po2.PortionKey)), N'[', N'[[') END, 
         '%'
     )
 )
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.