When and How to Create View Joins Continued

In an earlier blog, I discussed creating view joins using an xu2 or xu5 script. Specifically, in writing about adding a join to a view by setting the base_table_flag to 'Y' for a specific table's record in the n_view_table_templates table, I indicated this (When and How to Create View Joins):

Okay, how do we join them?  Noetix advises that if a table is a base table (integral to the view and not outer joined within the view) then it can be a candidate for creating a Z$ column (the join columns).  The way to communicate this to the Noetix View Administrator tool is to set the base_table_flag = ‘Y’.

 My understanding of how to create a view join (specifically the Z$ column with the resulting view joins created by the Generator) has changed since this last post. I now realized that it is prevalent to not only set the base_table_flag to 'Y' (documented approach), but also set the key_view_label as well.

What happened?
 
The other day, I “cleaned-up” an xu2 script which creates a custom view.  I originally created the view prior to taking the Noetix View Customization Certification course.

My methodology for cleaning-up this xu2 script was to use a custom “copy the Noetix Template records” package.  It creates the insertion arguments in the xu2 script and spool the results.  It follows the same insertion format that is presented in the class for n_view_table_templates, n_view_where_templates and n_view_column_templates.  This became my new script. 

Anyways, my original custom view xu2 script was inserting a non-null value into the  key_view_label column of the n_view_table_templates table where the base_table_flag was set to ‘Y’ and my new spooled custom xu2 script did not include the non-null values into the key view_label column for my table records. 

The end result was that my join columns associated with the item master table, INV.MTL_SYSTEM_ITEMS_B, failed. To be clear, the view regenerated, but the join column was missing. 

When I compared my old and new script,  I noticed the omission and I added ‘INV_Items’  value for the key_view_label column for the n_view_table_templates table insertion argument for the item master table, INV.MTL_SYSTEM_ITEMS_B. I then included the 'INV_Items' information in the key_view_label_column.  This time, my join columns generated.  

 Next, I thought I would peruse the prevalence of the usage of the key_view_label column wherever the INV.MTL_SYSTEM_ITEMS_B table occurred in the n_view_table_templates table records:

NOETIX_SYS@erptst> list

  1  select key_view_label,

  2  application_label,

  3  count(1)

  4  from

  5  n_view_table_templates

  6  where

  7  base_table_flag = 'Y'

  8  and application_label in ('INV')

  9  and table_name = 'MTL_SYSTEM_ITEMS_B'

 10  and nvl(key_view_label,'INV_Items') in ('INV_Items')

 11  and include_flag = 'Y'

 12  group by

 13   key_view_label,

 14  application_label

 15* order by 2, 1

NOETIX_SYS@erptst> /

KEY_VIEW_LABEL                 APPLICATIO   COUNT(1)

============================== ========== ==========

INV_Items                      INV               161

                               INV                21

NOETIX_SYS@erptst> spool off;

From this analysis, it appears to occur more often than not (it would interesting to analyze this more…at a later date). 

To conclude (for those of us not using NoetixViews Workbench), I will be cognizant in the future that the key_view_label column is used by Noetix components to generate joins.