Thursday, August 10, 2017

Thanks, LISTAGG. I owe ya one.

My son is in Boy Scouts and their fundraising campaign involves the placement of U.S. Flags on certain holidays. In it's infancy it was all handled via spreadsheets. You can do that when you're only talking a limited number of flags.

I, of course, wrote an APEX app for it.

It's what you do when you do what we do.

Since this wasn't a paid gig, I re-purposed the "Sample Database App". Everything was coming along. The desktop functionality was in place and looking grand. The mobile functionality one needed in the field was taking shape. I got to a mobile list view that needed to list the address and under it the details of the individual flags on that property....could be one...could be many. Not difficult but I wanted an elegant solution that wouldn't add rows to my list view.

Let's look briefly at the source code...

   select f.flag_id,

     from customers c,
          flags f
    where c.customer_id = f.customer_id

Nothing to get excited about here.

Looking at the Advanced Formatting options for the List View...

Hmm. This could potentially add blank lines. What I really needed to do is aggregate the list of flags for that property. What I really need is something like this...

Enter LISTAGG. I assume we've all used listagg to do this very thing but I didn't want the result to simply be a concatenated string of "Flag1, Flag2, Flag3, FlagN"...or did I?  A few well placed P's here and there and I got exactly what I wanted.

and here's the big reveal..."Move that Bus!"

Exactly what I needed. LISTAGG provided me a way, for display purposes, to effectively pivot the data. The takeaway here is don't rule out things like LISTAGG because at first glance they don't appear to deliver what you need. In this case the concatenated string I received just needed a little TLC.

...our journey continues

No comments: