Using Global Variable in Select Statement
Sherry Whitten asked 6 years ago
I created a new global variable – GBL_Year. I wrote a task that runs before the map that asks the user to input the year and assigns the value to GBL_Year. I have a message box that correctly displays the year that the user entered as the value for GBL_Year, so I know my global variable value is getting set correctly.
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!
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!
Answers
Patrick Roth Staff answered 6 years ago
Since the field is numeric, you don’t need to use the single quotes. You just make SQL do a varchar->int conversion.
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.
patrick
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.
patrick
Sherry Whitten replied 6 years ago 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.
Daniel answered 3 years ago
When you use a global variable in the select statement that comes from a task, the map works but every time I open the map, it gives an error about the data source being invalid. How can this be stopped?
Ethan Sorenson Staff replied 3 years ago Daniel,
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.