QDA reduces code…

At my office we are building and supporting software for an insurance company. We have chosen for an architecture where there is one request record. This is connected to one or more variance records, which are in turn connected to the type of insurance. There is also a connection to a number of employees connected to this request. These employees can individually be connected to the insurance record using a result record.

Today I had to create a function that counts the connected result records to a varying record. This should be done for a couple of types, not every type available. We have given the modules, or insurance types, numbers. I had to create this function for modules 14, 15, 16 and 17. For the last three we have the Qnxo Development Architecture (QDA) in place, for module 14 we are not using the QDA yet (due to column name lengths etc.).

To create the function I had to build the code like we did before (before we used the QDA) and use the QDA (where possible). This gave me a good insight in the code that needs to be written with and without the use of the QDA.

FUNCTION pf_aantal_deelnemers(pi_vrn_seqnr IN NUMBER )
RETURN NUMBER
IS
  lv_returnvalue NUMBER;
  — NO QDA available
  — excedent (14)
  CURSOR c_excedent_14(p_vrn_seqnr NUMBER )
  IS
  SELECT t.ex14_seqnr
    FROM t_excedent_14 t
   WHERE 1 = 1
     AND t.ex14_vrn_seqnr = p_vrn_seqnr;
  CURSOR c_num_resultaat_14(p_ex14_seqnr NUMBER )
  IS
  SELECT COUNT( * ) the_count
    FROM t_resultaat_14 t
   WHERE 1 = 1
     AND t.rs14_ex14_seqnr = p_ex14_seqnr; 
  rc_excedent_14 c_excedent_14%ROWTYPE;
— QDA available
— WGA-gat (15)
tc_t_wga_gat_15 t_wga_gat_15_tp.t_wga_gat_15_tc;
— WGA ER (16)
tc_t_wga_er_16 t_wga_er_16_tp.t_wga_er_16_tc;
— Vaste aanvulling (17)
tc_t_wia_aanvulling_17 t_wia_aanvulling_17_tp.t_wia_aanvulling_17_tc;
BEGIN
  lv_returnvalue := 0;
  IF c_excedent_14%ISOPEN THEN
    CLOSE c_excedent_14;
  END IF; — c_excedent_14%IsOpen
  OPEN c_excedent_14( p_vrn_seqnr => pi_vrn_seqnr );
  FETCH c_excedent_14 INTO rc_excedent_14;
  IF c_excedent_14%FOUND THEN
    IF c_num_resultaat_14%ISOPEN THEN
      CLOSE c_num_resultaat_14;
    END IF; — c_num_resultaat_14%IsOpen
    OPEN c_num_resultaat_14( p_ex14_seqnr => rc_excedent_14.ex14_seqnr );
    FETCH c_num_resultaat_14 INTO lv_returnvalue;
    IF c_num_resultaat_14%ISOPEN THEN
      CLOSE c_num_resultaat_14;
    END IF; — c_num_resultaat_14%IsOpen
  ELSE
    IF c_excedent_14%ISOPEN THEN
      CLOSE c_excedent_14;
    END IF; — c_excedent_14%IsOpen 
    tc_t_wga_gat_15 := t_wga_gat_15_qp.ar_fk_wg15_vrn_seqnr
                                                        ( wg15_vrn_seqnr_in => pi_vrn_seqnr );
    IF tc_t_wga_gat_15.COUNT > 0 THEN
      lv_returnvalue := t_resultaat_15_qp.num_fk_rs15_wg15_seqnr
                          ( rs15_wg15_seqnr_in => tc_t_wga_gat_15( 1 ).wg15_seqnr );
    ELSE
      tc_t_wga_er_16 := t_wga_er_16_qp.ar_fk_wg16_vrn_seqnr
                                                        ( wg16_vrn_seqnr_in => pi_vrn_seqnr );
      IF tc_t_wga_er_16.COUNT > 0 THEN
        lv_returnvalue := t_resultaat_16_qp.num_fk_rs16_wg16_seqnr
                            ( rs16_wg16_seqnr_in => tc_t_wga_er_16( 1 ).wg16_seqnr );
      ELSE
        tc_t_wia_aanvulling_17 := t_wia_aanvulling_17_qp.ar_fk_wa17_vrn_seqnr
                                                        ( wa17_vrn_seqnr_in => pi_vrn_seqnr );
        IF tc_t_wia_aanvulling_17.COUNT > 0 THEN
          lv_returnvalue := t_resultaat_17_qp.num_fk_rs17_wa17_seqnr
                  ( rs17_wa17_seqnr_in => tc_t_wia_aanvulling_17( 1 ).wa17_seqnr );
        ELSE
          lv_returnvalue := 0;
        END IF;
      END IF;
    END IF;
  END IF;
  RETURN lv_returnvalue;
END pf_aantal_deelnemers;

 The code written in RED is ‘normal’ PL/SQL code, the code written in GREEN is ‘QDA’ code.
As you can see, the code written using the QDA is much less than the ‘normal’ code. Each type takes about 4 lines of code , instead of the about 30 lines of code that had to be written to get the job done without the QDA(the GREEN section is used for three(!) modules, where the RED section is used for one(!) module only) .

Of course there is code in the packages of the QDA, but this code is generated by QNXO, so I don’t have to write this code myself and when I have created these packages once, I can use them in all my custom code.