10 Replies Latest reply: Apr 18, 2016 7:26 PM by Rean Fadyl RSS

    Load *; on top of SQL SELECT * not working

    Rean Fadyl

      Hi Experts,

       

      Can someone please answer why the third example of load scripting below doesn't work?

       

      The first two work fine, but the third one creates a bunch of $Orphan tables, as opposed to one PATIENTS_V3 table.

       

      / ----------------------------------------

      // Load the PATIENTS data

      // ----------------------------------------

       

      //Works version 1

       

      PATIENTS_V1:

       

      LOAD *;

      SQL SELECT *

      FROM PIMS.PATIENTS

      Where

      MODIF_DTTM > to_date('$(vModifyDateTime)','DD/MM/YYYY');

       

       

      //Works version 2

       

      PATIENTS_V2:

       

      LOAD

      "PATNT_REFNO",

          "SEXXX_REFNO",

          FORENAME;

      SQL SELECT *

      from

          PIMS.PATIENTS patnt,

              PIMS.PROVIDER_SPELLS prvsp

      where PATNT.PATNT_REFNO=PRVSP.PATNT_REFNO

             and  nvl(PRVSP.ARCHV_FLAG,'N')='N'

             and PRVSP.MODIF_DTTM > sysdate-101;

       

       

      //Doesn't Work???

       

      PATIENTS_V3:

       

      LOAD *;

      SQL SELECT *

      from

          PIMS.PATIENTS patnt,

              PIMS.PROVIDER_SPELLS prvsp

      where PATNT.PATNT_REFNO=PRVSP.PATNT_REFNO

             and  nvl(PRVSP.ARCHV_FLAG,'N')='N'

             and PRVSP.MODIF_DTTM > sysdate-101;

       

      Cheers

       

      Rean