Qlik Community

Qlik Sense App Development

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

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
qlikmark1990
New 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 (3)
2 Replies

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

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

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

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