Reputation: 45098

How can I implement this a recursive join in a group between two tables?

I have some messy data in a table called AVAILABLE_TEMPLATES. Here's a simple example:

TEMPLATE_GROUP TEMPLATE_NAME LOCALE
-------------- ------------- ------
RO              LTRU          fi_FI  
RO              LTRU          se_SE  
RO              LTRU          en_US  
BL              V1PRO         se_SE  
BL              V1PRO         en_US

I have another table containing locales called SYSTEM_LOCALES.

SYS_LOCALE
------
lv_LV  
fi_FI  
sv_SE  
en_US

The data that I'm looking to get by joining these two table should have a cartesian product of the rows in the SYS_LOCALE table and the distinct TEMPLATE_GROUP/TEMPLATE_NAME from the AVAILABLE_TEMPLATES table.

Our default locale is fi_FI. For each TEMPLATE_NAME in a TEMPLATE_GROUP, I would like to check if a matching locale is available, if it is, it should be returned as USE_LOCALE. If a matching locale is not found, I'd like to return the system's default locale i.e. fi_FI if it exists for the TEMPLATE_NAME and TEMPLATE_GROUP as USE_LOCALE.

Here's what joining the two tables should return:

TEMPLATE_GROUP TEMPLATE_NAME SYS_LOCALE  USE_LOCALE
-----------    ------------- ------      ----------
RO             LTRU          lv_LV       fi_FI      --There's a fi_FI locale but no lv_LV 
RO             LTRU          fi_FI       fi_FI    
RO             LTRU          se_SE       se_SE    
RO             LTRU          en_US       en_US    
BL             V1PRO         lv_LV       NULL       --There's no lv_LV or a fi_FI locale 
BL             V1PRO         fi_FI       NULL       --There's no fi_FI locale
BL             V1PRO         se_SE       se_SE    
BL             V1PRO         en_US       en_US

I haven't been able to figure this one and have been quite lost wit it. Would it have to be done with recursion? Thanks

Upvotes: 3

Answers (5)

Rob van Wijk

Reputation: 17705

The only construct that allows you to do this operation without reading the table more than once is the partitioned outer join.

Here is an example on your data set:

SQL> create table available_templates (template_group,template_name,locale)
  2  as
  3  select 'RO', 'LTRU', 'fi_FI' from dual union all
  4  select 'RO', 'LTRU', 'se_SE' from dual union all
  5  select 'RO', 'LTRU', 'en_US' from dual union all
  6  select 'BL', 'V1PRO', 'se_SE' from dual union all
  7  select 'BL', 'V1PRO', 'en_US' from dual
  8  /

Table created.

SQL> create table system_locales (sys_locale)
  2  as
  3  select 'lv_LV' from dual union all
  4  select 'fi_FI' from dual union all
  5  select 'se_SE' from dual union all
  6  select 'en_US' from dual
  7  /

Table created.

And the partitioned outer join:

SQL> select at.template_group
  2       , at.template_name
  3       , sl.sys_locale
  4       , nvl
  5         ( at.locale
  6         , max(decode(at.locale,'fi_FI',at.locale)) over (partition by at.template_group, at.template_name)
  7         ) use_locale
  8    from system_locales sl
  9         left outer join available_templates at
 10           partition by (at.template_group,at.template_name)
 11           on (at.locale = sl.sys_locale)
 12  /

TEMPLATE_GROUP TEMPLATE_NAME SYS_LOCALE USE_LOCALE
-------------- ------------- ---------- ----------
BL             V1PRO         en_US      en_US
BL             V1PRO         fi_FI
BL             V1PRO         lv_LV
BL             V1PRO         se_SE      se_SE
RO             LTRU          en_US      en_US
RO             LTRU          fi_FI      fi_FI
RO             LTRU          lv_LV      fi_FI
RO             LTRU          se_SE      se_SE

8 rows selected.

Here is the proof that the tables were only scanned once:

    SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'))
      2  /

    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  57br33gc6n1sc, child number 0
    -------------------------------------
    select at.template_group      , at.template_name      , sl.sys_locale
       , nvl        ( at.locale        ,
    max(decode(at.locale,'fi_FI',at.locale)) over (partition by
    at.template_group, at.template_name)        ) use_locale   from
    system_locales sl        left outer join available_templates at
     partition by (at.template_group,at.template_name)          on
    (at.locale = sl.sys_locale)

    Plan hash value: 921719364

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |      1 |        |      8 |00:00:00.01 |       6 |       |       |          |
|   1 |  WINDOW BUFFER               |                     |      1 |      1 |      8 |00:00:00.01 |       6 |  2048 |  2048 | 2048  (0)|
|   2 |   VIEW                       |                     |      1 |      1 |      8 |00:00:00.01 |       6 |       |       |          |
|   3 |    MERGE JOIN PARTITION OUTER|                     |      1 |      1 |      8 |00:00:00.01 |       6 |       |       |          |
|   4 |     SORT JOIN                |                     |      3 |      4 |      9 |00:00:00.01 |       3 |  2048 |  2048 | 2048  (0)|
|   5 |      TABLE ACCESS FULL       | SYSTEM_LOCALES      |      1 |      4 |      4 |00:00:00.01 |       3 |       |       |          |
|*  6 |     SORT PARTITION JOIN      |                     |      9 |      5 |      5 |00:00:00.01 |       3 |  2048 |  2048 | 2048  (0)|
|   7 |      TABLE ACCESS FULL       | AVAILABLE_TEMPLATES |      1 |      5 |      5 |00:00:00.01 |       3 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("AT"."LOCALE"="SL"."SYS_LOCALE")
       filter("AT"."LOCALE"="SL"."SYS_LOCALE")

Note
-----
   - dynamic sampling used for this statement (level=2)


35 rows selected.

Regards,
Rob.

Upvotes: 5

DazzaL

Reputation: 21993

you can do something like:

select t_base.template_group, t_base.template_name, l.sys_locale, 
      nvl( t.locale, default_locale)  use_locale
  from system_locales l
       cross join (select t.template_group, t.template_name, 
                          max(case t.locale when 'fi_FI' then  t.locale end) default_locale
                     from available_templates t
                    group by t.template_group, t.template_name) t_base
       left outer join available_templates t
               on t.template_group = t_base.template_group
              and t.template_name = t_base.template_name
              and t.locale = l.sys_locale
 order by 1, 2

Upvotes: 2

Stefan Steinegger

Reputation: 64638

Something like this?

It makes a Cartesian product of the locales and all the group / name constellations. Then it returns a row for each group / name / locale.

The CASE statement return:

the locale, when it exists for that group
fi_FI when it exists
null if there is none

select
  sl.SYS_LOCALE,
  at.TEMPLATE_GROUP,
  at.TEMPLATE_NAME,
  CASE 
    WHEN EXISTS (SELECT 1 FROM AVAILABLE_TEMPLATES at_loc 
      WHERE 
        at_loc.TEMPLATE_GROUP = at.TEMPLATE_GROUP, 
        at_loc.TEMPLATE_NAME = at_fi.TEMPLATE_NAME
        at_loc.LOCALE = sl.SYS_LOCALE) 
      THEN sl.SYS_LOCALE

    WHEN EXISTS (SELECT 1 FROM AVAILABLE_TEMPLATES at_fi 
      WHERE 
        at_fi.TEMPLATE_GROUP = at.TEMPLATE_GROUP, 
        at_fi.TEMPLATE_NAME = at_fi.TEMPLATE_NAME
        at_fi.LOCALE = 'fi_FI') 
      THEN 'fi_FI'
    ELSE NULL
  END as USE_LOCALE
from SYSTEM_LOCALES sl,
    (select 
      TEMPLATE_GROUP, 
      TEMPLATE_NAME
    from AVAILABLE_TEMPLATES
    GROUP BY TEMPLATE_GROUP, TEMPLATE_NAME)
    allgroups

Upvotes: 1

user359040

Reputation:

Try:

with templates as 
(select template_group, template_name, max(case locale when 'fi_FI' then locale end) available_default
 from available_templates
 group by template_group, template_name),
template_combinations as
(select t.*, s.sys_locale
 from templates t
 cross join sys_locale s)
select c.template_group, c.template_name, c.sys_locale, coalesce(a.locale, c.available_default) use_locale
from template_combinations c
left join available_templates a 
on c.template_group = a.template_group and c.template_name = a.template_name and c.sys_locale = a.locale

From the information provided, it appears that your database is improperly normalised - at the very least, there should be an extra table for combinations of template_group and template_name.

Upvotes: 1

Egor Skriptunoff

Reputation: 23767

select
   dat.template_group,
   dat.template_name,
   sl.sys_locale,
   nvl(at.locale, dat.fi_fi) as use_locale
from
   (
      select
         template_group,
         template_name,
         max(decode(locale, 'fi_FI', 'fi_FI')) as fi_fi
      from
         AVAILABLE_TEMPLATES
      group by
         template_group,
         template_name
   ) dat
   cross join SYS_LOCALE sl
   left join AVAILABLE_TEMPLATES at
      on at.template_group = dat.template_group
      and at.template_name = dat.template_name
      and at.locale = sl.sys_locale

Upvotes: 1

How can I implement this a recursive join in a group between two tables?

Answers (5)

Related Questions