Page 1 of 1
Variable as part of SQL ‘IN’ expression.
Posted: Thu Dec 04, 2014 9:56 am
by error11
OK I confess - Im stumped! Ive been trying to use a string variable in my query. It looks good when I output the string to the report in a text box but when I try to use it as part of the query I get errors.
The combo-box ‘year’ property ‘Items’ contain the following:
3
4
3-4
On the user input form the user selects a year number (as string) from a combo-box. The three choices are ‘3’, ‘4’ or ‘3-4’. The OK button does this:
Code: Select all
vYear = (string)Switch(
year.Text == "3", "'3'",
year.Text == "4", "'4'",
year.Text == "3-4", "'3', '4'");
This works as confirmed if I output to the report page in a text box etc.
I see either
‘3’
‘4’
or
‘3’, ‘4’
Depending on what was selected.
The problems appear when I try to use the string in the query like this:
Code: Select all
AND
practitionerYear IN ('{vYear}')
When I run this I get to the combo-box selection but then get an error after I click ‘OK’. E.g. If I select ‘4’ the error message is "Incorrect syntax near '4'." If I select ‘3-4’ the error is "Incorrect syntax near '3'."
Needless to say the rest of the query runs fine if I remove this expression. Interestingly the query will run if I am not using the ‘IN’ expression. For instance if I use ‘=’.. but this prevents me from having 3 and 4 together as criteria in the expression.
Im using 2010.2.751 .NET which is set to use C#. Data source is MSSQL server.
Would appreciate any leads or clues. Thanks!
regds myles
Re: Variable as part of SQL ‘IN’ expression.
Posted: Fri Dec 05, 2014 9:05 am
by Alex K.
Hello,
Can you please send us your report with test data for analysis.
Thank you.
Re: Variable as part of SQL ‘IN’ expression.
Posted: Wed Dec 10, 2014 11:48 am
by error11
Hi Aleksey,
Ive recreated a simple query in SQLExpress which demonstrats the problem.
The Query:
Code: Select all
select * from Aleksey
where country IN ({vDomain})
The OK button code:
Code: Select all
vDomain = (string)Switch(
domain.Text == "Ukraine", "'Ukraine'",
domain.Text == "China", "'China'",
domain.Text == "Ukraine-China", "'Ukraine', 'China'");
I attach data CSV 'Aleksey.csv' (import to database 'testdata' - table 'Aleksey') and run attached mrt 'Report
Hope this includes all you need to reproduce the problem. This is a completely new report on a new server (SQLExpress) and the problem remains the same.
Ive kept the query to an absolute minimum for clarity. Let me know if this is enough for you to reproduce the problem.
best regds
myles
Re: Variable as part of SQL ‘IN’ expression.
Posted: Thu Dec 11, 2014 8:06 am
by HighAley
Hello.
In your first message the part of SQL query was wrong. You used extra ' symbols.
The query should be next:
because if you use your query you will get next queries
or
Code: Select all
AND
practitionerYear IN (''3', '4'')
Of cause you will get error.
As a way you could use List variable with ToQueryString(quotationMark: String = null, dateTimeFormat: String = null) method.
You could read more information in the
Dynamic SQL queries article on our Blog.
Thank you.
Re: Variable as part of SQL ‘IN’ expression.
Posted: Thu Dec 11, 2014 11:42 am
by error11
Hi Aleksey,
Thanks for looking at this. Im not absolutely clear about the solution but this is what I gather from your post ... please correct me if I am wrong.
The string is treated differently depending on where it is used. For instance when I use the string variable {vYear} on the header it displays correctly in output, but when I try to use the same variable {vYear} in a query then extra symbols are added which cause the query to fail due to a syntax error.
Where it gets confusing is your proposal to use a 'List Variable', do you mean I should try using a 'List box' rather than a 'Combo box'?
If only there was a simple way to 'inject' a string into the query. e.g. It would be great if I could do something like this:
Code: Select all
select x, y, z from whatevertable
where x IN ( {variable} )
The variable could be defined something like this:
After the variable is injected the query looks like this:
Code: Select all
select x, y, z from whatevertable
where x IN ( '1', '3', 'five' )
this has not worked for me when I try it in a query although it does display the variable correctly if used elsewhere such as in the report header.
So I am taking it that this method does not work and I should consider something along these lines:
Code: Select all
ToQueryString(quotationMark: String = null, dateTimeFormat: String = null)
Im not using type dateTime and maybe Im missing something but Its not immediately clear to me how to implement this alternative method? Is there a working example somewhere which I can have a look at?
thanks again,
myles
PS Currently I am considering using multiple 'OR' expressions as a workaround but would prefer to be able to use the 'IN' construct if possible.
Re: Variable as part of SQL ‘IN’ expression.
Posted: Thu Dec 11, 2014 12:13 pm
by HighAley
Hello.
Please, try to upgrade to our latest release.
There were a lot of improvements since version you use.
Thank you.
Re: Variable as part of SQL ‘IN’ expression.
Posted: Thu Dec 11, 2014 2:13 pm
by error11
Hi Aleksey,
Before I posted the problem to this forum I performed a test with the latest trial version of the Stimulsoft product. I can say with confidence that the latest version performs in the same way as the version I am already using.
If you can demonstrate this working then I will gladly consider your suggestion to purchase whatever version that is. Have you got an example that I can look at?
thanks in advance,
myles
Re: Variable as part of SQL ‘IN’ expression.
Posted: Thu Dec 11, 2014 3:53 pm
by error11
OK I have a workaround for anybody who is having this problem.
It looks as if Stimulsoft is adding a single-quote at the beginning and a single-quote at the end.
The fix is to neglect the first and last single-quote when setting the variable ..
In the query it seems you must have the single quotes or an error is produced:
Code: Select all
select x, y, z from whatevertable
where x IN ( ' {variable} ' )
The single quotes stay with the query as do the brackets so leave them out when setting the variable:
If you output the result to the page it would look like this:
looks a bit strange but in the query it works since the variable is injected between the existing single-quotes (which are themselves contained within the left and right brackets).
Hope this helps someone.
Re: Variable as part of SQL ‘IN’ expression.
Posted: Fri Dec 12, 2014 12:44 pm
by HighAley
Hello.
This problem occurs because all data is requested before showing the Form. You should use Request Parameters property as it's described in the
Dynamic SQL queries article on our Blog.
While the variable variable have no initial value, you get next query on preview
Code: Select all
select x, y, z from whatevertable
where x IN ()
This is wrong SQL query and you get the error. So your solution is one of several ways out.
The other way is set the isit value of variable to ' '
One another way is to set the Request Parameters property to True. Then the Form will be shown and only then the data will be requested.
Thank you.