1 Reply Latest reply: Apr 7, 2010 6:00 PM by John Witherspoon RSS

    Drop record from a table

      Hi guys,

      Which is the fast way to drop record from a table, starting from an "exclusion table".

      I have let's say 'table1' of all my record, but I've to exclude those record that I've in 'ExclusionTable'

      Matching field are 2. So which is in your opinion the fastest way to exclude record of 'Exclusion Table' from 'Table1'

       

      Thanks

      Sergio

        • Drop record from a table
          John Witherspoon

          The fastest way is probably to not load the excluded rows in the first place:

          [Exclusion Table]:
          LOAD Field1 & ',' & Field2 as ExclusionKey
          ...
          ;
          [Main Table]
          LOAD
          UniqueID
          ,some fields
          ,Field1
          ,Field2
          ,some other fields
          ...
          WHERE NOT EXISTS (ExclusionKey,Field1 & ',' & Field2)
          ;

          If you MUST load the main table first (or if using QVDs, where the not exists above would, I believe, cause an unoptimized load), probably something like this:

          [Main Table]
          LOAD
          UniqueID
          ,some fields
          ,Field1
          ,Field2
          ,some other fields
          ...
          ;
          [Exclusion Table]:
          LOAD Field1 & ',' & Field2 as ExclusionKey
          ...
          ;
          INNER JOIN ([Main Table])
          LOAD UniqueID
          RESIDENT [Main Table]
          WHERE NOT EXISTS (ExclusionKey,Field1 & ',' & Field2)
          ;