{"id":472,"date":"2010-04-16T15:49:39","date_gmt":"2010-04-16T15:49:39","guid":{"rendered":"http:\/\/bar-solutions.com\/weblog\/?p=472"},"modified":"2010-04-16T15:49:39","modified_gmt":"2010-04-16T14:49:39","slug":"","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=472","title":{"rendered":"Report in APEX"},"content":{"rendered":"

The other day someone at a customer had a question about displaying a report. The different options are in a table so he could easily use a report to display the options. But he wanted to display three columns of options. That is, if there are 10 options, he would want to display 3 rows of 3 options and a single row of 1 option.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
option 1<\/td>\n<\/tr>\n
option 2<\/td>\n<\/tr>\n
option 3<\/td>\n<\/tr>\n
option 4<\/td>\n<\/tr>\n
option 5<\/td>\n<\/tr>\n
option 6<\/td>\n<\/tr>\n
option 7<\/td>\n<\/tr>\n
option 8<\/td>\n<\/tr>\n
option 9<\/td>\n<\/tr>\n
option 10<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

should be displayed as:<\/p>\n\n\n\n\n\n\n
option 1<\/td>\noption 2<\/td>\noption 3<\/td>\n<\/tr>\n
option 4<\/td>\noption 5<\/td>\noption 6<\/td>\n<\/tr>\n
option 7<\/td>\noption 8<\/td>\noption 9<\/td>\n<\/tr>\n
option 10<\/td>\n <\/td>\n <\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

How can this be done. The answer is in the query.<\/p>\n

<\/p>\n

I decided to try some stuff and came up with the solution. First I need about 10 records.
\n
SELECT rownum rn FROM user_source WHERE rownum < 11;<\/font><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
1<\/td>\n<\/tr>\n
2<\/td>\n<\/tr>\n
3<\/td>\n<\/tr>\n
4<\/td>\n<\/tr>\n
5<\/td>\n<\/tr>\n
6<\/td>\n<\/tr>\n
7<\/td>\n<\/tr>\n
8<\/td>\n<\/tr>\n
9<\/td>\n<\/tr>\n
10<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Using subquery factoring helps me build the query step by step. Next, I need to split the results into three groups. Using the MOD function I can tell in which group a record should reside.<\/p>\n

WITH t AS
\n
(SELECT rownum rn FROM user_source WHERE rownum < 11)
\n
select MOD(rn,3) rn2
\n
        ,rn from t;<\/font><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
1<\/td>\n1<\/td>\n<\/tr>\n
2<\/td>\n2<\/td>\n<\/tr>\n
0<\/td>\n3<\/td>\n<\/tr>\n
1<\/td>\n4<\/td>\n<\/tr>\n
2<\/td>\n5<\/td>\n<\/tr>\n
0<\/td>\n6<\/td>\n<\/tr>\n
1<\/td>\n7<\/td>\n<\/tr>\n
2<\/td>\n8<\/td>\n<\/tr>\n
0<\/td>\n9<\/td>\n<\/tr>\n
1<\/td>\n10<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Now I want to split the results into different sets. Let\u2019s begin by creating a set of the first records.<\/p>\n

WITH t AS
\n
(SELECT rownum rn FROM user_source WHERE rownum < 11),<\/p>\n

x AS<\/p>\n

(SELECT MOD(rn<\/p>\n

            ,3) rn2<\/p>\n

        ,rn<\/p>\n

    FROM t)<\/p>\n

SELECT rn     v1<\/p>\n

        ,rownum r1<\/p>\n

    FROM x<\/p>\n

   WHERE rn2 = 1<\/font>;<\/p>\n\n\n\n\n\n\n
1<\/td>\n1<\/td>\n<\/tr>\n
4<\/td>\n2<\/td>\n<\/tr>\n
7<\/td>\n3<\/td>\n<\/tr>\n
10<\/td>\n4<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

If I create three set of data (unique) then I can join them again<\/p>\n

WITH t AS
\n
(SELECT rownum rn FROM user_source WHERE rownum < 11),<\/p>\n

x AS<\/p>\n

(SELECT MOD(rn,3) rn2<\/p>\n

        ,rn<\/p>\n

    FROM t),<\/p>\n

all1 AS<\/p>\n

(SELECT rn     v1<\/p>\n

        ,rownum r1<\/p>\n

    FROM x<\/p>\n

   WHERE rn2 = 1),<\/p>\n

all2 AS<\/p>\n

(SELECT rn     v2<\/p>\n

        ,rownum r2<\/p>\n

    FROM x<\/p>\n

   WHERE rn2 = 2),<\/p>\n

all3 AS<\/p>\n

(SELECT rn     v3<\/p>\n

        ,rownum r3<\/p>\n

    FROM x<\/p>\n

   WHERE rn2 = 0)<\/p>\n

SELECT v1<\/p>\n

      ,v2<\/p>\n

      ,v3<\/p>\n

  FROM all1<\/p>\n

  JOIN all2 ON (r1 = r2)<\/p>\n

  JOIN all3 ON (r2 = r3);<\/font><\/p>\n\n\n\n\n\n
1<\/td>\n2<\/td>\n3<\/td>\n<\/tr>\n
4<\/td>\n5<\/td>\n6<\/td>\n<\/tr>\n
7<\/td>\n8<\/td>\n9<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

What happened to number 10? The records don\u2019t always exist in the second and third set, so I have to use an outer join to see all the records available.<\/p>\n

WITH t AS
\n
(SELECT rownum rn FROM user_source WHERE rownum < 11),<\/p>\n

x AS<\/p>\n

(SELECT MOD(rn,3) rn2<\/p>\n

        ,rn<\/p>\n

    FROM t),<\/p>\n

all1 AS<\/p>\n

(SELECT rn     v1<\/p>\n

        ,rownum r1<\/p>\n

    FROM x<\/p>\n

   WHERE rn2 = 1),<\/p>\n

all2 AS<\/p>\n

(SELECT rn     v2<\/p>\n

        ,rownum r2<\/p>\n

    FROM x<\/p>\n

   WHERE rn2 = 2),<\/p>\n

all3 AS<\/p>\n

(SELECT rn     v3<\/p>\n

        ,rownum r3<\/p>\n

    FROM x<\/p>\n

   WHERE rn2 = 0)<\/p>\n

SELECT v1<\/p>\n

      ,v2<\/p>\n

      ,v3<\/p>\n

  FROM all1<\/p>\n

  LEFT OUTER JOIN all2 ON (r1 = r2)<\/p>\n

  LEFT OUTER JOIN all3 ON (r2 = r3);<\/font><\/p>\n\n\n\n\n\n\n
1<\/td>\n2<\/td>\n3<\/td>\n<\/tr>\n
4<\/td>\n5<\/td>\n6<\/td>\n<\/tr>\n
7<\/td>\n8<\/td>\n9<\/td>\n<\/tr>\n
10<\/td>\n <\/td>\n <\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Now the result is exactly the way I (or actually the customer) wanted. All that needs to be done now is adapt this query so that it uses the real data. But the idea is in place.<\/p>\n","protected":false},"excerpt":{"rendered":"

The other day someone at a customer had a question about displaying a report. The different options are in a table so he could easily use a report to display the options. But he wanted to display three columns of options. That is, if there are 10 options, he would want to display 3 rows […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16,2,9],"tags":[],"class_list":["post-472","post","type-post","status-publish","format-standard","hentry","category-apex","category-oracle","category-sql"],"_links":{"self":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/472","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=472"}],"version-history":[{"count":0,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/472\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=472"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=472"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=472"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}