9 Replies Latest reply: Dec 7, 2016 2:17 PM by Eduardo DImperio RSS

    Problem using "IF"

    Eduardo DImperio

      Hi,

       

      Please, can someone tell me where im wrong

       

      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 AS DIA0,

          VALUE_READ,

          VALUE_READ*TYPE_CONSTANT AS LEITURA_FINAL,

          IF(OID_METER=PEEK(OID_METER) AND FLOOR(DATE_READ)=FLOOR(TODAY()),PEEK(VALUE_READ)) * TYPE_CONSTANT)-(VALUE_READ*TYPE_CONSTANT) AS CONSUMODIA0,

          IF(OID_METER=PEEK(OID_METER) AND FLOOR(DATE_READ)=FLOOR(TODAY()-1),PEEK(VALUE_READ)) * TYPE_CONSTANT)-(VALUE_READ*TYPE_CONSTANT) AS CONSUMODIA1,

          IF(OID_METER=PEEK(OID_METER) AND FLOOR(DATE_READ)=FLOOR(TODAY()-2),PEEK(VALUE_READ)) * TYPE_CONSTANT)-(VALUE_READ*TYPE_CONSTANT) AS CONSUMODIA2

        RESIDENT TMP2

          ORDER BY OID_METER, DATE_READ DESC;

       

       

      I got this message:

       

      Ocorreram os seguintes erros:

      Syntax error, missing/misplaced FROM:

        • Re: Problem using "IF"
          Sarah Rosen

          I think that error means that if you are doing an order by, you have to load from a qvd or external source, not resident load?

            • Re: Problem using "IF"
              Eduardo DImperio

              No Sarah,  below i have the original code and it works fine. But im trying to improve.

              They use 3 times the same code and i want to do replace with a multiple IF

               

              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 AS DIA0,

                  VALUE_READ AS VALOR0,

                VALUE_READ*TYPE_CONSTANT AS LEITURA_INICIAL_DIA0,

                IF(OID_METER=PEEK(OID_METER),PEEK(DIA0)) AS DATA_HORA_LEITURA_FINAL_DIA0,

                  IF(OID_METER=PEEK(OID_METER),PEEK(VALOR0)) * TYPE_CONSTANT AS LEITURA_FINAL_DIA0,

                  (IF(OID_METER=PEEK(OID_METER),PEEK(VALOR0)) * TYPE_CONSTANT)-(VALUE_READ*TYPE_CONSTANT) AS CONSUMODIA0

                RESIDENT TMP2

                  where FLOOR(DATE_READ)=FLOOR(TODAY())

                  ORDER BY OID_METER, DATE_READ DESC;

            • Re: Problem using "IF"
              Sunny Talwar

              May be try this:

               

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