11 Replies Latest reply: Oct 29, 2015 3:45 AM by Giovanni Vasti RSS

    ETL with NOT IN

      Hello guys,

       

      I'm translating many queries and I have some doubts on etl strategy.

      There are cases with nested subqueries where I must do the selections on fields on all used tables.

      Here's an example:

       

      SELECT

        table01.C_PDS,

        table01.VOLUME

      FROM

        table01

      WHERE

        (

        table01.C_CARRIER  =  @Carrier

        AND  table01.V_ANNO =    year(@StartDate)

        AND  table01.C_PDS  IN 

          (SELECT

              table02.PDS FROM

              table02

          WHERE

        (

        table02.DT_INI  <=  @StartDate

        AND  table02.DT_FINE  >=  @EndDate

        AND  table02.COD_TRASP  =  @Carrier

        AND  table02.PDS  NOT IN 

              (SELECT

                  table03.C_PDR FROM

                  table03

              WHERE

            (

            table03.V_ANNO = year(@StartDate)

            AND  table03  =  @Carrier

        )

      )))))

       

      @Carrier, @StartDate and @EndDate are the values that i'll use on the front end with set analysis, lists etc.

      Please note that I'm working with large volumes of data and each table corresponds to a QVD file.

       

      What's the best approach that you think?

      Thanks in advance