What must updated null flag updated or conflict flag updated?

  • ahmed_elbarbary.2010

    SSCrazy

    Points: 2709

    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

    SSCrazy

    Points: 2709

    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)

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply