CODEWITHSUNDEEP
distinctsql-server-2014outer-apply
Joseph Shirk
Joseph Shirk

Reputation: 55

DISTINCT results of CTE Outer Apply return empty set

Summary:

Results of CTE using OUTER APPLY return duplicates due to dirty nature of data and results of RegexFind to split out commingled key=value pairs.

When using DISTINCT on that CTE, the result is empty. Is this a bug in SQL Server 2014?

I tried:

  1. Adding a layer on top of the CTE to implement DISTINCT has the same result.
  2. GROUP BY instead of DISTINCT has the same result.
  3. OVER PARTITION has the same result

NOTE: I was able to overcome this by exploiting submatchIDs from RegexFind, but this problem is concerning. I believe that OUTER APPLY messes up any attempt to get a DISTINCT resultset.

Actual code that cannot be easily tested on SQL Fiddle or DBFiddle.uk due to insufficient permissions to implement the RegexFind function (on those I have loaded results of OUTER APPLY instead of raw input data):

USE M1_MA
--SELECT @@version -- Microsoft SQL Server 2014 (SP3-GDR) (KB5021037) - 12.0.6174.8 (X64)     Jan  4 2023 08:59:45     Copyright (c) Microsoft Corporation    Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )
declare  @key_value_pair_regex_pat nvarchar(max) = 
    
    '\b(jm[o|s]\w+)\s*=\s*((?:(?!(?:jmo|jms))[^=])*)(?=\s+(?:jmo|jms)|$)'
    
; WITH JobOperationsChangeLog as (
SELECT --DISTINCT 
keys.JobID  JobID
, keys.JobAssemblyID JobAssemblyID
, keys.JobOperationID   JobOperationID

, xagChangeDate ChangeDate, xagChangeUserID ChangeUserID, ChangeLogID 
--, xagTableName
, xagChangeType --[U]pdate [I]nsert, etc
, xagTableKeyValues
, xagTableOldValues
    , xagTableNewValues
FROM ChangeLog 
JOIN (
  SELECT distinct
  xagChangeLogID ChangeLogID, xagChangeDate ChangeDate

  , REPLACE(JobID.Value,'jmoJobID = ','') JobID
  , REPLACE(JobAssemblyID.Value,'jmoJobAssemblyID = ','') JobAssemblyID
  , REPLACE(JobOperationID.Value, 'jmoJobOperationID = ','') JobOperationID
  
  FROM ChangeLog

  OUTER APPLY M1_MA.dbo.RegexFind (
    @key_value_pair_regex_pat
    ,xagTableKeyValues
    ,1
    ,1) JobID 

  OUTER APPLY M1_MA.dbo.RegexFind (
    @key_value_pair_regex_pat
    ,xagTableKeyValues
    ,1
    ,1) JobAssemblyID 

  OUTER APPLY M1_MA.dbo.RegexFind (
    @key_value_pair_regex_pat
    ,xagTableKeyValues
    ,1
    ,1) JobOperationID 

    ) keys on ChangeLogID=xagChangeLogID and ChangeDate=xagChangeDate
    WHERE xagTableName='JobOperations'
    AND (Keys.JobID NOT LIKE 'jmo%' and Keys.JobOperationID NOT LIKE 'jmo%' and Keys.JobAssemblyID NOT LIKE 'jmo%') -- eliminate non-value rows
    AND xagChangeUserID <> 'SQLAGENT - TSQL JOBS'
)
, inserts_BASE as (
    SELECT  
    JobID, JobAssemblyID, JobOperationID
    ,  ChangeDate FirstDate
    ,  ChangeUserID 
    ,  ChangeLogID
    --,  old.Error , old.FirstIndex , old.Match_ID , old.Submatch_ID , old.SubmatchValue , old.[Value]
    ,  new.Match_ID     MatchID
    ,  new.FirstIndex   new_FirstIndex
    ,  new.Submatch_ID  new_SubmatchID  
    ,  new.[Value]      
    --,  new.SubmatchValue -- omit for distinct rows
    FirstValue
    FROM JobOperationsChangeLog
/*
    OUTER APPLY M1_MA.dbo.RegexFind (
        @key_value_pair_regex_pat
        ,xagTableOldValues
        ,1
        ,1) old 
*/
    OUTER APPLY M1_MA.dbo.RegexFind (
        @key_value_pair_regex_pat
        ,xagTableNewValues
        ,1
        ,1) new 
    
    WHERE xagChangeType='I'
    AND Match_ID NOT IN (1,2,3) -- eliminate rows matching key fields
)
, inserts_DISTINCT as (
    SELECT DISTINCT  -- returns 0 rows!
       JobID, JobAssemblyID, JobOperationID

    ,  FirstDate
    ,  ChangeUserID 
    ,  ChangeLogID
    ,  MatchID

    ,  FirstValue

    FROM inserts_BASE
)
, inserts_GROUP as (  -- returns 0 rows!
    SELECT          
        JobID, JobAssemblyID, JobOperationID
    ,  FirstDate
    ,  ChangeUserID 
    ,  ChangeLogID
    ,  MIN(  FirstValue  )
    FirstValue
    ,  MatchID
    --,  new_FirstIndex
    FROM inserts_BASE
    GROUP BY JobID, JobAssemblyID, JobOperationID, FirstDate, ChangeUserID, ChangeLogID, MatchID
)
, inserts_OVER_PARTITION as (  -- returns 0 rows!
    SELECT          
        JobID, JobAssemblyID, JobOperationID
    ,  FirstDate
    ,  ChangeUserID 
    ,  ChangeLogID
    ,  MatchID

    ,  FIRST_VALUE (FirstValue) OVER( 
                PARTITION BY JobID, JobAssemblyID, JobOperationID, FirstDate, ChangeUserID, ChangeLogID, MatchID
                ORDER BY ChangeLogID, MatchID
        )
    FirstValue
    --,  new_FirstIndex
    FROM inserts_BASE
)
, inserts_SUBMATCH as (
    SELECT  JobID, JobAssemblyID, JobOperationID
    , FirstDate, ChangeUserID, ChangeLogID
    --, MatchID , new_FirstIndex , new_SubmatchID
    , FirstValue
    FROM inserts_BASE
    WHERE ISNULL(new_SubmatchID , 1 ) = 1 
    -- ^ only way to yeild distinct partition over JobID, JobAssemblyID, JobOperationID, ChangeDate, ChangeUserID, ChangeLogID, MatchID
)
--SELECT * from JobOperationsChangeLog
--SELECT   * from inserts_BASE
--SELECT * from inserts_DISTINCT        -- does not work! returns zero rows!
--SELECT * from inserts_GROUP           -- does not work! returns zero rows!
--SELECT * from inserts_OVER_PARTITION  -- does not work! returns zero rows!
SELECT * from inserts_SUBMATCH          -- regex metadata exploit workaround

In the online tests, DISTINCT or GROUP BY do work, but there I am not able to implement the base CTE that uses OUTER APPLY on the function. Too many restrictions. So my example is inadequate to test the hypothesis.

Sample data

Pre-filtered change log data (I am not responsible for the terrible implementation of logging field changes):

SELECT xagChangeLogID, xagChangeType, xagChangeDate, xagChangeUserID
, xagTableKeyValues, xagTableOldValues , xagTableNewValues 
FROM ChangeLog     
WHERE xagTableName='JobOperations'
  AND xagChangeUserID <> 'SQLAGENT - TSQL JOBS'
  AND xagChangeType = 'I' 
