Field not found

[eduardo_dimperio]

Hi everyone,

Im need to select all oid_meter's that consume was not zero, for that i create a variable that receive the consume zero (Exclusao:)

and select oid_meter not select what's in Exclusao.

Exclusao:

Load

DISTINCT OID_METER AS EXCLUDE_OID_METER,

HORA

RESIDENT ANALISE_TMP

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

INNER JOIN(Exclusao)

ANALISE:

Load

  HORA,

    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,

   EXCLUDE_OID_METER

    RESIDENT ANALISE_TMP

    WHERE OID_METER <> EXCLUDE_OID_METER

    ORDER BY OID_METER;

but i receive this error:

Ocorreram os seguintes erros:

Field not found - <EXCLUDE_OID_METER>

I think the EXCLUDE_OID_METER is already created in exclusao, but maybe the inner join have something to do with.

[sunny_talwar]

Does your ANALISE_TMP table include both EXCLUDE_OID_METER and OID_METER? or one of the two?

[eduardo_dimperio]

Hey sunny,

Just one

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

  RESIDENT TMP2

    ORDER BY OID_METER, DATE_READ DESC;

DROP TABLE TMP,TMP2;

Exclusao:

Load

DISTINCT OID_METER AS EXCLUDE_OID_METER,

HORA

RESIDENT ANALISE_TMP

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

INNER JOIN(Exclusao)

ANALISE:

Load

  HORA,

    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,

    EXCLUDE_OID_METER

    RESIDENT ANALISE_TMP

    WHERE OID_METER <> EXCLUDE_OID_METER

    ORDER BY OID_METER;

[sunny_talwar]

So, if you don't have EXCLUDE_OID_METER in your table, how can you refer it in the resident load.

ANALISE:

Load

  HORA,

    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,

  EXCLUDE_OID_METER

  OID_METER,

    RESIDENT ANALISE_TMP

    WHERE OID_METER <> EXCLUDE_OID_METER

    ORDER BY OID_METER;

Not sure I understand what you are trying to do here ->   WHERE OID_METER <> EXCLUDE_OID_METER

[sunny_talwar]

Why don't you just do this?

ANALISE_TMP:

LOAD *

Where Not(CONSUMODIA0=0 OR CONSUMODIA1=0 OR CONSUMODIA2=0);

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

  RESIDENT TMP2

    ORDER BY OID_METER, DATE_READ DESC;

[eduardo_dimperio]

Because if i do Where Not(CONSUMODIA0=0 OR CONSUMODIA1=0 OR CONSUMODIA2=0); I'll eliminate only the hour that measure had happen, but not the oid_meter responsible for that.

The logic is, if my oid_meter find some value = 0 in any hour of the day we discard him.

Like if 19:00 of (today -1)  the oid_meter 1234 got a zero, we dont want to see any other measure of that meter in today-2, today-1 or today.

got it?

[sunny_talwar]

How about this?

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

  RESIDENT TMP2

    ORDER BY OID_METER, DATE_READ DESC;

DROP TABLE TMP,TMP2;

Right Join (ANALISE_TMP)

LOAD DISTINCT OID_METER

RESIDENT ANALISE_TMP

WHERE NOT(CONSUMODIA0=0 OR CONSUMODIA1=0 OR CONSUMODIA2=0);

Not sure if you want to remove just the particular HORA also, but assuming you want to completely get rid of all OID_METER if the condition is met once, then try the above

[eduardo_dimperio]

Hum, i thought that once i have a join i can use the field of that join.

The problem persist with the modification

How can i use the values of EXCLUDE_OID_METER to exclude in OID_METER?

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

  RESIDENT TMP2

    ORDER BY OID_METER, DATE_READ DESC;

DROP TABLE TMP,TMP2;

Exclusao:

Load

DISTINCT OID_METER AS EXCLUDE_OID_METER,

HORA

RESIDENT ANALISE_TMP

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

INNER JOIN(Exclusao)

ANALISE:

Load

  HORA,

    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

    RESIDENT ANALISE_TMP

    WHERE

    OID_METER <> EXCLUDE_OID_METER

    ORDER BY OID_METER, HORA DESC;

[eduardo_dimperio]

Hey Sunny,

Dont work, i did a simple test, print some oid_meters, oid_meter_exclude and oid_meter after right join.

I took oid_meters 87065 and 87066 present in Exclude_Oid_meter, but they still remain after right join.

[sunny_talwar]

How are you still getting EXCLUDE_OID_METER? I changed the right join table to this:

Right Join (ANALISE_TMP)

LOAD DISTINCT OID_METER

RESIDENT ANALISE_TMP

WHERE NOT (CONSUMODIA0=0 OR CONSUMODIA1=0 OR CONSUMODIA2=0);

No more EXCLUDE_OID_METER