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

[MK_QSL]

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?

[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;

[MK_QSL · Accepted solution]

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)

[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)=0,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);

[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 !