| Category: | Bug report |
Priority: | High |
Status: | Feedback Requested |
| Project: | Severity: | Major |
Resolution: | Open |
|
| Component: | Reproducibility: | Always |
Assigned to: |
Team,
We have a report requirement for which the designed SQL query is using "WITH" clause. The query runs just fine in SQL tools but the Jasper Studio SQL editor fails to read any fields and throws SQL Execution error. I ignored the error and created all the fields manually and tried previewing the report. But, still it throws SQL Execution error. Below is the query I am using.
WITH
TEMP_HIER (hierarchy_key, parent_hierarchy_key,hierarchy_cd, corporate, region)
AS (
SELECT h.hierarchy_key, NULL AS parent_hierarchy_key,h.hierarchy_cd, gh.corporate, gh.region
FROM acf_common_svcs.HIERARCHY h
LEFT OUTER JOIN acf_common_svcs.GMAS_HIERARCHY gh
ON h.hierarchy_key = gh.hierarchy_key
WHERE h.hierarchy_cd = '012'
UNION ALL
SELECT os.hierarchy_key, os.parent_hierarchy_key,h.hierarchy_cd, gh.corporate, gh.region
FROM acf_common_svcs.ORGANIZATIONAL_STRUCTURE os
INNER JOIN acf_common_svcs.HIERARCHY h
ON os.hierarchy_key = h.hierarchy_key
LEFT OUTER JOIN acf_common_svcs.GMAS_HIERARCHY gh
ON os.hierarchy_key = gh.hierarchy_key
INNER JOIN TEMP_HIER th
ON os.parent_hierarchy_key = th.hierarchy_key)
SELECT
b.batch_state_cd
,merchant
,e.deposit_effective_state_cd
FROM (
SELECT b.*, ad.source_id
FROM fsods_merchant.ACQ_DEPOSIT_BATCHES b
INNER JOIN fsods_merchant.ACQ_DEPOSIT ad
ON b.file_id = ad.file_id
UNION ALL
SELECT b.*, ad.source_id
FROM fsods_merchant.ACQ_WORK_DEPOSIT_BATCHES b
INNER JOIN fsods_merchant.ACQ_WORK_DEPOSIT ad
ON b.file_id = ad.file_id
) b
INNER JOIN (
SELECT file_id, file_state_cd
FROM fsods_merchant.ACQ_DEPOSIT
UNION ALL
SELECT file_id, file_state_cd
FROM fsods_merchant.ACQ_WORK_DEPOSIT
) d
ON b.file_id = d.file_id
LEFT OUTER JOIN fsods_merchant.DEPOSIT_EFFECTIVE_BATCH_STATE e
ON d.file_state_cd = e.file_state_cd
AND b.batch_state_cd = e.batch_state_cd
LEFT OUTER JOIN (
SELECT source_id
FROM fsods_merchant.ACQ_DEPOSIT_SOURCE_ID adsi
INNER JOIN (
SELECT DISTINCT hierarchy_cd FROM TEMP_HIER
) authorized_hier
ON adsi.hierarchy_cd = authorized_hier.hierarchy_cd
) adsi
ON b.source_id = adsi.source_id
WHERE ( adsi.source_id IS NOT NULL -- user has access to the source ID
) -- user has access to everything
AND ( b.deposit_date >= to_timestamp('2017-01-08', 'yyyy-MM-dd')
AND b.deposit_date <= to_timestamp('2017-03-31', 'yyyy-MM-dd') )
order by deposit_date;
8 Comments:
I have having the same problem. I write code in Toad for oracle 12. copy code that excutes into ireports6.0 and I get table or view does not exist.
SELECT
SV_PROTOCOL.PROTOCOL_NO AS PROTOCOL_NO,
SV_EPRMS_ALL_SUB.REVIEW_TYPE AS REVIEW_TYPE,
SV_EPRMS_ALL_SUB.REASON AS REASON,
SV_EPRMS_ALL_SUB.REVIEW_DATE AS REVIEW_DATE,
SV_PROTOCOL.TITLE AS SV_PROTOCOL_TITLE,
SV_PCL_SPONSOR.SPONSOR_NAME AS SPONSOR_NAME,
SV_PCL_MGMT_PROGRAMAREA.PROGRAM_AREA AS PROGRAM_AREA,
SV_EPRMS_ALL_SUB.DECISION AS ACTION,
PF_ORGANIZATION_CONTACT.LAST_NAME AS REVIEWER_LAST,
PF_ORGANIZATION_CONTACT.FIRST_NAME AS REVIEWER_FIRST,
SV_PROTOCOL.DEPARTMENT_NAME AS DEPARTMENT_NAME,
SV_PCL_SPONSOR.PRINCIPAL_SPONSOR AS SPONSOR_PRINCIPAL,
SV_PCL_STAFF_ROLE.STAFF_NAME AS PI_NAME
FROM
ONCORE.SV_EPRMS_ALL_SUB INNER JOIN ONCORE.SV_PROTOCOL ON SV_EPRMS_ALL_SUB.PROTOCOL_ID = SV_PROTOCOL.PROTOCOL_ID
INNER JOIN ONCORE.SV_PCL_SPONSOR ON SV_PROTOCOL.PROTOCOL_ID = SV_PCL_SPONSOR.PROTOCOL_ID
INNER JOIN ONCORE.SV_PCL_MGMT_PROGRAMAREA ON SV_PROTOCOL.PROTOCOL_ID = SV_PCL_MGMT_PROGRAMAREA.PROTOCOL_ID
INNER JOIN "ONCORE"."SV_PCL_STAFF_ROLE" SV_PCL_STAFF_ROLE ON SV_PROTOCOL."PROTOCOL_NO" = SV_PCL_STAFF_ROLE."PROTOCOL_NO"
INNER JOIN "ONCORE"."SMRS_RCM_PCL_REVIEW" SMRS_RCM_PCL_REVIEW ON SV_EPRMS_ALL_SUB."OUTCOME_ID" = SMRS_RCM_PCL_REVIEW."OUTCOME_ID"
INNER JOIN "ONCORE"."PF_ORGANIZATION_CONTACT" PF_ORGANIZATION_CONTACT ON SMRS_RCM_PCL_REVIEW."STAFF_ID" = PF_ORGANIZATION_CONTACT."CONTACT_ID"
I am trying to add program area to this report. and it wont work. any ideas on how to add program area from this view to the code above?
DROP VIEW ONCORE.SV_PCL_MGMT_PROGRAMAREA;
CREATE OR REPLACE FORCE VIEW ONCORE.SV_PCL_MGMT_PROGRAMAREA
(PROTOCOL_DETAIL_ID, PROGRAM_AREA, PLANNED, PRIMARY, PROTOCOL_ID,
PARENT_ID, CODE_ID, CODE)
BEQUEATH DEFINER
AS
select pd.pcl_detail_id protocol_detail_id,
sc.description AS program_area,
DECODE(scp.description,'Approved','N','Planned','Y') Planned,
pd.value primary,
pd.protocol_id,
sc.parent_id, sc.code_id, sc.code
from smrs_pcl_detail pd, pf_code sc, pf_code scp
where pd.detail = sc.code_id
and sc.category = 'PROGRAM_AREA'
and sc.parent_id = scp.code_id
WITH READ ONLY;
Hi,
I am having also difficulties using a "with-clause" in a report query (build with JasperSoft Studio Professional v621) - receiving the error:
net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query:
with a as(....),b as (..)select..
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:478)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:295)
at com.jaspersoft.studio.data.jdbc.JDBCFieldsProvider.getFields(JDBCFieldsProvider.java:79)
at com.jaspersoft.studio.data.jdbc.JDBCDataAdapterDescriptor.getFields(JDBCDataAdapterDescriptor.java:75)
at com.jaspersoft.studio.property.dataset.dialog.DataQueryAdapters.doGetFields(DataQueryAdapters.java:396)
at com.jaspersoft.studio.data.designer.AQueryDesignerContainer$1.run(AQueryDesignerContainer.java:53)
at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:119)
Caused by: java.sql.SQLException: [TibcoSoftwareInc][Oracle JDBC Driver]The specified SQL type is not supported by this driver.
at tibcosoftwareinc.jdbc.oraclebase.ddcr.b(Unknown Source)
at tibcosoftwareinc.jdbc.oraclebase.ddcr.a(Unknown Source)
at tibcosoftwareinc.jdbc.oraclebase.ddcq.b(Unknown Source)
at tibcosoftwareinc.jdbc.oraclebase.ddcq.a(Unknown Source)
at tibcosoftwareinc.jdbc.oraclebase.dddz.d(Unknown Source)
at tibcosoftwareinc.jdbc.oraclebase.dddz.a(Unknown Source)
at tibcosoftwareinc.jdbc.oraclebase.dddz.setNull(Unknown Source)
at tibcosoftware.jdbc.common.oracle.PreparedStatementWrapper.setNull(PreparedStatementWrapper.java:53)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.setStatementParameter(JRJdbcQueryExecuter.java:702)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.setStatementParameter(JRJdbcQueryExecuter.java:512)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter$1.visit(JRJdbcQueryExecuter.java:438)
at net.sf.jasperreports.engine.query.JRAbstractQueryExecuter$QueryParameter.accept(JRAbstractQueryExecuter.java:164)
at net.sf.jasperreports.engine.query.JRAbstractQueryExecuter.visitQueryParameters(JRAbstractQueryExecuter.java:666)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createStatement(JRJdbcQueryExecuter.java:423)
... 6 more
Please advise.
Kind regards
Sebastian
p.s. query works in sqldeveloper / dbeaver using the oracle jdbc-driver.
though using the oracle jdbc driver for the studio connection results in "Invalid column index" error.
What worked for me was to remove all comments within the query.
see also
https://community.jaspersoft.com/jasperreports-library/issues/6526
and
https://community.jaspersoft.com/questions/960561/invalid-column-index
HTH.
Hi,
This issue is not fixed yet for me.
I have downloaded today a fresh copy of jaspersoft studio community edition (6.5.1), and it keeps on throwing the sql syntax error whenever I try to fill my report. The query starts ´with a as (...), b as (...) select ...'. As reported by other users, the query works fine in sqldeveloper.
I know jasperserver has got a couple of properties which can be modified in order to avoid this in the server. Is there not anything similar we can apply to studio? I have tried setting the Validator.ValidSQL and security.validation.sql.on=false in the properties preference tab, to no avail.
It would be nice to accept these 'with' clauses as valid, or at least to be able to disable validation.
Thanks,
R
Awfully sorry, this is fixed. It turned out I had a silly extra character in my query (which I had tested successfuly before sending my comment, honest!).
R
Hi,
You may have troubles with your SQL queries beginning with the keyword 'WITH'. You need to modifiy the following file :
<JRS>\apache-tomcat\webapps\jasperserver\WEB-INF\classes\esapi\validation.properties
and the following property, line 113-114, adding 'WITH', example :
# starts with 'select', does not contain ';'
Validator.ValidSQL=(?is)^\\s*(select|call|WITH)\\s+[^;]+;?\\s*$
It worked for me. I don't remember if I had to restart the webapp...
I was trying to connect Jaspersoft 6.6.0 to Hive 2.3. I am able to connect but not able to run query. Getting error that "Method not supported"
Kindly help me to resolve this asap.