Page 1 of 2
request by user variable of the list type in SQL query
Posted: Sun Jun 05, 2016 5:32 pm
by smartleopard
Hi,
Does Reports.Fx for Java 2016.1 support request by user variable of the list type in SQL query?
e.g:
Code: Select all
select * from table where name in '{var1}'
Thx
Re: request by user variable of the list type in SQL query
Posted: Mon Jun 06, 2016 6:09 am
by Vadim
Hello.
Yes, it supported.
Re: request by user variable of the list type in SQL query
Posted: Fri Jun 10, 2016 2:09 pm
by smartleopard
Hi,
the query in datasource is:
Code: Select all
select * from table where name in ('{var1}')
when i run the report in webviewer 2016.1 or FX 2016.1 preview and profile my data base a query as below is generated for the first time:
Code: Select all
select * from table where name in ('[b, d, g, f, e, c, a]')
when i change the value of request by user variable the query will be for example like this one:
Code: Select all
select * from table where name in ('[b, d]')
As you can see the generated query is not correct. is there any problem in my datasource query?
Thx
Re: request by user variable of the list type in SQL query
Posted: Mon Jun 13, 2016 6:00 am
by Alex K.
Hello,
We need some additional time for investigating the issue.
We will try to add the additional changes within two or three weeks.
We will let you know about the result.
Thank you.
Re: request by user variable of the list type in SQL query
Posted: Mon Jun 13, 2016 6:22 am
by smartleopard
Hi,
Ok, I will look for your response.
Thanks
Re: request by user variable of the list type in SQL query
Posted: Mon Jun 13, 2016 6:23 am
by Alex K.
Hello,
OK.
Re: request by user variable of the list type in SQL query
Posted: Tue Jun 28, 2016 7:54 am
by Vadim
Hello.
You can use next SQL request:
Code: Select all
select * from actor where actor_id in ({Substring(ToString(Variable1), 1, Length(ToString(Variable1)) - 2)})
Re: request by user variable of the list type in SQL query
Posted: Thu Jun 30, 2016 3:36 pm
by smartleopard
Vadim wrote:Hello.
You can use next SQL request:
Code: Select all
select * from actor where actor_id in ({Substring(ToString(Variable1), 1, Length(ToString(Variable1)) - 2)})
Hi
I must use 2016.1.16 for this change?
Would you please explain what does this code do?
Re: request by user variable of the list type in SQL query
Posted: Thu Jun 30, 2016 7:23 pm
by smartleopard
Hi,
I tested the code you suggested, it removes [ and ] but the query is still incorrect. I created two datasources, datasource1 for my main query and datasource2 for var1:
- datasource1.jpg (77.21 KiB) Viewed 7971 times
- datasource2.jpg (61.3 KiB) Viewed 7971 times
- var1.jpg (61.22 KiB) Viewed 7971 times
when i go to preview tab and select value for var1 a query like this is generated:
Code: Select all
select * from ServiceType where Title in ('''')
1- As you can see the selected value for var1 is not set.
2- I must set single quotation for
Code: Select all
{Substring(ToString(var1), 1, Length(ToString(var1)) - 2)}
if i don't an error happen
Thanks for your attention and reply
Re: request by user variable of the list type in SQL query
Posted: Fri Jul 01, 2016 12:31 pm
by Vadim
Hello.
We need time to prepare answer for you.