How to solve syntax error in SQL in datasource and query option?

[susilasivan92]

SELECT TA.entryregister_patient_name, doctor_name, TA.patient_sex, entryregister_patient_sex, TA.patient_age, entryregister_entry_date_display, entryregister_patient_age_year, entryregister_patient_age_month, testcategorycode, testmastercode, TA.label, Profile.code, observation, TA.volume_uom, TA.normal_range, TA.remarks, test_master_purpose_ref FROM at_test_master AS Profile RIGHT JOIN at_v_lab_result_withtestasso AS TA ON TA.testmasterasso_base_test_master_ref = Profile.test_master_ref LEFT JOIN at_entry_register AS ER ON ER.entry_number = TA.entryregister_parent_entry_number LEFT JOIN at_v_room_allocation AS RA ON RA.ra_entry_register_ref = ER.parent_ref WHERE testmaster_test_master_purpose_ref = 1 AND entryregister_entry_register_ref = $P{entry_register_ref} AND testcategorycode != 'OTHER ITEM' AND ( ($P{PARAM_FILTER_BY_TEST_CATEGORY} = 'Y' AND TA.test_category_ref = $P{PARAM_TEST_CATEGORY_REF}) OR ($P{PARAM_FILTER_BY_TEST_CATEGORY} = 'N' AND TRUE) ) AND ( ($P{PARAM_PrintCompletedOnly} = 'N' AND TRUE) OR ($P{PARAM_PrintCompletedOnly} = 'Y' AND labtest_status IN ('COMPLETED', 'Completed')) ) AND ( $P{LAB_TEST_REF_PARAM} = 0 OR labtest_parent_ref = $P{LAB_TEST_REF_PARAM} OR labtest_user_defined_n04 = $P{LAB_TEST_REF_PARAM} OR TA.test_master_ref = $P{TEST_MASTER_REF_PARAM} ) AND labtest_status != 'Cancelled' AND ( $P{PatientSampleTestMasterRefs} = '0' OR TA.test_master_ref IN ($P!{PatientSampleTestMasterRefs}) )

 

this is the error i got

error: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: LabTestReport. at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:367) at com.jaspersoft.studio.data.jdbc.JDBCFieldsProvider.getFields(JDBCFieldsProvider.java:79) at com.jaspersoft.studio.data.jdbc.JDBCDataAdapterDescriptor.getFields(JDBCDataAdapterDescriptor.java:69) at com.jaspersoft.studio.property.dataset.dialog.DataQueryAdapters.doGetFields(DataQueryAdapters.java:525) at com.jaspersoft.studio.data.designer.AQueryDesignerContainer$1.run(AQueryDesignerContainer.java:48) at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:122) Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near ")" Position: 2027 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190) at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:134) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:317) i have this error but i have checked i can not found any syntax error

[anish.rai]

Thank you for posting to the Jaspersoft Community. Our team of experts has read your question and we are working to get you an answer as quickly as we can. If you have a Jaspersoft Professional Subscription plan, please visit https://support.tibco.com/s/ for direct access to our technical support teams offering guaranteed response times.

[jiman]

The result of the query depends on the output of the parameters passed. That is where this error seems to be thrown. What is the result of "TA.test_master_ref IN ($P!{PatientSampleTestMasterRefs}) )"? Jaspersoft uses output of $P!{} and appends the same to the query and that is where this error could be coming. 

1. If you want to use TA.test_master_ref IN with "IN" operator then rather than using  $P!{} you need to use $X{} as shown in examples below.

$X{IN, <column_name>, <parameter_name>} ==> $X{IN, TA.test_master_ref, PatientSampleTestMasterRefs}

Assuming here PatientSampleTestMasterRefs contains list of values i.e. 'val1', 'val2', 'val3'

Another example: select * from city where $X{IN, city_column, city_list_parameter}

Check below link for more examples of using $X{}.
https://jasperreports.sourceforge.net/sample.reference/query/

2. Also, if you can enable the debugging, you can verify the output for this query in the log file. And also you can paste the same to this question for us to understand how the end result looks and where is this error in the final query.

You can find similar examples with enabled debugging in the link provided above.