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

ahmedsalah

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

Sheepings

Senior Programmer
Staff member
Joined
Sep 5, 2018
Messages
1,518
Location
UK
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