For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Hi,
Does someone know what property do I need to add to glassfish data source to avoid the exception of Could not commit with auto-commit set on while commiting a transaction?
Regards
with t as ( select date_start + offset dt from get_dates, lateral( select level - 1 offset from dual connect by level <= date_end - date_start + 1 ) ) select case grouping(to_char(dt,'mm')) when 1 then to_char(dt,'yyyy') || ' Total' end year, to_char(dt,'mm') month, sum(case dt - trunc(dt,'iw') when 5 then 1 end) saturday, sum(case dt - trunc(dt,'iw') when 6 then 1 end) sunday, sum(case dt - trunc(dt,'iw') when 0 then 1 end) monday, sum(case dt - trunc(dt,'iw') when 1 then 1 end) tuesday, sum(case dt - trunc(dt,'iw') when 2 then 1 end) wednesday, sum(case dt - trunc(dt,'iw') when 3 then 1 end) thursday, sum(case dt - trunc(dt,'iw') when 4 then 1 end) friday, count(*) grand_total from t group by grouping sets((to_char(dt,'yyyy')),(to_char(dt,'yyyy'),to_char(dt,'mm'))) order by to_char(dt,'yyyy'), to_char(dt,'mm') / YEAR MO SATURDAY SUNDAY MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY GRAND_TOTAL ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- 05 3 3 3 3 3 3 3 21 06 1 1 1 1 4 2014 Total 3 4 4 4 4 3 3 25 01 1 2 2 2 1 1 1 10 02 4 4 4 4 4 4 4 28 03 3 3 3 3 4 3 3 22 2017 Total 8 9 9 9 9 8 8 60 7 rows selected. SQL>
SY.
You posted the exact same problem yesterday .... https://community.oracle.com/tech/developers/discussion/4483855/need-day-wise-leave-statistic-report#latest
Yes Sir, you suggested me to post in this forum, ..
Yes Sir, you suggested me to post in this forum, .. Ah, so I did. Mea culpa.
Thanks for the reply. We are using 11g database. Attached is the query execution output. query_execution.jpg (67.13 KB)
Next time provide version upfront, expecially when you are on very old version:
with t as ( select date_start + level - 1 dt from get_dates connect by rowid = prior rowid and level <= date_end - date_start + 1 and prior sys_guid() is not null ) select case grouping(to_char(dt,'mm')) when 1 then to_char(dt,'yyyy') || ' Total' end year, to_char(dt,'mm') month, sum(case dt - trunc(dt,'iw') when 5 then 1 end) saturday, sum(case dt - trunc(dt,'iw') when 6 then 1 end) sunday, sum(case dt - trunc(dt,'iw') when 0 then 1 end) monday, sum(case dt - trunc(dt,'iw') when 1 then 1 end) tuesday, sum(case dt - trunc(dt,'iw') when 2 then 1 end) wednesday, sum(case dt - trunc(dt,'iw') when 3 then 1 end) thursday, sum(case dt - trunc(dt,'iw') when 4 then 1 end) friday, count(*) grand_total from t group by grouping sets((to_char(dt,'yyyy')),(to_char(dt,'yyyy'),to_char(dt,'mm'))) order by to_char(dt,'yyyy'), to_char(dt,'mm') / YEAR MONTH SATURDAY SUNDAY MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY GRAND_TOTAL ---------- ----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- 05 3 3 3 3 3 3 3 21 06 1 1 1 1 4 2014 Total 3 4 4 4 4 3 3 25 01 1 2 2 2 1 1 1 10 02 4 4 4 4 4 4 4 28 03 3 3 3 3 4 3 3 22 2017 Total 8 9 9 9 9 8 8 60 7 rows selected. SQL>
Many Thanks to you Mr. Solomon Yakobson Sir, Mr. EdSteven Sir, report working perfectly.