10 Replies Latest reply: May 11, 2018 8:52 AM by Eduardo DImperio RSS

    Use a Field from qvd in a where oracle clause

    Eduardo DImperio

      Hi,

       

      I've a query to store into a qvd, but after i need to be incrememental, but for that i need to use a field from my qvd into oracle clause.

      Until now i use some kind a workaround creating a string with all rows in the field.

       

       

      //******************************************************************************

      //FIELD from QVD

       

      MO_ISSUE_FIELD_STRING:

      ISSUE_ID

          FROM

          [lib://Dados/TESTE/MO_ISSUE_FIELD_STRING.qvd](qvd);

      //******************************************************************************

       

      CONCATENATE(MO_ISSUE_FIELD_STRING)

      //******************************************************************************

      MO_ISSUE_FIELD_STRING:

      Load

          ID_ISSUE_FIELD_STRING AS ISSUE_FIELD_STRING_ID,

          ISSUE_FIELD_ID,

          ISSUE_ID,

          FIELD_ID  AS CATEGORY_ID,

          FIELD_VALUE AS CATEGORY_VALUE,

          1 AS ID_STORAGE_TYPE

      ;

      select

          ID_ISSUE_FIELD_STRING,

          ISSUE_FIELD_ID,

          ISSUE_ID,

          FIELD_ID,

          FIELD_VALUE

      from MO_ISSUE_FIELD_STRING

      where

      ????? // I WOULD LIKE TO PUT IN HERE ONLY ID_ISSUE THAT NOT IN MY QVD


      //********************************************************************************

      //WORKAROUND

      LET vWHERE='';

      LET Aux='';

       

      ID_ISSUE_LIST:

      load

      DISTINCT

      ISSUE_ID

      RESIDENT MO_ISSUE;

       

      For Each ISSUE_ID in FieldValueList('ISSUE_ID')

       

      Aux='ISSUE_ID='& $(ISSUE_ID)&' OR ';

      vWHERE=Aux& vWHERE;

      NEXT

       

      If Aux='' then

      exit Script;

      endif

      Aux=LEFT(vWHERE,LEN(vWHERE)-3);

      ********************************************************************************

        • Re: Use a Field from qvd in a where oracle clause
          Marcus Sommer

          If the amount of your data are rather small and/or your database is fast enough to deliver the data in your needed time-frame you could use an where exists clause on the preceeding load like:

           

          ...

          MO_ISSUE_FIELD_STRING:

          ISSUE_ID

              FROM

              [lib://Dados/TESTE/MO_ISSUE_FIELD_STRING.qvd](qvd);

           

          ...

           

          MO_ISSUE_FIELD_STRING:

          Load

              ID_ISSUE_FIELD_STRING AS ISSUE_FIELD_STRING_ID,

              ISSUE_FIELD_ID,

              ISSUE_ID,

              FIELD_ID  AS CATEGORY_ID,

              FIELD_VALUE AS CATEGORY_VALUE,

              1 AS ID_STORAGE_TYPE

          where not exists(ISSUE_ID)

          ;

          select

              ID_ISSUE_FIELD_STRING,

              ISSUE_FIELD_ID,

              ISSUE_ID,

              FIELD_ID,

              FIELD_VALUE

          from MO_ISSUE_FIELD_STRING;

          ...

           

          But this meant that each record from the database is pulled and checked.

           

          If this this not feasible I would go with your workaround of loading the data within a loop only if no other alternatives would be possible because I assume that this would be the slowest approach.

           

          And this could be to concat the ISSUE_ID as a string and using them within a not in() clause - AFAIK this often limited to a certain number of parameters and/or number of chars. Also thinkable might be to use something like max(ISSUE_ID) if there could be ensured that each new ID must be greater than the previous ones.

           

          Another way would be to rewrite the in Qlik loaded ID's again into the database - maybe with something like this: Write Back to Database via ETL process (using CSV or XML) - or maybe even easier to store these ID's within a parallel process directly within the database and using these ID's within a join to filter the data.

           

          - Marcus