Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikmark1990
Contributor III
Contributor III

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

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?

 

Labels (1)
2 Replies
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.
Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
jonathandienst
Partner - Champion III
Partner - Champion III

You need to include the quotes:

where vglnaam = '$(vgl)' and startDatumMoment between isnull($(startTS), ....
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein