#100CodeExamples – A challenge to learn and teach: Primary/Secondary Lookup

A while ago, Gregory Brown brought up the idea to do a “100 Code Examples” challenge:

In addition to #100DaysOfCode, it seems like #100CodeExamples would be a fun challenge to try.

Aim to produce 100 small annotated sample programs over the course of a year or two that illustrate all that you've learned.

— Practicing Developer (@practicingdev ) October 8, 2018

I liked the idea very much due to several reasons:

• The challenge will probably help me to track/be aware of what I learned
• Breaking down what I learned into small samples will reinforce the knowledge
• It will hopefully also exercise my skills to teach and share knowledge

I won’t put myself under pressure because that’s not the point of the challenge, but if I can manage I will post 1-2 examples on my blog here so the 100 Code Examples should be done in roughly a year.

The topics of the examples will most likely be related to my current work, so mainly SQL, PL/SQL and I will also try to add several examples around utPLSQL.

Every example will come with a very brief explanation of the goal and the critical snippet – hopefully annotated in a way that it’s understandable (I’d really appreciate your feedback on the latter part!).

If it’s possible I’ll also share a link to LiveSQL or a different environment where the example can be run immediately (won’t work with utPLSQL examples so easily).

Example: Primary/Secondary Lookup

We often have Primary/Secondary situations and sometimes we need an easy way to get all related entries based of a single entry’s ID.

In my Star Wars example, the Deathstar operates on several power nodes, some of them have secondary nodes in case the primary power node is no longer working.

We want to create a view one can query with the ID of any power node, no matter whether a primary or secondary, and get the full list of related power nodes as result.

The base table of nodes is very simple:

Column	Constraints
ID	PK, NOT NULL
LABEL
PRIMARY_NODE_FK

The view could look like this:

create or replace view v_deathstar_grouped_power_nodes as
  -- We first need to arrange the related nodes into groups
  with node_groups as (
    select group_entry.id group_id, -- Group-ID is the ID of the primary node
           members.id member_id
      from deathstar_power_nodes group_entry
      -- We join the base-table with itself to get all
      -- related entries for each row
      inner join deathstar_power_nodes members
            -- using either the primary-node reference,
            -- or if it's NULL (because it *is* the
            -- primary node) the ID
            on group_entry.id = nvl(members.primary_node_fk, members.id)
      -- We only do this for primary nodes
      where group_entry.primary_node_fk is null
  )
  select nodes.id power_node_id,
         groups.group_id,
         groups.member_id,
         member.label member_label,
         case
           when member.primary_node_fk is null then 1
           else 0
         end is_primary
    from deathstar_power_nodes nodes
    -- We join the groups via primary-node reference (if it exists) or the ID (of the primary node)
    inner join node_groups groups
           on nvl(nodes.primary_node_fk, nodes.id) = groups.group_id
    -- To get more information than only the ID of the group-members we need to join our base table again
    inner join deathstar_power_nodes member
           on groups.member_id = member.id;

You can find a full working example on LiveSQL.

This came in very handy in a production scenario at work this week.

Caution: This might be performance problematic for larger datasets.

Update 2018-12-05: Changed wording from Primary/Replica to Primary/Secondary because it might be easier to understand. Also made wording in WITH-clause more expressive. Thanks Fabsi!