Hi,
I've created a report chart to show the number of transactions per hour period for a given (bind) client.
The SQL is good and sorts as expected if executed as a normal query whilst extracting Data from a 12c EE database.. however, whenever i attempt to sort the 'hour' periods on the x-axis of a SQL*Developer (v19.4.0) Report Chart I'm getting some bizarre results. I've tried to enforce the 'hour' column to be a number via TO_NUMBER and also to be a string by padding it out but to no avail 8am seems to be after 6pm but before 7pm.
I've attached the DDL, DML (Insert) and the Report's XML to this post along with a screenshot of the issue.
PS: Why are SQL and XML extensions not allowed as an attachment's extension?...I've had to rename the files to allow upload. Just remove the '.txt' extension if you want to replicate my issue
ddl .sql.txt (717 Bytes)dml.sql.txt (331.41 KB)report.xml.txt (6.71 KB)
The reports SQL is listed below also.
SELECT
rce.client_id
-- , to_char(rce.creation_date, 'fmD') day_of_wk
-- , to_char(rce.creation_date, 'fmDAY') day
, to_char(rce.creation_date, 'fmD-DAY') day -- Inc. day nbr of week, 'fm' to remove trailing space
-- , to_number(to_char(rce.creation_date, 'HH24')) hour /* 'To_NUMBER' to see if it aids sorting along the x-axis...chart module throwing a hissy fit and not ordering as expected. */
, rpad(lpad(to_char(rce.creation_date, 'fmHH24'), 2, 0), 4, '0') hour /* PADDING to see if it aids sorting along the x-axis...chart module throwing a hissy fit and not ordering as expected. */
, COUNT(*) nbr_orders
FROM
report_chart_example rce
WHERE
rce.client_id LIKE upper('%'
|| :client
|| '%')
AND rce.creation_date > trunc(sysdate) - 30
GROUP BY
rce.client_id
-- , to_char(rce.creation_date, 'fmD')
-- , to_char(rce.creation_date, 'fmDAY')
, to_char(rce.creation_date, 'fmD-DAY')
, to_char(rce.creation_date, 'fmHH24')
ORDER BY
rce.client_id ASC
-- , to_char(rce.creation_date, 'fmD')
-- , to_char(rce.creation_date, 'fmDAY')
, to_char(rce.creation_date, 'fmD-DAY') ASC
, to_char(rce.creation_date, 'fmHH24') ASC
If anyone knows how resolve this I'd be be most thankful...it's driving me batty!
Cheers,
Michael