Cannot use variable inside SQL

[cristian_av]

Hi

I want to load a SQL sentence dinamically limited by the last year (2016). But it seems to be that QliKView is not replacing the variable with the year. Any idea why?

This is the code:

SET CargarDesde = year(today());
SET RepositorioXLS='..\Datos Externos\XLS\Estado de Resultados\';

.....................

Kilos:
SQL Select
*
From dba.costo_tipomovtocantidades ct
LEFT JOIN dba.especies ON ct.espe_codigo=especies.espe_codigo
  LEFT JOIN dba.costo_gerencias ON ct.plde_codigo=costo_gerencias.plde_codigo
Group By ctmc_period, ctmc_nromes, ct.espe_codigo, espe_nombre, coge_codigo, coge_nombre, ctmv_codigo, ctmc_tipser
Having ctmc_period>=$(CargarDesde);

Thi is the error it gives to me:

ErrorSource: (null), ErrorMsg: (null)

Kilos:

SQL Select

*

From dba.costo_tipomovtocantidades ct

LEFT JOIN dba.especies ON ct.espe_codigo=especies.espe_codigo

  LEFT JOIN dba.costo_gerencias ON ct.plde_codigo=costo_gerencias.plde_codigo

Group By ctmc_period, ctmc_nromes, ct.espe_codigo, espe_nombre, coge_codigo, coge_nombre, ctmv_codigo, ctmc_tipser

Having ctmc_period>=year(today())

[eespiritu]

Hi,

Try this:

Having ctmc_period >= '$(CargarDesde)';

Regards,

[vishsaggi]

What is the value coming from ctmc_period ? Is that a date or a year value in that? Try some thing like this

Kilos: 

LOAD *

WHERE ctmc_period >= '$(cargarDesde)' ;

SQL Select 

* 

From dba.costo_tipomovtocantidades ct 

LEFT JOIN dba.especies ON ct.espe_codigo=especies.espe_codigo 

  LEFT JOIN dba.costo_gerencias ON ct.plde_codigo=costo_gerencias.plde_codigo 

Group By ctmc_period, ctmc_nromes, ct.espe_codigo, espe_nombre, coge_codigo, coge_nombre, ctmv_codigo, ctmc_tipser 

; 

[maxgro · Accepted solution]

I think you need a LET

LET CargarDesde = year(today());

trace $(CargarDesde);

instead of SET

SET CargarDesde = year(today());

trace $(CargarDesde);

[calvin_wee]

Seen several threads on this but this is the only one that works for me. Not sure what is the difference using LET or SET. Thanks Massimo Grossi.