5 Replies Latest reply: Feb 16, 2017 9:24 AM by Eduardo DImperio RSS

    Concatenated a table

    Eduardo DImperio

      Hi people,

       

      I need to make a incremental charge, for that reason i load my qvd and load my new query, after i need to join only the results that doesn't exist on the qvd.

       

      How can i exclude this values, cause Exist() could not use in diferent tables?

       

      ****load the qvd

       

      CARGA_INCREMENTAL:

      LOAD

      *

      FROM [lib://MI_csv/Leituras/Individual_Read/RS_INDIVIDUAL_READ_$(vCarga).qvd](qvd);

       

      ***** get the latest update

       

      DATA_ATUALIZACAO:

       

      LOAD

      timestamp(MAX(DATA_SERV),'DD/MM/YYYY hh:mm:ss') AS DATAMAX

       

      RESIDENT CARGA_INCREMENTAL;

       

      Let vDatamax=FieldValue('DATA_SERV',1);

       

       

      Concatenate(CARGA_INCREMENTAL)

       

       

      CARGA_INCREMENTAL2:

      LOAD

      ID_SYSTEM,

      DATE_READ,

      ORA_ROWSCN,

      PORT,

      VALUE_READ,

      DATA_SERV,

      CONCENTRATOR

      WHERE DATA_SERV>'$(vDatamax)'

      ;

      SELECT

      id_system,

      port,

      date_read,

      value_read,

      concentrator,

      ora_rowscn,

      scn_to_timestamp(ora_rowscn) data_serv

      from rs_individual_read

      where   date_read between TO_DATE(SYSDATE) and TO_DATE(SYSDATE+1);

        • Re: Concatenated a table
          Petter Skjolden

          I think that this line:

           

          Let vDatamax=FieldValue('DATA_SERV',1);


          Should be:


          Let vDatamax=FieldValue('DATA_MAX',1);

          • Re: Concatenated a table
            Jayaseelan K

            Hi ,

             

            Try this for setting the max date,

             

            Let vDatamax= Num(Peek('DATAMAX',0,'DATA_ATUALIZACAO'));

             

            Thanks,

            • Re: Concatenated a table
              Eduardo DImperio

              Hi guys,

              Petter is right about the field name, but im still with the same result 2 tables that i dont know how to concate.

              But really thank's for help me

              • Re: Concatenated a table
                Marcus Malinow

                You've got a little bit of redundant code. Delete 'CARGA_INCREMENTAL2, as I presume this is overriding the prior Concatenate statement:

                 

                 

                Concatenate(CARGA_INCREMENTAL)

                CARGA_INCREMENTAL2:

                LOAD

                ID_SYSTEM,

                 

                • Re: Concatenated a table
                  Eduardo DImperio

                  Hi everyone, i finally did. Follow the final code if someone need for something

                   

                  CARGA_INCREMENTAL:

                   

                  LOAD

                  *

                  FROM [lib://MI_csv/Leituras/Individual_Read/RS_INDIVIDUAL_READ_$(vCarga).qvd](qvd);

                   

                  Concatenate(CARGA_INCREMENTAL)

                   

                  LOAD

                  ID_SYSTEM,

                  DATE_READ,

                  PORT,

                  VALUE_READ,

                  CONCENTRATOR,

                  ID_SYSTEM&'|'&CONCENTRATOR&'|'&PORT AS ID_LEITURA;

                  SELECT

                  id_system,

                  port,

                  date_read,

                  value_read,

                  concentrator,

                  ora_rowscn,

                  scn_to_timestamp(ora_rowscn) data_serv

                  from rs_individual_read

                  //where   ora_rowscn>timestamp_to_scn(SYSDATE-1) AND (date_read between TO_DATE(SYSDATE-2) and TO_DATE(SYSDATE+1));

                  where   ora_rowscn>timestamp_to_scn(sysdate - interval '1' hour)

                  AND(date_read between TO_DATE(SYSDATE-2) and TO_DATE(SYSDATE+1));

                   

                  //this solved my problem

                  NoConcatenate

                  QVD_ATUALIZADO:

                   

                  Load

                  Distinct

                  ID_SYSTEM,

                  DATE_READ,

                  PORT,

                  VALUE_READ,

                  CONCENTRATOR,

                  ID_SYSTEM&'|'&CONCENTRATOR&'|'&PORT AS ID_LEITURA

                  RESIDENT CARGA_INCREMENTAL;

                   

                  Drop Table CARGA_INCREMENTAL;