神刀安全网

SQL Analytics in every day APEX

Looking for way to apply analytical functions to your APEX applications?

I had a classic report where I wanted to dynamically source the column headers from counts in the database (values in brackets).

SQL Analytics in every day APEX

The ability to do this has been a feature of APEX for a while, but this was the first time I did it in APEX 5.0.

SQL Analytics in every day APEX
Customise headers via Region attributes

With Connor’s recent spate of analytics videos ,  I thought I’d mention this use case where LISTAGG() was perfectly apt.

A basic query like this will return a grouped count.

select count(*), catgy from some_categories group by catgy    COUNT(*) CATGY_CODE ---------- ----------          5 CATGY1              5 CATGY3              1 CATGY5              7 CATGY2              1 CATGY4              1 CATGY6       6 rows selected

But the string needs to look like

Rep:Catgy1 (2):Catgy2 (4):...

So I need to transpose those rows into a colon delimited string. Here’s how you can do it with SQL analytics.

declare   lc_hdr  varchar2(512); begin     select 'Rep:'||listagg(initcap(catgy)||' ('||count(*)||')' -- just build a fancy string                         ,':') within group (order by catgy) -- concatenated by ':', listed in order of catgy   into lc_hdr   from some_categories   group by catgy;    return lc_hdr; end anon;

An alternative may be bulk collecting the results into a PL/SQL array, then using apex_util.table_to_string() , but not when the problem can be solved with simple SQL ;p

转载本站任何文章请注明:转载至神刀安全网,谢谢神刀安全网 » SQL Analytics in every day APEX

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
分享按钮