'Microsoft OLE DB Provider for SQL Server, ErrorMsg: Invalid column name' at given SQL variable

[qlikmark1990]

I have an SQL script that has to run in QlikSense to fetch the needed data for my application. First I defined some SQL variables to use in the script. When I run the script:

SET vgl = 'MATE1';
LET vglId = 1;
LET startTS = 'null';
LET endTS = 'null';

LIB CONNECT TO 'db02';

SQL

select a.id, a.patientNummer, a.achternaam, a.tussenvoegsel, a.voorletters, a.geboortedatum, a.geslacht, a.email, a.opmerkingx,
       a.geblokkeerd, a.startDatum, a.bsn, a.meettrajectId, a.afgebroken, a.typeTraject, a.trajectStatus, a.taal, a.moment, a.startDatumMoment, a.ingestuurd,
       cast(a.score as numeric(5,2)) as score, a.eenheid, a.opmerking, a.schaalid, a.schaalnaam, a.volgnrInSchaalInt, a.volgnrInLijst, a.vglnaam, a.momentNr
       from
(
select *, null as opmerkingx
       from rom.vwOutputForAnalyseRom
where vglnaam = $(vgl) and startDatumMoment between isnull($(startTS), '2000-01-01') and isnull($(endTS), '2040-01-01')) as a;

I receive the error:

De volgende fout is opgetreden:

Connector reply error: ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Invalid column name 'MATE1'.

When I change the line:

where vglnaam = $(vgl) and startDatumMoment between isnull($(startTS), '2000-01-01') and isnull($(endTS), '2040-01-01')

to

where vglnaam = 'MATE1' and startDatumMoment between isnull($(startTS), '2000-01-01') and isnull($(endTS), '2040-01-01')

I don't get an error anymore but by this way I don't use the variable $(vgl). How do I use the variable properly in this script?

[Anil_Babu_Samineni]

Whenever, We are working with SQL and Qlik. They will work there own way not altogether. So, You can't use SET and LET variables into SQL queries. Instead using Resident load will deserve.

[jonathandienst]

You need to include the quotes:

where vglnaam = '$(vgl)' and startDatumMoment between isnull($(startTS), ....