Using variable in SQL statement
Using variable in SQL statement
Hallo,
I try the test version before i purchase a license. Target is to realize one report to check out if all works as we need it. All looks very good but I have a problem to define the Where condition with an variable in the SQL statement. I didn't found an corresponding example in your sample files and in the online documentation I also didn't find and explaination. Potentially I'm wrong but I think there should be such a possibility. I want to define the value in the C# main program and transfer this value to the SQL statement. I used a syntax for the variable {variabl name}, but the SQL assistant reports an syntax error. Can you show me an small example how to handle such an problem.
Thanks in advance for your support.
I try the test version before i purchase a license. Target is to realize one report to check out if all works as we need it. All looks very good but I have a problem to define the Where condition with an variable in the SQL statement. I didn't found an corresponding example in your sample files and in the online documentation I also didn't find and explaination. Potentially I'm wrong but I think there should be such a possibility. I want to define the value in the C# main program and transfer this value to the SQL statement. I used a syntax for the variable {variabl name}, but the SQL assistant reports an syntax error. Can you show me an small example how to handle such an problem.
Thanks in advance for your support.
-
- Posts: 6199
- Joined: Tue Mar 20, 2018 5:34 am
Re: Using variable in SQL statement
Hello,
You can use Parameters for the data source, in the query. Also, you can use variables directly in the query:
select * from Table where column = {Variable}
Thank you.
You can use Parameters for the data source, in the query. Also, you can use variables directly in the query:
select * from Table where column = {Variable}
Thank you.
Re: Using variable in SQL statement
Hello, I am trying to do what this is suggesting but I cannot get it to work.
I wish to store in a variable a value from a data source.
I want to use this variable to select data from another data source.
{Variable} = Data Source 1 info.
Select * From DataSource2
Where Column = {Variable}.
When I try to retrieve the columns, I am prompted to input a value for "Variable" which seems to suggest I am actually not able to dynamically issue the WHERE clause.
So then I removed the WHERE statement, retrieved the columns from the table, and then added the WHERE statement back and saving the data source.
The problem I have then is I get an error trying to convert something to int.
Clearly I am doing something wrong. Could someone provide more detail how to query a data source with the value of a variable yet unknown?
I wish to store in a variable a value from a data source.
I want to use this variable to select data from another data source.
{Variable} = Data Source 1 info.
Select * From DataSource2
Where Column = {Variable}.
When I try to retrieve the columns, I am prompted to input a value for "Variable" which seems to suggest I am actually not able to dynamically issue the WHERE clause.
So then I removed the WHERE statement, retrieved the columns from the table, and then added the WHERE statement back and saving the data source.
The problem I have then is I get an error trying to convert something to int.
Clearly I am doing something wrong. Could someone provide more detail how to query a data source with the value of a variable yet unknown?
-
- Posts: 6199
- Joined: Tue Mar 20, 2018 5:34 am
Re: Using variable in SQL statement
Hello,
You should set the ReadOnly property for the variable which store value from the data source.
Thank you.
You should set the ReadOnly property for the variable which store value from the data source.
Thank you.
Re: Using variable in SQL statement
I have tried to set for ReadOnly, but I still get an error.
I do not know how the software queries the database. Does it only get queried when a column is used in the report?
I am thinking maybe the data source is getting queried trying to use a variable that has not be set with a value yet?
I do not know how the software queries the database. Does it only get queried when a column is used in the report?
I am thinking maybe the data source is getting queried trying to use a variable that has not be set with a value yet?
-
- Posts: 6199
- Joined: Tue Mar 20, 2018 5:34 am
Re: Using variable in SQL statement
Hello,
Please send us a sample report with test data which reproduces the issue for analysis.
Thank you.
Please send us a sample report with test data which reproduces the issue for analysis.
Thank you.
Re: Using variable in SQL statement
I dont think I am able to send a sample. I am using some embedded stimulsoft web based version and doesn't allow me save out the file.
Here is a screen shot of my Variable.
Here is my DataSource I wish to use to Pull only the data associated with what that variable holds. Its not complete because it always gives me an error. How should it be configured?
Here is a screen shot of my Variable.
Here is my DataSource I wish to use to Pull only the data associated with what that variable holds. Its not complete because it always gives me an error. How should it be configured?
- Attachments
-
- DataSource.JPG (55.19 KiB) Viewed 5104 times
-
- Variable.JPG (34.73 KiB) Viewed 5105 times
-
- Posts: 6199
- Joined: Tue Mar 20, 2018 5:34 am
Re: Using variable in SQL statement
Hello,
Please try to use the following query without parameters:
select * from ORDERS where OrderNo = {OrderNo}
Thank you.
Please try to use the following query without parameters:
select * from ORDERS where OrderNo = {OrderNo}
Thank you.
Re: Using variable in SQL statement
I get
"Incorrect Syntax Near '='. "
"Incorrect Syntax Near '='. "
-
- Posts: 6199
- Joined: Tue Mar 20, 2018 5:34 am
Re: Using variable in SQL statement
Hello,
Sorry, in your case, try to use:
select * from ORDERS where OrderNo = '{OrderNo}'
Thank you.
Sorry, in your case, try to use:
select * from ORDERS where OrderNo = '{OrderNo}'
Thank you.