Skip to Main Content

Integration

Announcement

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!

Does MRCA 10.1.2.0.3 support Oracle DB 10.2.0.1?

acayci-OracleMay 8 2006 — edited May 11 2006
Does OracleAS Metadata Repository Creation Assistant 10.1.2.0.3 support Oracle DB 10.2.0.1? Documentation says it supports 10.1.0.4 or later. Is there a patch for it?

Thank you,

MC

Comments

odie_63

I have to display output which is grouped based on month and its corresponding array values as json_ojbect.
Then you have to actually aggregate JSON objects per month in a JSON array : use aggregate function JSON_ARRAYAGG.
Final step is to consolidate all months in a single object, that's an aggregation too : use JSON_OBJECTAGG.
For example (just showing the last query step) :

SELECT json_objectagg(
     cal_mon
     value json_arrayagg(
         json_object(
          'date' value to_char(cal_dates,'DD-MON-YYYY')
         , 'val' value nvl(appointment_id,0)
         )
         order by cal_dates -- if necessary
        ) 
    ) Final_output
FROM get_json
GROUP BY cal_mon

You may also need to add some RETURNING CLOB clause here and there if the output is too large.

Paulzip

Maybe something like this (might need tweaking).


with
  cal_data as (
     select     first_date + level - 1   as cal_dates
     from       (select next_day(trunc(sysdate, 'MONTH') - 15, 'SUNDAY')  as first_date
                      , next_day(last_day(sysdate) - 1, 'SATURDAY')       as last_date
                 from   dual)
     connect by level <= last_date + 1 - first_date
  )
, data as (
    select c.*, a.*, to_number(to_char(cal_dates, 'dd')) day_of_week, to_char(cal_dates, 'fmMonth') month_name, to_number(to_char(cal_dates, 'MM')) month_num
    from   cal_data  c
    left join patient_appointments a on a.appointment_date >= c.cal_dates and a.appointment_date < c.cal_dates + 1
  ) 
select json_objectagg(
         month_name
       , json_arrayagg(
           json_object (
             'date' value day_of_week
           , 'val'  value nvl(appointment_id, 0)
           )
           order by day_of_week 
         )
       ) 
from data
group by month_num, month_name 
order by month_num
;

{
  "May" : [
    {
      "date" : 22,
      "val" : 0
    },
    {
      "date" : 23,
      "val" : 0
    },
    {
      "date" : 24,
      "val" : 0
    },
    {
      "date" : 25,
      "val" : 0
    },
    {
      "date" : 26,
      "val" : 0
    },
    {
      "date" : 27,
      "val" : 0
    },
    {
      "date" : 28,
      "val" : 0
    },
    {
      "date" : 29,
      "val" : 0
    },
    {
      "date" : 30,
      "val" : 0
    },
    {
      "date" : 31,
      "val" : 0
    }
  ],
  "June" : [
    {
      "date" : 1,
      "val" : 0
    },
    {
      "date" : 2,
      "val" : 0
    },
    {
      "date" : 3,
      "val" : 0
    },
    {
      "date" : 4,
      "val" : 0
    },
    {
      "date" : 5,
      "val" : 0
    },
    {
      "date" : 6,
      "val" : 0
    },
    {
      "date" : 7,
      "val" : 1
    },
    {
      "date" : 8,
      "val" : 0
    },
    {
      "date" : 9,
      "val" : 2
    },
    {
      "date" : 10,
      "val" : 3
    },
    {
      "date" : 11,
      "val" : 0
    },
    {
      "date" : 12,
      "val" : 0
    },
    {
      "date" : 13,
      "val" : 0
    },
    {
      "date" : 14,
      "val" : 0
    },
    {
      "date" : 15,
      "val" : 0
    },
    {
      "date" : 16,
      "val" : 0
    },
    {
      "date" : 17,
      "val" : 0
    },
    {
      "date" : 18,
      "val" : 0
    },
    {
      "date" : 19,
      "val" : 0
    },
    {
      "date" : 20,
      "val" : 0
    },
    {
      "date" : 21,
      "val" : 0
    },
    {
      "date" : 22,
      "val" : 0
    },
    {
      "date" : 23,
      "val" : 0
    },
    {
      "date" : 24,
      "val" : 0
    },
    {
      "date" : 25,
      "val" : 0
    },
    {
      "date" : 26,
      "val" : 0
    },
    {
      "date" : 27,
      "val" : 0
    },
    {
      "date" : 28,
      "val" : 0
    },
    {
      "date" : 29,
      "val" : 0
    },
    {
      "date" : 30,
      "val" : 0
    }
  ],
  "July" : [
    {
      "date" : 1,
      "val" : 0
    },
    {
      "date" : 2,
      "val" : 0
    }
  ]
}

 
Solomon Yakobson
Answer
WITH CAL_DATA AS (
                  SELECT  FIRST_DATE + LEVEL -1 AS CAL_DATES
                    FROM  (
                           SELECT  NEXT_DAY(TRUNC(SYSDATE,'MONTH') - 15 ,'SUNDAY') AS FIRST_DATE,
                                   NEXT_DAY(LAST_DAY(SYSDATE) - 1,'SATURDAY') AS LAST_DATE
                              FROM DUAL
                          )
                    CONNECT BY LEVEL <= LAST_DATE + 1 - FIRST_DATE
                 ),
     GET_JSON AS (
                  SELECT  TO_CHAR(C.CAL_DATES,'MON') CAL_MON,
                          C.CAL_DATES,
                          A.*
                    FROM      CAL_DATA C
                          LEFT JOIN
                              PATIENT_APPOINTMENTS A
                            ON (TRUNC(C.CAL_DATES) = TRUNC(A.APPOINTMENT_DATE))
                 )
