Nested Case statement in dataset and query

[leechiang.ng]

Hi,

I have a complex business logic which I try to apply and test the similar logic in foodmart as follow but I got an error. I appreciate the community could help:

 SELECT employee.employee_id, employee.full_name, employee.marital_status, employee.department_id, employee.position_title, employee.hire_date,(case when marriage_count<= 250 then (case         when (m_avg_salary < (select salary_paid from salary where department_id=15)) then 'Minimal'	when (m_avg_salary >= (select salary_paid from salary where department_id=15) 	and m_avg_salary < (select overtime_paid from salary where department_id=15)) then 'Partial'	when (m_avg_salary >= (select salary_paid from salary where department_id=11) 	and m_avg_salary < (select overtime_paid from salary where department_id=11)) then 'Basic'	when (m_avg_salary >= (select salary_paid from salary where department_id=5) 	and m_avg_salary < (select overtime_paid from salary where department_id=5)) then 'Medium'	when (m_avg_salary >= (select salary_paid from salary where department_id=1)) then 'High'       else  'Not Mapped'   end  )  when marriage_count> 250 then (case 	when (m_avg_salary < (select salary_paid from salary where department_id=15)) then 'Minimal'        when (m_avg_salary >= (select salary_paid from salary where department_id=15) 	and m_avg_salary < (select overtime_paid from salary where department_id=15)) then 'Partial'	when (m_avg_salary >= (select salary_paid from salary where department_id=11) 	and m_avg_salary < (select overtime_paid from salary where department_id=11)) then 'Basic'	when (m_avg_salary >= (select salary_paid from salary where department_id=5) 	and m_avg_salary < (select overtime_paid from salary where department_id=5)) then 'Medium'	when (m_avg_salary >= (select salary_paid from salary where department_id=1)) then 'High'          else  'Not Mapped'   end  ) else'Not Mapped'end) salary_status	FROM ( select employee.employee_id, employee.full_name, employee.marital_status, employee.department_id, employee.position_title, employee.hire_date, (select count(*) from employee) Total_count, (select count(*) from employee where employee.marital_status = 'M') marriage_count, (select avg(salary) from employee where employee.marital_status = 'M') m_avg_salaryfrom employee ) a[/code]

I have the syntax error in the query tab on the bracket '(' in front of case and after the end statement; I removed the brackets and the syntax went away. But when I went to 'data preview' tab to 'Refresh Preview Data', I got the following exception error:

"net.sf.jasperreports.engine.JRException: Error executing SQL statement for : data at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:240) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1112) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:689) at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1281) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:900) at net.sf.jasperreports.engine.fill.JRFiller.fill(JRFiller.java:152) at net.sf.jasperreports.engine.JasperFillManager.fill(JasperFillManager.java:464) at com.jaspersoft.studio.data.reader.DatasetReader.start(DatasetReader.java:184) at com.jaspersoft.studio.property.dataset.dialog.DataPreviewTable$4.run(DataPreviewTable.java:250) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:54) Caused by: org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table "employee" Position: 8 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:233) ... 9 more"

Thanks

Li