What must updated null flag updated or conflict flag updated?

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  September 17, 2020 at 12:57 am

  #3789031

  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

  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 '%|%'

   

  on case of data above what must updated Null flag or conflict flag

  and why

  can you please help me

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  September 17, 2020 at 8:08 am

  #3789056

  sample data

  CREATE TABLE [Parts].[Nop_Part](
  [PartID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
  [PartNumber] [nvarchar](70) NOT NULL,
  [Masked_ID] [int] NULL,
   CONSTRAINT [PK_Nop_Part] PRIMARY KEY CLUSTERED 
  (
  [PartID] ASC
  )
  
  insert into [Parts].[Nop_Part](PartID,PartNumber,Masked_ID)
  values
  ('31345983','PLT0603Z1372ABT0','29283933') ,
  ('31345984','PLT0603Z1372ABT1','29283933') ,
  ('31345985','PLT0603Z1372ABT5','29283933') ,
  ('31345986','PLT0603Z1372ABTF','29283933') ,
  ('31345989','PLT0603Z1372ABTS','29283933') ,
  ('31345992','PLT0603Z1372ABTS','29283933') 
  
  CREATE TABLE [PCN].[DocumentParts](
  [DocumentPartID] [int] IDENTITY(1,1) NOT NULL,
  [DocumentID] [int] NOT NULL,
  [PartID] [int] NULL,
   CONSTRAINT [PK_PCNParts] PRIMARY KEY CLUSTERED 
  (
  [DocumentPartID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]
     insert into [PCN].[DocumentParts](DocumentID,PartID) 
     values
    
  (76724,31345983),
  (76724,31345984) ,
  (76724,31345985),
  (76724,31345986) ,
  (76724,31345989),
  (76724,31345992)
  
  CREATE TABLE [dbo].[MaskExceptions](
  [MaskExceptionID] [int] IDENTITY(1,1) NOT NULL,
  [MaskID] [int] NOT NULL,
  [FunctionName] [varchar](50) NULL,
  [FunctionID] [int] NULL
  ) ON [PRIMARY]
  insert into [dbo].[MaskExceptions] (MaskID,FunctionName,FunctionID)
  values
  (29283933,'Introduction Date',4)
  
  CREATE TABLE [dbo].[NULLExceptions](
  [NULLExceptionID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
  [MaskID] [int] NOT NULL,
  [FunctionName] [varchar](50) NULL,
  [FunctionID] [int] NULL
  ) ON [PRIMARY]
  
    insert into [dbo].[NULLExceptions] (MaskID,FunctionName,FunctionID)
  values
  (29283933,'PCN',18)
  
  
   CREATE TABLE [dbo].[MultiMask](
  [Masked_ID] [int] NOT NULL,
  [RowsCount] [int] NULL,
  [IntroductionDate] [varchar](150) NULL,
  [PCNs] [varchar](1200) NULL,
  [NULLflag] [varchar](1000) NULL,
  [Conflictflag] [varchar](2000) NULL,
  [MaskExceptions] [nvarchar](500) NULL,
   CONSTRAINT [PK_MultiMask] PRIMARY KEY CLUSTERED 
  (
  [Masked_ID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]
   insert into [dbo].[MultiMask](Masked_ID,RowsCount,PCNS,MaskExceptions,NULLflag,Conflictflag)
   values
   (29283933,10,'NULL(4)|76724(6)','Introduction Date',NULL,NULL)