Hey, I have a sql statement that runs fine in a DB and doesn't show any errors in the Dataset and Query Dialog but when I hit the "Read Fields" button it throws the error
"Invalid Column Index"
Any ideas?
Thanks.
6 Answers:
Posted on October 8, 2015 at 12:43pm
My mistake, I guess I tagged this wrong. I am currently using JasperSoft Studio Verison 6.0.1
When I view the error details I get this message
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:372) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:196) at com.jaspersoft.studio.data.jdbc.JDBCFieldsProvider.getFields(JDBCFieldsProvider.java:67) at com.jaspersoft.studio.data.jdbc.JDBCDataAdapterDescriptor.getFields(JDBCDataAdapterDescriptor.java:75) at com.jaspersoft.studio.property.dataset.dialog.DataQueryAdapters.doGetFields(DataQueryAdapters.java:359) at com.jaspersoft.studio.data.designer.AQueryDesignerContainer$1.run(AQueryDesignerContainer.java:50) at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:121) Caused by: java.sql.SQLException: Invalid column index at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:5386) at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:5374) at oracle.jdbc.driver.OraclePreparedStatementWrapper.setString(OraclePreparedStatementWrapper.java:282) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.setStatementParameter(JRJdbcQueryExecuter.java:564) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.setStatementParameter(JRJdbcQueryExecuter.java:399) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter$1.visit(JRJdbcQueryExecuter.java:332) at net.sf.jasperreports.engine.query.JRAbstractQueryExecuter$QueryParameter.accept(JRAbstractQueryExecuter.java:157) at net.sf.jasperreports.engine.query.JRAbstractQueryExecuter.visitQueryParameters(JRAbstractQueryExecuter.java:646) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:317)
Posted on October 9, 2015 at 7:53am
I am curently using the tibcosoft.jdbc.OrcaleDriver, I don't believe my parameters are incorrect, but I could be wrong. Heres my sql code
SELECT department.department_name, utl_user.last_name||', ' LASTNAME, utl_user.first_name FIRSTNAME, authorization.labour_skill_cd, authorization.skill_sdesc, license.LIC_CODE, license.LIC_SDESK, utl_role.role_cd, utl_role.role_nameFROM utl_role, utl_user_role, utl_user, (SELECT org_hr.user_id, org_work_dept.desc_sdesc department_name FROM org_hr, org_dept_hr, org_work_dept WHERE org_hr.hr_db_id = org_dept_hr.hr_db_id AND org_hr.hr_id = org_dept_hr.hr_id AND org_dept_hr.dept_db_id = org_work_dept.dept_db_id AND org_dept_hr.dept_id = org_work_dept.dept_id) department, (SELECT org_hr.user_id, ref_labour_skill.labour_skill_cd, ref_labour_skill.desc_sdesc skill_sdesc FROM org_hr, org_hr_qual, ref_labour_skill WHERE org_hr.hr_db_id = org_hr_qual.hr_db_id AND org_hr.hr_id = org_hr_qual.hr_id AND org_hr_qual.labour_skill_cd = ref_labour_skill.labour_skill_cd AND org_hr_qual.labour_skill_db_id = ref_labour_skill.labour_skill_db_id) authorization, (SELECT DISTINCT org_hr.user_id, lic_defn.lic_cd LIC_CODE, lic_defn.lic_sdesc LIC_SDESK FROM org_hr, org_hr_lic, lic_defn WHERE org_hr.hr_db_id = org_hr_lic.hr_db_id AND org_hr.hr_id = org_hr_lic.hr_id AND org_hr_lic.lic_db_id = lic_defn.lic_db_id AND org_hr_lic.lic_id = lic_defn.lic_id) license WHERE utl_role.role_id = utl_user_role.role_id ANDutl_user_role.user_id = utl_user.user_id ANDutl_user.user_id = department.user_id ANDutl_user.user_id = authorization.user_id (+) ANDutl_user.user_id = license.user_id (+) AND utl_role.role_cd IN (SELECT role_cd FROM utl_role WHERE role_name LIKE NVL($P{ROLE}, '%'))AND authorization.labour_skill_cd IN (SELECT labour_skill_cd FROM ref_labour_skill WHERE labour_skill_cd LIKE NVL($P{Skill}, '%'))ORDER BY department.department_name, LASTNAME, FIRSTNAME, authorization.labour_skill_cd, license.LIC_CODE, utl_role.role_cd
The section with the parameters is near the end,
AND utl_role.role_cd IN (SELECT role_cd FROM utl_role WHERE role_name LIKE NVL($P{Role}, '%'))
AND authorization.labour_skill_cd IN (SELECT labour_skill_cd FROM ref_labour_skill WHERE labour_skill_cd LIKE NVL($P{Skill}, '%'))
ORDER BY department.department_name, LASTNAME, FIRSTNAME, authorization.labour_skill_cd, license.LIC_CODE, utl_role.role_cd