SELECT  JSON_SERIALIZE(
                       JSON_OBJECT(
                                   KEY CAL_MON
                                   VALUE JSON_ARRAYAGG(
                                                    JSON_OBJECT(
                                                                KEY 'date'
                                                                VALUE TO_CHAR(CAL_DATES,'DD-MON-YYYY'),
                                                                KEY 'val'
                                                                VALUE NVL(APPOINTMENT_ID,0)
                                                               )
                                                    ORDER BY CAL_DATES
                                                   )
                                  )
                       RETURNING CLOB
                       PRETTY
                      ) Final_output
  FROM  GET_JSON
  GROUP BY CAL_MON
  ORDER BY CAL_MON
/

FINAL_OUTPUT
------------------------------------------------------------------------------------------
{
  "JUL" :
  [
    {
      "date" : "01-JUL-2022",
      "val" : 0
    },
    {
      "date" : "02-JUL-2022",
      "val" : 0
    }
  ]
}
{
  "JUN" :
  [
    {
      "date" : "01-JUN-2022",
      "val" : 0
    },
    {
      "date" : "02-JUN-2022",
      "val" : 0
    },
    {
      "date" : "03-JUN-2022",
      "val" : 0
    },
    {
      "date" : "04-JUN-2022",
      "val" : 0
    },
    {
      "date" : "05-JUN-2022",
      "val" : 0
    },
    {
      "date" : "06-JUN-2022",
      "val" : 0
    },
    {
      "date" : "07-JUN-2022",
      "val" : 1
    },
    {
      "date" : "08-JUN-2022",
      "val" : 0
    },
    {
      "date" : "09-JUN-2022",
      "val" : 2
    },
    {
      "date" : "10-JUN-2022",
      "val" : 3
    },
    {
      "date" : "11-JUN-2022",
      "val" : 0
    },
    {
      "date" : "12-JUN-2022",
      "val" : 0
    },
    {
      "date" : "13-JUN-2022",
      "val" : 0
    },
    {
      "date" : "14-JUN-2022",
      "val" : 0
    },
    {
      "date" : "15-JUN-2022",
      "val" : 0
    },
    {
      "date" : "16-JUN-2022",
      "val" : 0
    },
    {
      "date" : "17-JUN-2022",
      "val" : 0
    },
    {
      "date" : "18-JUN-2022",
      "val" : 0
    },
    {
      "date" : "19-JUN-2022",
      "val" : 0
    },
    {
      "date" : "20-JUN-2022",
      "val" : 0
    },
    {
      "date" : "21-JUN-2022",
      "val" : 0
    },
    {
      "date" : "22-JUN-2022",
      "val" : 0
    },
    {
      "date" : "23-JUN-2022",
      "val" : 0
    },
    {
      "date" : "24-JUN-2022",
      "val" : 0
    },
    {
      "date" : "25-JUN-2022",
      "val" : 0
    },
    {
      "date" : "26-JUN-2022",
      "val" : 0
    },
    {
      "date" : "27-JUN-2022",
      "val" : 0
    },
    {
      "date" : "28-JUN-2022",
      "val" : 0
    },
    {
      "date" : "29-JUN-2022",
      "val" : 0
    },
    {
      "date" : "30-JUN-2022",
      "val" : 0
    }
  ]
}
{
  "MAY" :
  [
    {
      "date" : "22-MAY-2022",
      "val" : 0
    },
    {
      "date" : "23-MAY-2022",
      "val" : 0
    },
    {
      "date" : "24-MAY-2022",
      "val" : 0
    },
    {
      "date" : "25-MAY-2022",
      "val" : 0
    },
    {
      "date" : "26-MAY-2022",
      "val" : 0
    },
    {
      "date" : "27-MAY-2022",
      "val" : 0
    },
    {
      "date" : "28-MAY-2022",
      "val" : 0
    },
    {
      "date" : "29-MAY-2022",
      "val" : 0
    },
    {
      "date" : "30-MAY-2022",
      "val" : 0
    },
    {
      "date" : "31-MAY-2022",
      "val" : 0
    }
  ]
}

SQL>

SY.

Marked as Answer by user525840 · Jun 7 2022
user525840

Thank you everyone for quick turnaround.
Using the suggested query, I can see the expected output. I will now pass it to the Oracle JET web component to use this json data instead of static json file.
I will create a procedure returning clob output from this SQL and use that in array data provider in front end.
Thank you.

Solomon Yakobson

Keep in mind NEXT_DAY is NLS dependent so if your stored procedure will be executed by, say, client in Israel:

SQL> create or replace
  2    procedure p1
  3      is
  4      begin
  5          dbms_output.put_line(NEXT_DAY(TRUNC(SYSDATE,'MONTH') - 15 ,'SUNDAY'));
  6  end;
  7  /

Procedure created.

SQL> set serveroutput on
SQL> exec p1;
22-MAY-22

PL/SQL procedure successfully completed.

SQL> alter session set nls_date_language=hebrew;

Session altered.

SQL> exec p1;
BEGIN p1; END;

                                                                          *
ERROR at line 1:
ORA-01846: not a valid day of the week
ORA-06512: at "SY47755.P1", line 4
ORA-06512: at line 1

SQL>

You could use 'IW' format instead but it is simpler to use pre-defined date (any Sunday):

TRUNC(SYSDATE,'MONTH') - 15 + 1 + MOD(ABS(TRUNC(SYSDATE,'MONTH') - 15 - DATE '2022-05-29') - 1,7)

Similarly you can get next Saturday (or any day).
SY.

1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 8 2006
Added on May 8 2006
1 comment
342 views