PL/SQL: MODEL (example)

JasperReports has a specific feature: the detail band is strictly horizontal and spans the entire width of the page. If you need to print several small forms that could fit two per row, you would still have to print them one at a time due to this limitation.
Solution : Provide an input dataset that is already split into two columns.
A powerful PL/SQL construct that allows you to perform operations on a dataset with non-trivial logic. It is used very rarely, only in particularly complex or heavy cases.
select *
  from (select id1,
               name1,
               lead(id2, 1) over(order by null) id2,
               lead(name2, 1) over(order by null) name2
          from (select id1, name1, id2, name2
                  from (select 1 r, 10 id, 'a' name
                          from dual
                        union
                        select 2 r, 15 id, 'b' name
                          from dual
                        union
                        select 3 r, 20 id, 'c' name
                          from dual
                        union
                        select 4 r, 25 id, 'd' name
                          from dual
                        union
                        select 5 r, 30 id, 'e' name
                          from dual) model 
                          dimension by(r, id, name) 
                          measures(0 id1, 
                          cast(null as varchar2(255)) name1, 0 id2, 
                          cast(null as varchar2(255)) name2) 
                          rules(id1 [ mod(r, 2) != 0, id, name ] = cv(id), 
                          id2 [ mod(r, 2) = 0, id, name ] = cv(id), 
                          name1 [ mod(r, 2) != 0, id, name ] = cv(name), 
                          name2 [ mod(r, 2) = 0, id, name ] = cv(name))))
 where id1 != 0
In the example provided, we first split the dataset into columns based on the parity (even or odd) of the sequence number. Then, at a higher level, we use the LEAD function to concatenate rows in order to eliminate the alternating (chessboard-like) pattern in the data. Finally, by applying the condition `WHERE id1 != 0`, we filter out empty rows that appeared after the concatenation.
ceo@thomsonslegacy.com

We customize Oracle e-Business Suite R12 modules with Oracle Applications Framework and providing Oracle ADF based solutions


© Thomsonslegacy.com, 2025.
If you publish the materials of the site, the reference to the source is obligatory. The site uses cookies.