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

ahmedsalah

Member
Joined
Sep 26, 2018
Messages
23
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
 

NoUserHere

Well-known member
Joined
Sep 5, 2018
Messages
2,138
Programming Experience
10+
Please fix your code tags to help with readability. You can edit your topic. Thanks

Topic also cross posted on :
 
Top Bottom