topic Re: Why do I get errors on this LOAD SELECT? in QlikView

Why do I get errors on this LOAD SELECT?

Anonymous — Thu, 11 Feb 2016 15:36:43 GMT

What am I doing wrong? I keep getting errors and I just don't know why. I have tried different things and nothing is working. I am sure it is something easy I am missing that I will feel silly for.

Count:

LOAD

number,

date,

store,

date & ' - ' & store as StoreDateKey

;

SQL SELECT

number,

date,

store

FROM PLACE.PROD.INFO

WHERE Year(date) = $(vCurrentYear);

Re: Why do I get errors on this LOAD SELECT?

oknotsen — Thu, 11 Feb 2016 16:14:40 GMT

What is the exact error you are getting?

Re: Why do I get errors on this LOAD SELECT?

Anonymous — Thu, 11 Feb 2016 16:18:15 GMT

Here is the error I get...

Field not found - <number>

SQL SELECT

number,

date,

store

FROM

PLACE.PROD.INFO

WHERE

Year(date) = 2016

Re: Why do I get errors on this LOAD SELECT?

Anonymous — Thu, 11 Feb 2016 16:18:43 GMT

Many flavours of SQL convert the field names to upper case. Try something like this :

Count:

LOAD

NUMBER as number ,

DATE as date ,

STORE as store ,

date & ' - ' & STORE as StoreDateKey

;

SQL SELECT

NUMBER,

DATE,

STORE

FROM PLACE.PROD.INFO

WHERE Year(date) = $(vCurrentYear);

Re: Why do I get errors on this LOAD SELECT?

Peter_Cammaert — Thu, 11 Feb 2016 16:30:00 GMT

Check whether there really is a field called number in your database table. You can peek in your DBMS by pressing the Select... button in the script editor. You may have to provide the connection credentials again. After that, you'll be presented with a dialog that allows you to view table layouts. Check table PLACE.PROD.INFO.

Best,

Peter

Re: Why do I get errors on this LOAD SELECT?

Peter_Cammaert — Thu, 11 Feb 2016 16:32:01 GMT

And an additional check you can perform: the WHERE clause may be too restrictive in that it forces your DBMS to return nothing at all. Comment out the WHERE clause of the SELECT statement and check whether the error message goes away.

Best,

Peter

Re: Why do I get errors on this LOAD SELECT?

Anonymous — Thu, 11 Feb 2016 16:32:23 GMT

Thank you that worked!

Re: Why do I get errors on this LOAD SELECT?

Peter_Cammaert — Thu, 11 Feb 2016 16:35:10 GMT

Bill, that would mean that this one would work as well?

Count:

LOAD

NUMBER as number ,

DATE as date ,

STORE as store ,

date & ' - ' & STORE as StoreDateKey

;

SQL SELECT

number,

date,

store

FROM PLACE.PROD.INFO

WHERE Year(date) = $(vCurrentYear);

Re: Why do I get errors on this LOAD SELECT?

Anonymous — Thu, 11 Feb 2016 16:43:12 GMT

Yup that would work fine as well.

But if SQL is going to convert field names to upper case then I find it easier for people to understand if I script it as upper case.

Another option in the sql is to rename the field in double quotes and that will force it to whatever case you have in the quotes, in the script below that is lower case:

SQL SELECT

number as "number" ,

date as "date" ,

etc

Re: Why do I get errors on this LOAD SELECT?

Peter_Cammaert — Thu, 11 Feb 2016 16:44:43 GMT

Thanks Bill.

Re: Why do I get errors on this LOAD SELECT?

Anonymous — Thu, 11 Feb 2016 17:14:11 GMT

No worries, glad your are ok now.

It is a right pain when SQL changes the case like that, but gives no clue at all that it has.

Re: Why do I get errors on this LOAD SELECT?

Peter_Cammaert — Thu, 11 Feb 2016 17:17:10 GMT

No connect string option to block the DBMS from doing this?

Re: Why do I get errors on this LOAD SELECT?

Anonymous — Thu, 11 Feb 2016 17:18:35 GMT

I do not know, but there may well be.

Re: Why do I get errors on this LOAD SELECT?

Peter_Cammaert — Thu, 11 Feb 2016 17:46:19 GMT

I'm totally deluded, or the solution isn't that straightforward.

Apparently, the ANSI SQL standard says that all unquoted column & table names should be converted to upper case. And then ... SQL Server at least doesn't seem to give a ... about upper/lower case. It just returns the case a name was created in. Oracle on the other hand is very strict...

Ok, life is what it is...

Re: Why do I get errors on this LOAD SELECT?

Anonymous — Thu, 11 Feb 2016 18:10:43 GMT

I commonly use Oracle, which does adhere to standards very well.

Microsoft SQL and some other rdbms's are a bit more random as to what they do ..........................

Re: Why do I get errors on this LOAD SELECT?

Peter_Cammaert — Fri, 12 Feb 2016 09:24:48 GMT

As a final remark, I think this one is the least-effort-solution to make it work (copied code from OP):

Count:

LOAD

number,

date,

store,

date & ' - ' & store as StoreDateKey

;

SQL SELECT

"number",

"date",

"store"

FROM PLACE.PROD.INFO

WHERE Year(date) = $(vCurrentYear);

Put the SELECT field names between double quotes, and you'll get field names with that particular spelling. Works as an implied AS for every column.