Using Global Variable in Select Statement
In my select statement, I am selecting from SQL View ZSQLSummary with a where clause –
WHERE [ZGLSummary].[YEAR1] = ‘GBL_Year’
If I substitute 2017 – either with single quotes or with no quote marks at all – the query runs fine.
If I run it with the where clause in place as shown, I get “Error 22018 Conversion failed when converting the varchar value ‘GBL_Year’ to data type smallint. Looks like it is trying to literally use ‘GBL_Year’ as the value. for the where clause.
How can I get it to recognize my variable value correctly? Somehow I am referencing my global variable incorrectly in my select statement. How do I reference it? Thanks!
I suspect that you issue might be the casing of your global variable name. Globals are “upper case” and not “mixed case” as you are using.
So it should be GBL_YEAR vs GBL_Year.
I modified my Where clause to be all caps for the global variable name.
WHERE [ZGLSummary].[YEAR1] = GBL_YEAR
It won’t validate, and I can’t preview it, but I can run it, and it does work. I think you were right – the all caps was the issue. Thank you for your help.
Every time you open a map the data source is validated. When this happens the query is ran and there is no default value for the global variable. You can fix this with these steps.
1. Open the Map in question and go to the Global Variables tab
2. Find the variable that you are using in the query and add a default variable.
3. In the future, every time you preview the map this value will be used, but it will be overwritten by your task at runtime.
If you would like to submit an answer or comment, please sign in to the eOne portal.