SELECT t.min_date + numtodsinterval(LEVEL - 1,'hour') date_by_hourĬONNECT BY LEVEL <= ( t.max_date - t.min_date + 1) * 24)ĬOUNT(CASE WHEN h.hour = TRUNC(s.sampled_on,'hh24') THEN 1 END) sampled#ĬOUNT(CASE WHEN h.hour = TRUNC(s.received_on,'hh24') THEN 1 END) received#ĬOUNT(CASE WHEN h.hour = TRUNC(s.completed_on,'hh24') THEN 1 END) completed#ĬOUNT(CASE WHEN h.hour = TRUNC(s.authorized_on,'hh24') THEN 1 END) authorized# GREATEST(MAX(t.sampled_on), MAX(t.received_on), MAX(t.completed_on), MAX(t.authorized_on)) 'max_date' SELECT LEAST(MIN(t.sampled_on), MIN(t.received_on), MIN(t.completed_on), MIN(t.authorized_on)) 'min_date' LISTAGG(TO_CHAR(A),',')WITHINGROUP(ORDERBY1) SQL> select listagg(to_char(a),',') within group (order by 1) from test group by 1 IF this is not acceptible, you need to raise a ticket with Oracle support. You could cast to a char to get round this. SQL> select listagg(a, ',') within group (order by 1) from test group by 1 Are you using UTF-16 + NVARCHAR2 by any chance? eg this: SQL> select * from nls_database_parameters where parameter='NLS_NCHAR_CHARACTERSET'
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |