[Report Builder] Error while executing the query

[UKB30237]

I am establishing connection of Report Builder with dataset using below expression, however getting syntax error while executing the same. I am not able to identify the error, hnce posting here to check if anyone can help

Error

ERROR [ ] ERROR: Syntax error: Exception parsing query near 'a' java.sql.SQLException: Syntax error: Exception parsing query near 'a'; Error while executing the query

Query

="Select a.* , coalesce(sub_region,0) sub_region_flag from
(Select * from study where project_id in
(" & join(Parameters!Parameter1.Value,",") & ")
"&" OR
full_title like '%" & Parameters!Parameter2.Value & "%'
"&" OR
abbreviated_title like '%" & Parameters!Parameter2.Value & "%'
"&" OR
(Select distinct clinical_study_master_id from study_country where sub_region in
(" & join(Parameters!Parameter3.Value,",") & ")))a
Left join
(
Select clinical_study_master_id,count(distinct clinical_study_master_id) as sub_region_flag
from study_country
where sub_region in (" & join(Parameters!Parameter3.Value,",") & ")
group by clinical_study_master_id
) b
on a.clinical_study_master_id = b.clinical_study_master_id"

[v-lili6-msft]

hi

Try to remove "&"  in the conditional statement, 

and also if (Select distinct clinical_study_master_id from study_country where sub_region in
(" & join(Parameters!Parameter3.Value,",") & ")) should be adjust?

try to use this query code with specific value in conditional statement to have a test in your datasource.

Regards,

Lin

[UKB30237]

Thanks @v-lili6-msft 

I also updated som part of query to test things. When i am executing the query using hard coded variables it's working finr and when i am using parameters getting error:

Parametrized:

="
Select a.* , coalesce(sub_region_flag,0) sub_region_flag from
(Select * from study where project_id in
(" & join(Parameters!Parameter1.Value,",") & ")
"&" OR
full_title like '%" & Parameters!Parameter2.Value & "%'
"&" OR
abbreviated_title like '%" & Parameters!Parameter2.Value & "%'
"&" OR
clinical_study_master_id in
(Select distinct clinical_study_master_id from study_country where sub_region in
(" & join(Parameters!Parameter3.Value,",") & "))) a Left join
(
Select clinical_study_master_id, count(distinct clinical_study_master_id) as sub_region_flag
from study_country
where sub_region in (" & join(Parameters!Parameter3.Value,",") & ")
group by clinical_study_master_id
) b on a.clinical_study_master_id = b.clinical_study_master_id
"

Hard Coded:

Select a.* , coalesce(sub_region_flag,0) sub_region_flag from
(Select * from study where project_id in
(926,927)
OR
full_title like '%cancer%'
OR
abbreviated_title like '%cancer%'
OR
clinical_study_master_id in
(Select distinct clinical_study_master_id from study_country where sub_region in
('CIS')))a Left join
(
Select clinical_study_master_id,count(distinct clinical_study_master_id) as sub_region_flag
from study_country
where sub_region in ('CIS')
group by clinical_study_master_id
) b on a.clinical_study_master_id = b.clinical_study_master_id