Join Crash QS

[eduardo_dimperio]

Hi everyone,

I need to get all OID_METERS that dont have value zero during the day, so i did 2 tables, one with all meters and other with meters the get zero in some hour the day.

But when i select excluding that meters with zeros, my QS crashs and i need to close everything anda restar. May someone help me?

//get the meters with zeros

Exclusao:

LOAD

HORA, //key

OID_METER AS EXCLUDE_OID_METER

RESIDENT ANALISE_TMP

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

Right Join(Exclusao)

//get all meters

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 //key

    RESIDENT ANALISE_TMP

    WHERE NOT EXISTS(EXCLUDE_OID_METER, OID_METER) // exclude zeros

    ORDER BY OID_METER, HORA DESC;

//crash and burn

[eduardo_dimperio]

I had an idea,

Can i save the result of EXCLUDE_OID_METER into some variable?

[jonathandienst]

Why not simply this:

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 CONSUMODIA0<>0 AND CONSUMODIA1<>0 AND CONSUMODIA2<>0;

DROP Table ANALISE_TMP;

There is no need to JOIN, and the ORDER BY will also increase the load time, and it serves no purpose here as far as I can see.

[eduardo_dimperio]

Because this will only show the consume that not zero. What i need is not show the meter if it have some zero along the day. But my true question is, why that code crash QS?

[eduardo_dimperio · Accepted solution]

Hey Guys,

Follow the final code:

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;

Thanks for help