ORDER BY xagChangeLogID
xagChangeLogID xagChangeType xagChangeDate xagChangeUserID xagTableKeyValues xagTableNewValues
499725 I 2023-05-02 15:03:40.647 ALIPLY jmoJobID = 27180-02-01 jmoJobAssemblyID = 0 jmoJobOperationID = 545 jmoJobID = 27180-02-01 jmoJobOperationID = 545 jmoOperationType = 1 jmoWorkCenterID = DRYIN jmoProcessID = UNDER jmoProcessShortDescription = Roofing Dry-In jmoProcessLongDescriptionRTF = {\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Tahoma;}} \viewkind4\uc1\pard\f0\fs18 This part is for installation of all roof dry-in products and self-adhering membranes\par } jmoProcessLongDescriptionText = This part is for installation of all roof dry-in products and self-adhering membranes jmoQuantityPerAssembly = 1.000000 jmoProductionStandard = 1.0000 jmoStandardFactor = MP jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoFirm = 1 jmoEstimatedProductionHours = 0.02 jmoMachinesToSchedule = 1 jmoCreatedBy = aliply jmoCreatedDate = May 2 2023 3:03PM
499810 I 2023-05-02 15:07:56.777 VICGEN jmoJobID = 29121-01-01 jmoJobAssemblyID = 0 jmoJobOperationID = 10 jmoJobID = 29121-01-01 jmoJobOperationID = 10 jmoOperationType = 1 jmoWorkCenterID = ENGIN jmoProcessID = PERMI jmoProcessShortDescription = Permitting jmoQuantityPerAssembly = 1.000000 jmoProductionStandard = 1.0000 jmoStandardFactor = TD jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoEstimatedProductionHours = 8.00 jmoMachinesToSchedule = 1
499811 I 2023-05-02 15:07:56.777 VICGEN jmoJobID = 29121-01-01 jmoJobAssemblyID = 0 jmoJobOperationID = 20 jmoJobID = 29121-01-01 jmoJobOperationID = 20 jmoOperationType = 1 jmoWorkCenterID = BRAKE jmoProcessID = SMETA jmoProcessShortDescription = Sheet Metal Fab jmoQuantityPerAssembly = 1.000000 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1
499812 I 2023-05-02 15:07:56.790 VICGEN jmoJobID = 29121-01-01 jmoJobAssemblyID = 0 jmoJobOperationID = 30 jmoJobID = 29121-01-01 jmoJobOperationID = 30 jmoOperationType = 1 jmoWorkCenterID = TRUCK jmoProcessID = DELIV jmoProcessShortDescription = Delivery to Job jmoQuantityPerAssembly = 1.000000 jmoProductionStandard = 3.0000 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoEstimatedProductionHours = 3.00 jmoMachinesToSchedule = 1
499813 I 2023-05-02 15:07:56.790 VICGEN jmoJobID = 29121-01-01 jmoJobAssemblyID = 0 jmoJobOperationID = 510 jmoJobID = 29121-01-01 jmoJobOperationID = 510 jmoOperationType = 1 jmoWorkCenterID = IWIND jmoProcessID = IWIND jmoProcessShortDescription = Window and Door Install jmoQuantityPerAssembly = 1.000000 jmoSetupHours = 0.50 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1
499814 I 2023-05-02 15:07:56.790 VICGEN jmoJobID = 29121-01-01 jmoJobAssemblyID = 0 jmoJobOperationID = 520 jmoJobID = 29121-01-01 jmoJobOperationID = 520 jmoOperationType = 1 jmoWorkCenterID = CRANE jmoProcessID = CRANE jmoProcessShortDescription = Crane Operator jmoQuantityPerAssembly = 1.000000 jmoStandardFactor = MP jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1
502171 I 2023-05-03 07:36:28.000 LINHOC jmoJobID = 25140-04-01 jmoJobAssemblyID = 0 jmoJobOperationID = 535 jmoJobID = 25140-04-01 jmoJobOperationID = 535 jmoOperationType = 2 jmoAddedOperation = 1 jmoWorkCenterID = SUB jmoProcessID = 9FREI jmoProcessShortDescription = 905 - FREIGHT / POSTAGE jmoQuantityPerAssembly = 1.000000 jmoSetupHours = 1.00 jmoStandardFactor = MP jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoPartID = FREIGHT/POSTAGE jmoUnitOfMeasure = EA jmoSupplierOrganizationID = RBWAG jmoFirm = 1 jmoPurchaseOrderID = 31824 jmoEstimatedUnitCost = 10.40000 jmoCalculatedUnitCost = 10.40000 jmoStartDate = Aug 26 2022 12:00AM jmoDueDate = Feb 1 2023 12:00AM jmoMachinesToSchedule = 1 jmoCreatedBy = linhoc jmoCreatedDate = May 3 2023 7:36AM
502183 I 2023-05-03 07:54:05.963 PAMNEW jmoJobID = 29123-01-01 jmoJobAssemblyID = 0 jmoJobOperationID = 10 jmoJobID = 29123-01-01 jmoJobOperationID = 10 jmoOperationType = 1 jmoWorkCenterID = ENGIN jmoProcessID = PERMI jmoProcessShortDescription = Permitting jmoQuantityPerAssembly = 1.000000 jmoProductionStandard = 1.0000 jmoStandardFactor = TD jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoEstimatedProductionHours = 8.00 jmoMachinesToSchedule = 1
502184 I 2023-05-03 07:54:05.963 PAMNEW jmoJobID = 29123-01-01 jmoJobAssemblyID = 0 jmoJobOperationID = 20 jmoJobID = 29123-01-01 jmoJobOperationID = 20 jmoOperationType = 1 jmoWorkCenterID = BRAKE jmoProcessID = SMETA jmoProcessShortDescription = Sheet Metal Fab jmoQuantityPerAssembly = 1.000000 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1
502185 I 2023-05-03 07:54:05.963 PAMNEW jmoJobID = 29123-01-01 jmoJobAssemblyID = 0 jmoJobOperationID = 30 jmoJobID = 29123-01-01 jmoJobOperationID = 30 jmoOperationType = 1 jmoWorkCenterID = TRUCK jmoProcessID = DELIV jmoProcessShortDescription = Delivery to Job jmoQuantityPerAssembly = 1.000000 jmoProductionStandard = 3.0000 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoEstimatedProductionHours = 3.00 jmoMachinesToSchedule = 1
502186 I 2023-05-03 07:54:05.963 PAMNEW jmoJobID = 29123-01-01 jmoJobAssemblyID = 0 jmoJobOperationID = 510 jmoJobID = 29123-01-01 jmoJobOperationID = 510 jmoOperationType = 1 jmoWorkCenterID = IWIND jmoProcessID = IWIND jmoProcessShortDescription = Window and Door Install jmoQuantityPerAssembly = 1.000000 jmoSetupHours = 0.50 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1
502187 I 2023-05-03 07:54:05.963 PAMNEW jmoJobID = 29123-01-01 jmoJobAssemblyID = 0 jmoJobOperationID = 520 jmoJobID = 29123-01-01 jmoJobOperationID = 520 jmoOperationType = 1 jmoWorkCenterID = CRANE jmoProcessID = CRANE jmoProcessShortDescription = Crane Operator jmoQuantityPerAssembly = 1.000000 jmoStandardFactor = MP jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1
502195 I 2023-05-03 07:55:47.807 PAMNEW jmoJobID = 29124-01-01 jmoJobAssemblyID = 0 jmoJobOperationID = 10 jmoJobID = 29124-01-01 jmoJobOperationID = 10 jmoOperationType = 1 jmoWorkCenterID = ENGIN jmoProcessID = PERMI jmoProcessShortDescription = Permitting jmoQuantityPerAssembly = 1.000000 jmoProductionStandard = 1.0000 jmoStandardFactor = TD jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoEstimatedProductionHours = 8.00 jmoMachinesToSchedule = 1
502196 I 2023-05-03 07:55:47.807 PAMNEW jmoJobID = 29124-01-01 jmoJobAssemblyID = 0 jmoJobOperationID = 20 jmoJobID = 29124-01-01 jmoJobOperationID = 20 jmoOperationType = 1 jmoWorkCenterID = BRAKE jmoProcessID = SMETA jmoProcessShortDescription = Sheet Metal Fab jmoQuantityPerAssembly = 1.000000 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1
502197 I 2023-05-03 07:55:47.807 PAMNEW jmoJobID = 29124-01-01 jmoJobAssemblyID = 0 jmoJobOperationID = 30 jmoJobID = 29124-01-01 jmoJobOperationID = 30 jmoOperationType = 1 jmoWorkCenterID = TRUCK jmoProcessID = DELIV jmoProcessShortDescription = Delivery to Job jmoQuantityPerAssembly = 1.000000 jmoProductionStandard = 3.0000 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoEstimatedProductionHours = 3.00 jmoMachinesToSchedule = 1
502198 I 2023-05-03 07:55:47.807 PAMNEW jmoJobID = 29124-01-01 jmoJobAssemblyID = 0 jmoJobOperationID = 510 jmoJobID = 29124-01-01 jmoJobOperationID = 510 jmoOperationType = 1 jmoWorkCenterID = IWIND jmoProcessID = IWIND jmoProcessShortDescription = Window and Door Install jmoQuantityPerAssembly = 1.000000 jmoSetupHours = 0.50 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1
502199 I 2023-05-03 07:55:47.807 PAMNEW jmoJobID = 29124-01-01 jmoJobAssemblyID = 0 jmoJobOperationID = 520 jmoJobID = 29124-01-01 jmoJobOperationID = 520 jmoOperationType = 1 jmoWorkCenterID = CRANE jmoProcessID = CRANE jmoProcessShortDescription = Crane Operator jmoQuantityPerAssembly = 1.000000 jmoStandardFactor = MP jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1

