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
datasource1.jpg (77.21 KiB) Viewed 7972 times
datasource2.jpg
datasource2.jpg (61.3 KiB) Viewed 7972 times
var1.jpg
var1.jpg (61.22 KiB) Viewed 7972 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.