Resolved when null flag updated and conflict flag updated on SQL query statement?

ahmedsalah

Active member
Joined
Sep 26, 2018
Messages
32
Programming Experience
3-5
I work on SQL server 2012 .really I don't understand what statement below do updatedand when
when NullFlag updated
and
when conflict flag updated

can you explain to me according to sample below
<code>
result of query data details rows without grouping :

Masked_ID DocumentID PartID NULLCount
29283933 76724 31345983 NULL
29283933 76724 31345984 NULL
29283933 76724 31345985 NULL
29283933 76724 31345986 NULL
29283933 NULL NULL 1
29283933 NULL NULL 1
29283933 76724 31345989 NULL
29283933 NULL NULL 1
29283933 NULL NULL 1
29283933 76724 31345992 NULL
UPDATE FFFF
SET
Conflictflag= IIF((NotNULL+NuLLCount)<>RowsCount AND Ex.MaskExceptionID IS NULL ,CONCAT(Conflictflag,'PCN','|'),Conflictflag),
NULLflag=IIF((NotNULL+NuLLCount)=RowsCount AND NuLLCount>0 AND NULEX.NULLExceptionID IS NULL,CONCAT(NULLflag,'PCN','|'),NULLflag)
FROM
(
--SELECT Masked_ID,SUM(CNT)/COUNT(DocumentID) AS NotNULL ,SUM(NULLCount)AS NuLLCount
SELECT Masked_ID,SUM(CNT)/nullif(COUNT(DocumentID),0) AS NotNULL ,SUM(NULLCount)AS NuLLCount
FROM (
SELECT FF.Masked_ID, LC.DocumentID,
COUNT(DISTINCT LC.PartID) AS CNT,
COUNT( CASE WHEN DocumentID IS NULL THEN 1 ELSE NULL END )NULLCount
FROM ExtractReports.dbo.MultiMask FF
INNER JOIN Parts.Nop_Part ptt WITH(NOLOCK) ON ptt.Masked_ID=ff.Masked_ID
LEFT JOIN PCN.DocumentParts LC WITH(NOLOCK) ON ptt.PartID=LC.PartID
WHERE FF.PCNs LIKE '%|%'
GROUP BY FF.Masked_ID ,LC.DocumentID
)DD
GROUP BY DD.Masked_ID
) DDFF
INNER JOIN ExtractReports.dbo.MultiMask FFFF ON DDFF.Masked_ID=FFFF.Masked_ID
LEFT JOIN [ConflictReport].dbo.MaskExceptions EX ON EX.MaskID=FFFF.Masked_ID AND EX.FunctionName='PCN'
LEFT JOIN [ConflictReport].dbo.NULLExceptions NULEX ON NULEX.MaskID=FFFF.Masked_ID AND NULEX.FunctionName='PCN'
WHERE
FFFF.PCNs LIKE '%|%'
</code>
on case of data above what must updated Null flag or conflict flag
and why
can you please help me
 
Please fix your code tags to help with readability. You can edit your topic. Thanks

Topic also cross posted on :
 
Back
Top Bottom