Sample of inserts with fields split out, contains duplicate records

SELECT JobID, JobAssemblyID, JobOperationID, ChangeDate, ChangeUserID
, xagChangeLogID , xagTableNewValues
from JobOperationsChangeLog 
where xagChangeType = 'I'
order by xagChangeLogID
JobID JobAssemblyID JobOperationID ChangeDate ChangeUserID xagChangeLogID xagTableNewValues
27180-02-01 0 545 2023-05-02 15:03:40.647 ALIPLY 499725 jmoJobID = 27180-02-01 jmoJobOperationID = 545 jmoOperationType = 1 jmoWorkCenterID = DRYIN jmoProcessID = UNDER jmoProcessShortDescription = Roofing Dry-In jmoProcessLongDescriptionRTF = {\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Tahoma;}} \viewkind4\uc1\pard\f0\fs18 This part is for installation of all roof dry-in products and self-adhering membranes\par } jmoProcessLongDescriptionText = This part is for installation of all roof dry-in products and self-adhering membranes jmoQuantityPerAssembly = 1.000000 jmoProductionStandard = 1.0000 jmoStandardFactor = MP jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoFirm = 1 jmoEstimatedProductionHours = 0.02 jmoMachinesToSchedule = 1 jmoCreatedBy = aliply jmoCreatedDate = May 2 2023 3:03PM
29121-01-01 0 10 2023-05-02 15:07:56.777 VICGEN 499810 jmoJobID = 29121-01-01 jmoJobOperationID = 10 jmoOperationType = 1 jmoWorkCenterID = ENGIN jmoProcessID = PERMI jmoProcessShortDescription = Permitting jmoQuantityPerAssembly = 1.000000 jmoProductionStandard = 1.0000 jmoStandardFactor = TD jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoEstimatedProductionHours = 8.00 jmoMachinesToSchedule = 1
29121-01-01 0 20 2023-05-02 15:07:56.777 VICGEN 499811 jmoJobID = 29121-01-01 jmoJobOperationID = 20 jmoOperationType = 1 jmoWorkCenterID = BRAKE jmoProcessID = SMETA jmoProcessShortDescription = Sheet Metal Fab jmoQuantityPerAssembly = 1.000000 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1
29121-01-01 0 30 2023-05-02 15:07:56.790 VICGEN 499812 jmoJobID = 29121-01-01 jmoJobOperationID = 30 jmoOperationType = 1 jmoWorkCenterID = TRUCK jmoProcessID = DELIV jmoProcessShortDescription = Delivery to Job jmoQuantityPerAssembly = 1.000000 jmoProductionStandard = 3.0000 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoEstimatedProductionHours = 3.00 jmoMachinesToSchedule = 1
29121-01-01 0 510 2023-05-02 15:07:56.790 VICGEN 499813 jmoJobID = 29121-01-01 jmoJobOperationID = 510 jmoOperationType = 1 jmoWorkCenterID = IWIND jmoProcessID = IWIND jmoProcessShortDescription = Window and Door Install jmoQuantityPerAssembly = 1.000000 jmoSetupHours = 0.50 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1
29121-01-01 0 520 2023-05-02 15:07:56.790 VICGEN 499814 jmoJobID = 29121-01-01 jmoJobOperationID = 520 jmoOperationType = 1 jmoWorkCenterID = CRANE jmoProcessID = CRANE jmoProcessShortDescription = Crane Operator jmoQuantityPerAssembly = 1.000000 jmoStandardFactor = MP jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1
25140-04-01 0 535 2023-05-03 07:36:28.000 LINHOC 502171 jmoJobID = 25140-04-01 jmoJobOperationID = 535 jmoOperationType = 2 jmoAddedOperation = 1 jmoWorkCenterID = SUB jmoProcessID = 9FREI jmoProcessShortDescription = 905 - FREIGHT / POSTAGE jmoQuantityPerAssembly = 1.000000 jmoSetupHours = 1.00 jmoStandardFactor = MP jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoPartID = FREIGHT/POSTAGE jmoUnitOfMeasure = EA jmoSupplierOrganizationID = RBWAG jmoFirm = 1 jmoPurchaseOrderID = 31824 jmoEstimatedUnitCost = 10.40000 jmoCalculatedUnitCost = 10.40000 jmoStartDate = Aug 26 2022 12:00AM jmoDueDate = Feb 1 2023 12:00AM jmoMachinesToSchedule = 1 jmoCreatedBy = linhoc jmoCreatedDate = May 3 2023 7:36AM
29123-01-01 0 10 2023-05-03 07:54:05.963 PAMNEW 502183 jmoJobID = 29123-01-01 jmoJobOperationID = 10 jmoOperationType = 1 jmoWorkCenterID = ENGIN jmoProcessID = PERMI jmoProcessShortDescription = Permitting jmoQuantityPerAssembly = 1.000000 jmoProductionStandard = 1.0000 jmoStandardFactor = TD jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoEstimatedProductionHours = 8.00 jmoMachinesToSchedule = 1
29123-01-01 0 20 2023-05-03 07:54:05.963 PAMNEW 502184 jmoJobID = 29123-01-01 jmoJobOperationID = 20 jmoOperationType = 1 jmoWorkCenterID = BRAKE jmoProcessID = SMETA jmoProcessShortDescription = Sheet Metal Fab jmoQuantityPerAssembly = 1.000000 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1
29123-01-01 0 30 2023-05-03 07:54:05.963 PAMNEW 502185 jmoJobID = 29123-01-01 jmoJobOperationID = 30 jmoOperationType = 1 jmoWorkCenterID = TRUCK jmoProcessID = DELIV jmoProcessShortDescription = Delivery to Job jmoQuantityPerAssembly = 1.000000 jmoProductionStandard = 3.0000 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoEstimatedProductionHours = 3.00 jmoMachinesToSchedule = 1
29123-01-01 0 510 2023-05-03 07:54:05.963 PAMNEW 502186 jmoJobID = 29123-01-01 jmoJobOperationID = 510 jmoOperationType = 1 jmoWorkCenterID = IWIND jmoProcessID = IWIND jmoProcessShortDescription = Window and Door Install jmoQuantityPerAssembly = 1.000000 jmoSetupHours = 0.50 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1
29123-01-01 0 520 2023-05-03 07:54:05.963 PAMNEW 502187 jmoJobID = 29123-01-01 jmoJobOperationID = 520 jmoOperationType = 1 jmoWorkCenterID = CRANE jmoProcessID = CRANE jmoProcessShortDescription = Crane Operator jmoQuantityPerAssembly = 1.000000 jmoStandardFactor = MP jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1
29124-01-01 0 10 2023-05-03 07:55:47.807 PAMNEW 502195 jmoJobID = 29124-01-01 jmoJobOperationID = 10 jmoOperationType = 1 jmoWorkCenterID = ENGIN jmoProcessID = PERMI jmoProcessShortDescription = Permitting jmoQuantityPerAssembly = 1.000000 jmoProductionStandard = 1.0000 jmoStandardFactor = TD jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoEstimatedProductionHours = 8.00 jmoMachinesToSchedule = 1
29124-01-01 0 20 2023-05-03 07:55:47.807 PAMNEW 502196 jmoJobID = 29124-01-01 jmoJobOperationID = 20 jmoOperationType = 1 jmoWorkCenterID = BRAKE jmoProcessID = SMETA jmoProcessShortDescription = Sheet Metal Fab jmoQuantityPerAssembly = 1.000000 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1
29124-01-01 0 30 2023-05-03 07:55:47.807 PAMNEW 502197 jmoJobID = 29124-01-01 jmoJobOperationID = 30 jmoOperationType = 1 jmoWorkCenterID = TRUCK jmoProcessID = DELIV jmoProcessShortDescription = Delivery to Job jmoQuantityPerAssembly = 1.000000 jmoProductionStandard = 3.0000 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoEstimatedProductionHours = 3.00 jmoMachinesToSchedule = 1
29124-01-01 0 510 2023-05-03 07:55:47.807 PAMNEW 502198 jmoJobID = 29124-01-01 jmoJobOperationID = 510 jmoOperationType = 1 jmoWorkCenterID = IWIND jmoProcessID = IWIND jmoProcessShortDescription = Window and Door Install jmoQuantityPerAssembly = 1.000000 jmoSetupHours = 0.50 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1
29124-01-01 0 520 2023-05-03 07:55:47.807 PAMNEW 502199 jmoJobID = 29124-01-01 jmoJobOperationID = 520 jmoOperationType = 1 jmoWorkCenterID = CRANE jmoProcessID = CRANE jmoProcessShortDescription = Crane Operator jmoQuantityPerAssembly = 1.000000 jmoStandardFactor = MP jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1

