Trying to build excel jxls template for my application to render and generate forms but ran into problems

Question

Hello everyone I have a jxls/sql related question and hopefully I can use some help from you.

I am trying to write sql in an excel template, run it in jdeveloper to launch the my application and once the user click a button the app generates a excel form based on the template I created.

I tried my code in oracle sql 10g and it works fine and gave me the resultes i wanted but once I put it into the excel template(I followed the required jxls documentation), the application wont be able to render the template and return a form to me.

Maybe I have errors in the sql code that should not be written like this?

I would qppreciate your help thank you in advance!

HERE IS THE SQL CODE IN EXCEL:

        <jx:forEach items="${rm.exec("WITH rvus AS (
SELECT period_last_day, cpts.cpt, fy.fiscal_quarter(period_last_day) AS quarter, work_rvu, units, charge_amount
FROM FIN.idxchg_credited_clp_current idx JOIN DOM.pc_cpts cpts on idx.cpt = cpts.cpt
WHERE 
period_last_day >= fy.fiscal_year_start('30-JUN-13')
AND period_last_day <= '30-JUN-13'
),totals as ( SELECT cpt, quarter, sum(work_rvu) as total_rvu, sum(units) as total_units, sum(charge_amount)
as total_charge
FROM rvus
GROUP BY quarter,cpt
)
SELECT cpt,
SUM(DECODE(QUARTER, 1,total_rvu))Q1TotalRvu,
SUM(DECODE(QUARTER, 1,total_units))Q1TotalUnits,
SUM(DECODE(QUARTER, 1,total_charge))Q1Totalcharge,
SUM(DECODE(QUARTER, 2,total_rvu))Q2TotalRvu,
SUM(DECODE(QUARTER, 2,total_units))Q2TotalUnits,
SUM(DECODE(QUARTER, 2,total_charge))Q2TotalCharge,
SUM(DECODE(QUARTER, 3,total_rvu))Q3TotalRvu,
SUM(DECODE(QUARTER, 3,total_units))Q3TotalUnits,
SUM(DECODE(QUARTER, 3,total_charge))Q3TotalCharge,
SUM(DECODE(QUARTER, 4,total_rvu))Q4TotalRvu,
SUM(DECODE(QUARTER, 4,total_units))Q4TotalUnits,
SUM(DECODE(QUARTER, 4,total_charge))Q4TotalCharge
FROM totals group by cpt}" var="totals">

</jx:forEach>

OH and the error message in jdeveloper just says it cant parse my query above. — Bray
It may have something to do with the fact the double-quotes are necessary to begin and end the attribute value. I don't know if JXLS allows you to escape the double-quote character so it can be included in an attribute value. — rgettman
@rgettman so I tried it wont work either.....would it be possible that the query parsed could not start with "with rvus as" but has to start with a select statement? Thanks. — Bray

brian brian · Accepted Answer · 2015-03-12T19:01:50

It looks like you need to close your rm.exec statement:

FROM totals group by cpt}" var="totals">

should be

FROM totals group by cpt")}" var="totals">

Trying to build excel jxls template for my application to render and generate forms but ran into problems

1 Answers