3 Replies Latest reply: Mar 28, 2014 3:45 AM by Jens Zibell RSS

    "Where not exists" not working

    Jens Zibell

      Hi,

       

      I'll try to build a table that shall be updated each month. To be sure, that not the data for the same month is added again, I'll try to use the "where not exists clause".

       

      Target table shall be:

       

      TERRITORY_ID     Month     Value

      a                         201401     50

      a                         201402     33

      a                         201403     55

      b                         201401     60

      b                         201402     -

      b                         201403     56

       

      Situation is now, that with the "where not exists" records are not concatenated to the table.

       

      Here's my code:

      IF(vCheckPlanQvdExists) THEN

        Trace QVD exists, loading;

        Plan_Input:

        LOAD TERRITORY_ID,

          Monat,

            Value,

            TERRITORY_ID & Monat as Key

        FROM

      ..\QVD\edm_SF_Plan.qvd

        (qvd);

      ENDIF

       

      MONTHLY_TEMPLATE:

      LOAD Distinct

        TERRITORY_ID,

        $(vCurrMonth) as Monat,

          TERRITORY_ID & $(vCurrMonth) as Key

      FROM

      ..\QVD\edm_SF_opp_full.qvd

      (qvd)

      where len(trim(TERRITORY_ID)) > 0;

       

       

       

       

      MONTHLY:

      join (MONTHLY_TEMPLATE)

      LOAD

        TERRITORY_ID,

        $(vCurrMonth) as Monat,

        //OLI_TMP.SERVICE_DATE_YEARMONTH//,

        sum(OLI_TMP.TCV_REPORTED) as Value

      Resident OPP

      where len(trim(TERRITORY_ID)) > 0

      group by TERRITORY_ID, OLI_TMP.SERVICE_DATE_YEARMONTH;

       

       

      IF(vCheckPlanQvdExists) THEN

        Trace Updating QVD;

        Concatenate (Plan_Input)

        LOAD

        Key,

        TERRITORY_ID,

        Monat,

        Value

        Resident MONTHLY_TEMPLATE where not exists (Key, Key);

      ELSE

        Plan_Input:

        LOAD *, 'Y' as New Resident MONTHLY_TEMPLATE;

        drop Field New;

        TRACE Creating new QVD;

      ENDIF

       

       

       

       

      Thanks