Sample of filtered data with key fields split out

SELECT  * from inserts
JobID JobAssemblyID JobOperationID FirstDate ChangeUserID xagChangeLogID Match_ID FirstIndex FirstValue
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 4 68 jmoWorkCenterID = TRUCK
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 4 68 jmoWorkCenterID = TRUCK
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 5 92 jmoProcessID = DELIV
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 6 113 jmoProcessShortDescription = Delivery to Job
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 6 113 jmoProcessShortDescription = Delivery to Job
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 7 158 jmoQuantityPerAssembly = 1.000000
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 8 192 jmoProductionStandard = 3.0000
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 8 192 jmoProductionStandard = 3.0000
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 9 223 jmoStandardFactor = TH
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 10 246 jmoSetupRate = 25.00
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 10 246 jmoSetupRate = 25.00
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 11 267 jmoProductionRate = 25.00
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 12 293 jmoOperationQuantity = 1.00000
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 12 293 jmoOperationQuantity = 1.00000

Sample of de-duplicated result via submatch exploit (other methods such as DISTINCT yeild empty set):

SELECT * from inserts_SUBMATCH
JobID JobAssemblyID JobOperationID FirstDate ChangeUserID ChangeLogID FirstValue
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 jmoWorkCenterID = TRUCK
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 jmoProcessID = DELIV
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 jmoProcessShortDescription = Delivery to Job
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 jmoQuantityPerAssembly = 1.000000
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 jmoProductionStandard = 3.0000
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 jmoStandardFactor = TH
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 jmoSetupRate = 25.00
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 jmoProductionRate = 25.00
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 jmoOperationQuantity = 1.00000
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 jmoMachineType = 1
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 jmoUnitOfMeasure = EA
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 jmoFirm = 1
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 jmoEstimatedProductionHours = 3.00
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 jmoMachinesToSchedule = 1
29127-01-01 0 510 2023-05-03 08:12:11.583 PAMNEW 502295 jmoWorkCenterID = IWIND
29127-01-01 0 510 2023-05-03 08:12:11.583 PAMNEW 502295 jmoProcessID = IWIND
29127-01-01 0 510 2023-05-03 08:12:11.583 PAMNEW 502295 jmoProcessShortDescription = Window and Door Install
29127-01-01 0 510 2023-05-03 08:12:11.583 PAMNEW 502295 jmoQuantityPerAssembly = 1.000000

Upvotes: 0

Views: 84

Answers (0)

Related Questions