May 15, 2021 at 6:23 pm
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))
, '%')
portion key is RXQ6R8
@PartNumber is RXQ6R8M2WSA-1020S
May 16, 2021 at 12:13 pm
It seems the CASE conditions could be simplified because it's not necessary to test the CHARINDEX prior to REPLACE. If the search characters are not present in the string then the function does nothing.
Current CASE condition
case when Po.PortionKey=N'blank' then ''
when charindex(N'[', Po.PortionKey) >0
then replace(Po.PortionKey,N'[',N'[[')
else Po.PortionKey end
Equivalent
case when Po.PortionKey=N'blank' then ''
else replace(Po.PortionKey,N'[',N'[[') end
Maybe it's clearer what the code does if the CASE conditions and concatenation are removed from the WHERE clause using CROSS APPLY and VALUES. p1.portion_key_decode and p2.portion_key_decode are concatenated together along with the text wildcard '%' character and compared to @PartNumber
select top 1 *
from dbo.GlobalPartNumberPortions Po /*WITH(NOLOCK)*/
cross join (values (ltrim(rtrim(case when Po.PortionKey=N'Blank' then ''
else replace(PO.PortionKey,N'[',N'[[') end)))) p1(portion_key_decode)
JOIN dbo.GlobalPartNumber GOl /*WITH(NOLOCK)*/ ON GOl.GlobalPnId = Po.GlobalPnId
AND Po.GroupId = 1
JOIN Parts.Nop_PartsFamily pf /*WITH(NOLOCK)*/ ON GOl.FamilyId = pf.PartFamilyID
JOIN dbo.GlobalPartNumberPortions Po2 /*WITH(NOLOCK)*/ ON GOl.GlobalPnId = Po2.GlobalPnId
AND Po2.GroupId = 2
cross join (values (ltrim(rtrim(case when Po.PortionKey=N'Blank' then ''
else replace(PO2.PortionKey,N'[',N'[[') end)))) p2(portion_key_decode)
cross apply (values (concat(p1.portion_key_decode, p2.portion_key_decode, '%'))) both(portion_keys)
where @PartNumber like both.portion_keys;
-- @PartNumber is 'RXQ6R8M2WSA-1020S'
-- When p1.portion_key_decode is 'RXQ6R8' and/if p2.portion_key_decode equals (or is a substring of) 'M2WSA-1020S'
-- Then the WHERE condition is evaluated as TRUE
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply