10May
Feuertips Gotta love that With Clause
By: Steven Feuerstein On: May 10, 2021 In: APEX Developer Solutions, Feuertips Comments: 0

WITH is now one of my favorite keywords in SQL.

That’s the keyword that lets us use modular design with SQL and also helps us avoid redundant logic in SQL statements.

The WITH clause (first introduced in Oracle Database 9i, also called “subquery factoring” and “common table expression”), to quote Tim Hall of ORACLE-BASE, “may be processed as an inline view or resolved as a temporary table. The advantage of the latter is that repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary table, rather than being requeried by each reference.”

And even more exciting (to PL/SQL fanatics like myself), Oracle Database 12c added the WITH function and WITH procedure variations, allowing you to define PL/SQL subprograms right inside a DML statement. There are two primary reasons to do this:

  1. Reduce the cost of context switching from SQL to PL/SQL.
  2. Avoid the repetition of logic in your SQL statement that can be expressed in PL/SQL.

I offer a couple of links below to make it easy for you to learn so much more about this feature. I’d like to focus in this post on a refactoring I just completed on a long SQL statement, leveraging both forms of the WITH clause. It was fun, and I learned a few lessons I thought I’d pass on to you.

It all started when an Insum consultant asked for help simplifying a query. Here’s what it looked like at the start (table names and so on have been changed to protect the innocent).

with cte_users as (
    select 
    dept_id,
    case when exists (
                        select 1
                        from
                            user_departments ud,
                            user_roles ur,
                            app_roles ar
                        where
                            ud.department_id = parent_dept_id and
                            ud.ur_id = ur.ur_id and
                            ur.role_id = ar.role_id and
                            role_code='CODE1' and 
                            app_id=:APP_ID) 
        then 'fa-check-circle' 
        else 'fa fa-times-circle' 
    end approver,
    case when exists (
                        select 1
                        from
                            user_departments ud,
                            user_roles ur,
                            app_roles ar
                        where
                            ud.department_id = parent_dept_id and
                            ud.ur_id = ur.ur_id and
                            ur.role_id = ar.role_id and
                            role_code='CODE2' and 
                            app_id=:APP_ID) 
        then 'fa-check-circle' 
        else 'fa fa-times-circle' 
    end adm,
    case when exists (
                        select 1
                        from
                            user_departments ud,
                            user_roles ur,
                            app_roles ar
                        where
                            ud.department_id = parent_dept_id and
                            ud.ur_id = ur.ur_id and
                            ur.role_id = ar.role_id and
                            role_code='CODE3' and 
                            app_id=:APP_ID) 
        then 'fa-check-circle' 
        else 'fa fa-times-circle' 
    end hr, 
    case when exists (
                        select 1
                        from
                            user_departments ud,
                            user_roles ur,
                            app_roles ar
                        where
                            ud.department_id = dept_id and
                            ud.ur_id = ur.ur_id and
                            ur.role_id = ar.role_id and
                            role_code='CODE4' and 
                            app_id=:APP_ID) 
        then 'fa-check-circle' 
        else 'fa fa-times-circle' 
    end s_approver,
    case when exists (
                        select 1
                        from
                            user_departments ud,
                            user_roles ur,
                            app_roles ar
                        where
                            ud.department_id = dept_id and
                            ud.ur_id = ur.ur_id and
                            ur.role_id = ar.role_id and
                            role_code='CODE5' and 
                            app_id=:APP_ID) 
        then 'fa-check-circle' 
        else 'fa fa-times-circle' 
    end s_adm,
    case when exists (
                        select 1
                        from
                            user_departments ud,
                            user_roles ur,
                            app_roles ar
                        where
                            ud.department_id = dept_id and
                            ud.ur_id = ur.ur_id and
                            ur.role_id = ar.role_id and
                            role_code='CODE6' and 
                            app_id=:APP_ID) 
        then 'fa-check-circle' 
        else 'fa fa-times-circle' 
    end s_hr 
   from all_departments 
)
select
    dept_desc department,
    b.adm Admins,
    b.approver Approvers,
    b.hr HR,
    b.s_approver S_Approvers,
    b.s_adm S_Admins,    
    b.s_hr S_HR,
    DECODE(NVL(b.adm, 'fa fa-times-circle'), 'fa-check-circle', 'green', 'red') adm_color,
    DECODE(NVL(b.approver, 'fa fa-times-circle'), 'fa-check-circle', 'green', 'red') approver_color,    
    DECODE(NVL(b.hr, 'fa fa-times-circle'), 'fa-check-circle', 'green', 'red') hr_color,
    DECODE(NVL(b.s_adm, 'fa fa-times-circle'), 'fa-check-circle', 'green', 'red') s_adm_color,
    DECODE(NVL(b.s_approver, 'fa fa-times-circle'), 'fa-check-circle', 'green', 'red') s_approver_color,    
    DECODE(NVL(b.s_hr, 'fa fa-times-circle'), 'fa-check-circle', 'green', 'red') s_hr_color
from
    all_departments v,
    cte_users b
where b.dept_id = v.dept_id

Anything catch your eye? 🙂

I know what catches my eye: repetition. As in the repeats of those case expressions and the six lines of decode.

Oh, and also: the six lines of decode, not in terms of repetition but in terms of decode. I don’t believe in decode anymore. Not now that we’ve got case.

The first thing I decided to do was reduce the code redundancy in the case expression. My first pass moved the four-way join query in each case expression out to its subquery:

with  
cte_role_check as (
   select role_code
     from user_departments ud,
          user_roles ur,
          app_roles ar
    where ud.ur_id = ur.ur_id 
      and ur.role_id = ar.role_id
      and app_id = :APP_ID), 
cte_users as (
   select dept_id,
          case
          when exists (
                       select 1
                         from cte_role_check
                        where role_code = 'CODE1'
                    ) then
                'fa-check-circle'
          else
                'fa fa-times-circle'
          end approver,
          ...

That certainly cut down on the code within each case expression, but there was still quite a lot there. Then I thought to myself: the case expression returns a value. Like a function. How about if I move all that logic into a function right inside the query?

Which changed the entire WITH clause in the original query into the following (note: I had to pass in :APP_ID to the function because it turns out you cannot reference a placeholder inside a function declared inside the query):

with
   function set_circle (
      p_app_id in varchar2, p_role_code in varchar2) return varchar2 
   is
      l_dummy integer;
   begin
      select 1
        into l_dummy
        from user_departments ud,
             user_roles ur,
             app_roles ar
       where ud.ur_id = ur.ur_id 
         and ur.role_id = ar.role_id
         and ar.role_code = p_role_code
         and hap.app_id = p_app_id;
 
      return 'fa-check-circle';
   exception
      when no_data_found then
         return 'fa fa-times-circle';
   end;
 
   cte_users as (
      select dept_id,
             set_circle(:APP_ID, 'CODE1') approver,
             set_circle(:APP_ID, 'CODE2') adm,
             set_circle(:APP_ID, 'CODE3') hr,
             set_circle(:APP_ID, 'CODE4') s_approver,
             set_circle(:APP_ID, 'CODE5') s_adm,
             set_circle(:APP_ID, 'CODE6') s_hr
        from all_departments
   )

Oh, I like that! Now, time to take a look at the decodes. I could simply switch it to using case, but there would still be a lot of repetition within each case expression. I think it’s time for another with!

with
   function set_color (p_value in varchar2) return varchar2 
   is
   begin
      return
         case NVL(p_value, 'fa fa-times-circle')
            when 'fa-check-circle' then
               'green'
            else 'red'
         end;
   end;

Which then leads to a refactored, much more concise, and utterly transformed query:

with
   function set_color (p_value in varchar2) return varchar2 
   is
   begin
      return
         case NVL(p_value, 'fa fa-times-circle')
            when 'fa-check-circle' then
               'green'
            else 'red'
         end;
   end;

   function set_circle (
      p_app_id     in  varchar2,
      p_role_code  in  varchar2
   ) return varchar2 
   is
      l_dummy integer;
   begin
      select 1
        into l_dummy
        from user_departments ud,
             app_roles ur,
             app_roles ar
       where ud.ur_id = ur.ur_id
         and ur.role_id = ar.role_id
         and hap.app_id = p_app_id;

      return 'fa-check-circle';
   exception
      when no_data_found then
         return 'fa fa-times-circle';
   end;

   cte_users as (
      select dept_id,
             set_circle(:APP_ID, 'CODE1') approver,
             set_circle(:APP_ID, 'CODE2') adm,
             set_circle(:APP_ID, 'CODE3') hr,
             set_circle(:APP_ID, 'CODE4') s_approver,
             set_circle(:APP_ID, 'CODE5') s_adm,
             set_circle(:APP_ID, 'CODE6') s_hr
        from all_departments
   )
select dept_desc department,
       b.adm Admins,
       b.approver Approvers,
       b.hr HR,
       b.s_approver S_Approvers,
       b.s_adm S_Admins,
       b.s_hr S_HR,
       set_color(b.adm) adm_color,
       set_color(b.approver) approver_color,
       set_color(b.hr) hr_color,
       set_color(b.s_adm) s_adm_color,
       set_color(b.s_approver) s_approver_color,
       set_color(b.s_hr) s_hr_color
  from all_departments v,
       cte_users b
 where b.dept_id = v.dept_id

But then….but then…I put this query into my interactive report and tried to run it. All I saw on the screen was…

“unsupported use of WITH clause”? Nooooooooo! How can this be? APEX is so tightly integrated with SQL and PL/SQL and the database and…

Well, it is not an APEX issue, folks. It’s the way this feature works. See, if you want to use the WITH clause either in a non-query DML statement (insert, update, delete, merge) or in a sub-query of a select statement, then you have to include the with_plsql hint, as in this example from Connor McDonald’s post (see “Further exploration” below):

SQL> insert /*+ WITH_PLSQL */ into TARGET_TABLE
  2  WITH
  3    function my_initcap(p_string varchar2) 
  4                return varchar2 is
  5      l_string varchar2(1000) := p_string;
  6    begin
          ...
 20    end;
 21  select my_initcap(surname)
 22  from   names;
 23  /

You might be thinking: but the WITH clause is already at the top of the select, not in a sub-query. So what’s the problem? The “problem” is that the query you write for an interactive report is not the query that is executed when the interactive report runs. APEX has a very smart report engine that transforms your query into something that can be used to provide all the wonderful features of an interactive report.

That’s why we get the “unsupported use” error. How do we fix it? APEX to the rescue! When you define a report, you can also provide an optimizer hint right in the properties for that report. This hint (or hints) will then be applied by APEX to the top level of the generated query, and then the report will work fine!

Summing it all up

Here are my take-aways from my “fun with WITH” this week:

  • Use the WITH clause to make your SQL more readable and concise.
  • WITH function still involves a context switch, though the overhead is reduced compared to a “normal” function stored in the database. So be careful how you use the function. It has the most impact when executed in the where clause.
  • You can’t put references to APEX placeholders inside the functions.
  • You need to add the WITH_PLSQL hint to your APEX reports.
  • You cannot use the WITH clause with queries executed inside a PL/SQL block.

Further exploration

Oracle documentation on WITH clause: https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6

Oracle-Base.com on WITH clause: https://oracle-base.com/articles/misc/with-clause#subquery-factoring

With and without WITH_PLSQL within a WITH SQL statement by Connor McDonald: https://connor-mcdonald.com/2019/05/31/with-and-without-with_plsql-within-a-with-sql-statement/

Share this:
Share

Leave reply:

Your email address will not be published. Required fields are marked *