request by user variable of the list type in SQL query

[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

[Vadim]

Hello.
Yes, it supported.

[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

[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.

[smartleopard]

Hi,
Ok, I will look for your response.
Thanks

[Alex K.]

Hello,

OK.

[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)})

[smartleopard]

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?

[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 5907 times

datasource2.jpg (61.3 KiB) Viewed 5907 times

var1.jpg (61.22 KiB) Viewed 5907 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

[Vadim]

Hello.
We need time to prepare answer for you.