Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
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);
What is the exact error you are getting?
Here is the error I get...
Field not found - <number>
SQL SELECT
number,
date,
store
FROM
PLACE.PROD.INFO
WHERE
Year(date) = 2016
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);
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
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
Thank you that worked!
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);
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
Thanks Bill.