The query string returned from from oxalis-sql/src/main/java/eu/peppol/persistence/sql/RawStatisticsRepositoryOracleImpl.java / getRawStatisticsSqlQueryText() does not work - at least not on Oracle 10 or 11 (12 not tested). The use of numbered columns is not supported in ORDER BY clauses. The same query with named columns DOES work - for instance:
SELECT
ap,
'OUT' direction,
TO_CHAR(tstamp,'YYYY-MM') period,
sender ppid,
doctype,
profile,
channel,
COUNT() count
FROM
raw_stats
WHERE
direction = 'OUT'
and tstamp between sysdate-10 and sysdate
GROUP BY ap, TO_CHAR(tstamp,'YYYY-MM') , sender , doc_type, profile, channel
union
SELECT
ap,
'IN' direction,
TO_CHAR(tstamp,'YYYY-MM') period,
receiver ppid,
doctype,
profile,
channel,
COUNT() count
FROM
raw_stats
WHERE
direction = 'IN'
and tstamp between sysdate-10 and sysdate
GROUP BY ap, TO_CHAR(tstamp,'YYYY-MM') , receiver , doc_type, profile, channel
order by period, ap
The query string returned from from oxalis-sql/src/main/java/eu/peppol/persistence/sql/RawStatisticsRepositoryOracleImpl.java / getRawStatisticsSqlQueryText() does not work - at least not on Oracle 10 or 11 (12 not tested). The use of numbered columns is not supported in ORDER BY clauses. The same query with named columns DOES work - for instance:
SELECT ap, 'OUT' direction, TO_CHAR(tstamp,'YYYY-MM') period, sender ppid, doctype, profile, channel, COUNT() count FROM raw_stats WHERE direction = 'OUT' and tstamp between sysdate-10 and sysdate GROUP BY ap, TO_CHAR(tstamp,'YYYY-MM') , sender , doc_type, profile, channel union SELECT ap, 'IN' direction, TO_CHAR(tstamp,'YYYY-MM') period, receiver ppid, doctype, profile, channel, COUNT() count FROM raw_stats WHERE direction = 'IN' and tstamp between sysdate-10 and sysdate GROUP BY ap, TO_CHAR(tstamp,'YYYY-MM') , receiver , doc_type, profile, channel order by period, ap