Skip to Main Content

Application Development Software

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!

selectManyChoice

user1254328Jul 17 2014 — edited Jul 21 2014

Hello,

I have  <af:selectManyChoice .........   in my code.

Problem : When I run the application and then click  selectManyChoice it will show me the options....  then i scroll down the page and notice that the selectManyChoice options keep floating and appear somewhere else on the page. Meaning they are not at the level where the selectManyChoice dropdown is...  its floating somewhere else.   How to fix this issue ?

Thx.

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 Aug 18 2014
Added on Jul 17 2014
7 comments
1,992 views