topic Re: Cannot use variable inside SQL in QlikView

Cannot use variable inside SQL

cristian_av — Wed, 25 Nov 2020 16:16:04 GMT

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())

Re: Cannot use variable inside SQL

eespiritu — Thu, 19 Jan 2017 17:34:05 GMT

Hi,

Try this:

Having ctmc_period >= '$(CargarDesde)';

Regards,

Re: Cannot use variable inside SQL

vishsaggi — Thu, 19 Jan 2017 17:36:34 GMT

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

;

Re: Cannot use variable inside SQL

maxgro — Thu, 19 Jan 2017 17:44:41 GMT

I think you need a LET

LET CargarDesde = year(today());

trace $(CargarDesde);

instead of SET

SET CargarDesde = year(today());

trace $(CargarDesde);

Re: Cannot use variable inside SQL

calvin_wee — Mon, 03 Apr 2017 01:04:26 GMT

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.