5 Replies Latest reply: Dec 20, 2016 1:01 PM by Eduardo DImperio RSS

    Store a value of load statement into a variable

    Eduardo DImperio

      Hi everyone,

       

      I read about use variable, but dont know how get a list of values. I have this code:

       

      Exclusao:

      LOAD

      DISTINCT OID_METER AS EXCLUDE_METER

      RESIDENT ANALISE_TMP

      WHERE CONSUMODIA0=0 OR CONSUMODIA1=0 OR CONSUMODIA2=0;

       

      and i want to store all values of that select into a variable to use in other table.

       

      I thought the sintaxe could be LET vExclude = PEEK(EXCLUDE_METER,-1,Exclusao); but it not work

       

      someone could help me?

       

      Thanks

        • Re: Store a value of load statement into a variable
          Manish Kachhia

          If there are more than one values, you need to use CONCAT function to concatenate all values and then store it in a Variable.

           

          What is the exact use of this variable?

            • Re: Store a value of load statement into a variable
              Eduardo DImperio

              Hi Manish,

               

              I need to do that:

               

              //Extract all meter where consumodia =0

               

              Exclusao:

              LOAD

              DISTINCT OID_METER AS EXCLUDE_METER

              RESIDENT ANALISE_TMP

              WHERE CONSUMODIA0=0 OR CONSUMODIA1=0 OR CONSUMODIA2=0;

               

               

              //LET vExclude = Get all values and store like a list

               

               

              ANALISE:

              LOAD

                  ID_LEITURA,

                 NAME_SYSTEM,

                  NAME_GROUP,

                  NEIGHB_SYSTEM,

                 CITY_SYSTEM,

                  OID_METER,

                 NAME_TYPE_METER,

                  TYPE_CONSTANT,

                  NAME_UC,

                  ADDRESS_BLOCK_UC,

                  DATE_READ,

                  VALUE_READ,

                  CONSUMODIA0,

                  CONSUMODIA1,

                  CONSUMODIA2,

                  HORA

                 RESIDENT ANALISE_TMP

                  WHERE NOT EXISTS(EXCLUDE_OID_METER, OID_METER) //select all where EXCLUDE_OID_METER<>OID_METER

                  ORDER BY OID_METER, HORA DESC;

                • Re: Store a value of load statement into a variable
                  Manish Kachhia

                  Exclusao:

                  LOAD

                      CHR(39) & Concat(DISTINCT OID_METER,Chr(39)&','&Chr(39)) &CHR(39) AS EXCLUDE_METER

                  RESIDENT ANALISE_TMP

                  WHERE CONSUMODIA0=0 OR CONSUMODIA1=0 OR CONSUMODIA2=0;

                   

                  Let vExclude = Peek('EXCLUDE_METER',0,'Exclusao');

                   

                  Drop Table Exclusao;

                   

                  Now use as below.

                   

                  Load

                  ..

                  ..

                  ..

                  RESIDENT ANALISE_TMP

                      WHERE NOT EXISTS($(vExclude), OID_METER)



                  If the above now work then use

                  WHERE NOT EXISTS('$(vExclude)', OID_METER)

                    • Re: Store a value of load statement into a variable
                      Eduardo DImperio

                      Hi Manish,

                       

                      I got the error:

                       

                      WHERE NOT EXISTS($(vExclude), OID_METER)

                      Ocorreram os seguintes erros:

                      Error in expression: ')' expected


                      WHERE NOT EXISTS('$(vExclude)', OID_METER)


                      Exists takes 1-2 parameters


                      Other question, i concatenate in string, there someway to create a list like in c#  vExclude[] or something like that?


                      And one last question, why my code consume all memory of QS and crash?


                      ANALISE_TMP:

                      LOAD

                        ID_LEITURA,

                          NAME_SYSTEM,

                          NAME_GROUP,

                          NEIGHB_SYSTEM,

                          CITY_SYSTEM,

                          OID_METER,

                          NAME_TYPE_METER,

                          TYPE_CONSTANT,

                          NAME_UC,

                          ADDRESS_BLOCK_UC,

                          DATE_READ,

                          VALUE_READ,

                          Date(DATE_READ) AS HORA,

                          If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT)  as CONSUMODIA0,

                          If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()-1),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT) as CONSUMODIA1,

                          If(OID_METER=  Peek(OID_METER) and Floor(DATE_READ)= Floor(Today()-2),(Peek(VALUE_READ)- VALUE_READ) * TYPE_CONSTANT) as CONSUMODIA2,

                          If((Peek(VALUE_READ)- VALUE_READ)=0Crash.JPG,OID_METER)  as EXCLUDE_METER

                          RESIDENT TMP2

                          ORDER BY OID_METER, DATE_READ DESC;

                       

                      DROP TABLE TMP,TMP2;

                       

                      Resultado:

                      LOAD

                      DISTINCT OID_METER

                      RESIDENT ANALISE_TMP

                      WHERE NOT EXISTS(EXCLUDE_METER, OID_METER);




                • Re: Store a value of load statement into a variable
                  Eduardo DImperio

                  I've change the code and now compile

                   

                  Exclusao:

                  LOAD

                    OID_METER,

                    EXCLUDE_METER

                  RESIDENT ANALISE_TMP

                  WHERE NOT EXISTS (EXCLUDE_METER,OID_METER);

                   

                   

                  INNER JOIN (Exclusao)

                  ANALISE:

                  LOAD

                    OID_METER,

                      ID_LEITURA,

                      NAME_SYSTEM,

                      NAME_GROUP,

                      NEIGHB_SYSTEM,

                      CITY_SYSTEM,

                      NAME_TYPE_METER,

                      TYPE_CONSTANT,

                      NAME_UC,

                      ADDRESS_BLOCK_UC,

                      DATE_READ,

                      VALUE_READ,

                      CONSUMODIA0,

                      CONSUMODIA1,

                      CONSUMODIA2,

                      If (OID_METER =PEEK(OID_METER), PEEK(CONSUMODIA0)- CONSUMODIA0) AS CONSUMO_REAL0,

                      If (OID_METER =PEEK(OID_METER), PEEK(CONSUMODIA1)- CONSUMODIA1) AS CONSUMO_REAL1,

                      If (OID_METER =PEEK(OID_METER), PEEK(CONSUMODIA2)- CONSUMODIA2) AS CONSUMO_REAL2,

                      HORA

                      RESIDENT ANALISE_TMP

                  //    WHERE  (CONSUMODIA0>=0.005 OR CONSUMODIA1>=0.005 OR CONSUMODIA2>=0.005)

                      where (HOUR(HORA)=2 OR HOUR(HORA)=5)

                      ORDER BY OID_METER DESC;

                   

                  But thanks to teach how to store in a variable !