Step by Step Creating a New View

The sequence of DML statements needed to create a new view follow an order determined by the foreign key constraints set-up in the template tables.


First enter your N_VIEW_TEMPLATES DML statements.  Prior to creating a DML statement to insert records to this template table, it would be advisable to look at the foreign key constraints.

Here we query for the foreign key constraints with this table.

select
constraint_name, r_constraint_name
from
all_constraints
where 1=1
and owner = 'NOETIX_SYS_TEST'
and table_name = 'N_VIEW_TEMPLATES'
and constraint_type = 'R'
/*this is a foreign key constraint*/;

Here are the results:

constraint_name,                         r_constraint_name

N_VIEW_TEMPLATES_FK1 N_APPL_OWNER_TEMPLATES_PK
N_VIEW_TEMPLATES_FK2 N_PROFILE_OPTION_TEMPLATES_PK

When composing this statement, be mindful that the N_VIEW_TEMPLATES_FK1 requires that your application label exist. Also, if the profile option is not null, then N_VIEW_TEMPLATES_FK2 requires that the option be set-up in N_PROFILE_OPTION_TEMPLATES. If you look at the example below, I am creating a custom view associated with our custom Trucking and Shipping Delivery application (TADS). I used ‘OE’ application because this is logical place for me to place our “Trucking and Shipping Delivery“ custom application view (from a role perspective).

INSERT INTO N_VIEW_TEMPLATES
(view_label
, application_label
, description
, profile_option
, essay
, keywords
, product_version
, include_flag
, export_view
, security_code
, special_process_code
, sort_layer
, freeze_flag
, created_by
, creation_date
, last_updated_by
, last_update_date
, original_version
, current_version
)
VALUES
( 'OE_CS_Tads_Loads_Details' --view_label
,'OE' -- application_label
, TO_CHAR(NULL) --description
, TO_CHAR(NULL) --profile_option
, 'This table will be used to track loads that are created '
'within the TADS system when a truck driver signs in.' --essay
,'K{\footnote Tad Load Details}' --keywords
,'*' --product_version
,'Y' --include_flag
,'Y' --export_view
, TO_CHAR(NULL) --security_code
,'XOPORG' --special_process_code
, TO_NUMBER(NULL) --sort_layer
,'Y' --freeze_flag
,'bob' --created_by
, SYSDATE --creation_date
,'bob' --last_updated_by
, SYSDATE --last_update_date
, '6.0.0.849' --original_version
, '6.0.0.849' --current_version
);
COMMIT;

Now that a view template record exists, I can then add arguments to insert records to the N_ROLE_VIEW_TEMPLATES table. Why this table?

Records added to this table require that an N_VIEW_TEMPLATES record exist for the view we are creating.

Again, we check constraints on the table which we want to insert records.
select
constraint_name, r_constraint_name
from
all_constraints
where 1=1
and owner = 'NOETIX_SYS_TEST'
and table_name = 'N_ROLE_VIEW_TEMPLATES'
and constraint_type = 'R'
/*this is a foreign key constraint*/;

Here are the results:

constraint_name,      r_constraint_name
N_ROLE_VIEW_TEMPLATES_FK1 N_ROLE_TEMPLATES_PK
N_ROLE_VIEW_TEMPLATES_FK2 N_VIEW_TEMPLATES_PK


If you look at my insertion argument, you will see that I have used an existing role and my new view:

INSERT INTO N_ROLE_VIEW_TEMPLATES
( role_label
, view_label
, product_version
, include_flag
, created_by
, creation_date
, last_updated_by
, last_update_date
)
VALUES
( 'ORDER_ENTRY' --role_label
,'OE_CS_Tads_Loads_Details' --view_label
,'*' --product_version
,'Y' --include_flag
,'bob' --created_by
, SYSDATE --creation_date
,'bob' --last_updated_by
, SYSDATE --last_update_date
);

COMMIT;


Next, records need to be added to the N_VIEW_QUERY_TEMPLATES table. We look at the foreign key constraints on this table:

select
constraint_name, r_constraint_name
from
all_constraints
where 1=1
and owner = 'NOETIX_SYS_TEST'
and table_name = 'N_VIEW_QUERY_TEMPLATES'
and constraint_type = 'R'
/*this is a foreign key constraint*/;

Here are the results:

constraint_name,  r_constraint_nam
N_VIEW_QUERY_TEMPLATES_FK1 N_VIEW_TEMPLATES_PK
N_VIEW_QUERY_TEMPLATES_FK2 N_PROFILE_OPTION_TEMPLATES_PK


If you look at the foreign key constraints on this table, you will see that the view you are adding has a record in the N_VIEW_TEMPLATES and a record (if not null) in the N_PROFILE_OPTION_TEMPLATES table.


Here is my insertion argument to this table:


INSERT INTO N_VIEW_QUERY_TEMPLATES
(
view_label
, query_position
, union_minus_intersection
, group_by_flag
, profile_option
, product_version
, include_flag
, view_comment
, created_by
, creation_date
, last_updated_by
, last_update_date
)
VALUES
( 'OE_CS_Tads_Loads_Details' --view_label
,1 --query_position
, TO_CHAR(NULL) --union_minus_intersection
,'N' --group_by_flag
, TO_CHAR(NULL) --profile_option
,'*' --product_version
,'Y' --include_flag
,TO_CHAR(NULL) --view_comment
, 'bob' -- created_by
, SYSDATE --creation_date
,'bob' --last_updated_by
, SYSDATE --last_update_date
);

COMMIT;


We are finally in familiar territory when we get to the N_VIEW_TABLE_TEMPLATES table (from a documentation perspective).

With this table, there is no shortage of foreign key constraints so it is imperative that the other records were added prior to this step. Here they are:

select
constraint_name, r_constraint_name
from
all_constraints
where 1=1
and owner = 'NOETIX_SYS_TEST'
and table_name = 'N_VIEW_TABLE_TEMPLATES'
and constraint_type = 'R'
/*this is a foreign key constraint*/;

The results of this query are:

constraint_name, r_constraint_name

N_VIEW_TABLE_TEMPLATES_FK1 N_VIEW_QUERY_TEMPLATES_PK
N_VIEW_TABLE_TEMPLATES_FK2 N_APPL_OWNER_TEMPLATES_PK
N_VIEW_TABLE_TEMPLATES_FK3 N_PROFILE_OPTION_TEMPLATES_PK
N_VIEW_TABLE_TEMPLATES_FK4 N_VIEW_TEMPLATES_PK

If you look at this DML statement, you will see that our previous commands have allowed us to run this without throwing a foreign key constraint error. In looking at other ‘OE’ application views, I see that they include the OE_OU_ACL_MAP_BASE views. This is a key component to maintain consistency with other ‘OE’ Noetix Views, so I add this table first.


INSERT INTO N_VIEW_TABLE_TEMPLATES
(view_label
, query_position
, table_alias
, from_clause_position
, application_label
, table_name
, product_version
, base_table_flag
, subquery_flag
, gen_search_by_col_flag
, created_by
, creation_date
, last_updated_by
, last_update_date
)
VALUES
('OE_CS_Tads_Loads_Details' --view_label
,1 --query_position
,'XMAP' -- table_alias
,1.1 --from_clause_position
,'OE' -- application_label
,'OE_OU_ACL_MAP_BASE' --table_name
,'*' --product_version
,'Y' --base_table_flag
,'N' --sub_query_flag
,'Y' --gen_search_by_col_flag
,'bob' --created_by
, SYSDATE --created_date
,'bob' --last_updated_by
, SYSDATE --last_updated_date
);

COMMIT;

After adding this table, I proceed to add additional tables. From a foreign key constraint perspective, one needs to add records first to the N_VIEW_WHERE_TEMPLATES table and then to the N_VIEW_COLUMNS_TEMPLATES table. The steps to do this as well as methodology are well documented.

My biggest concerns when I venture into creating a new view are the following:

1. Just because a new view can be created and one can leverage the Noetix View Administrator to regenerate views (and leverage its strengths and the consistency from an end user perspective), should you?

2. Is this really in the best interest of my company from a support perspective? Sometimes it is and sometimes it is not and one needs to really weigh the pros and cons. Who will maintain this?

3. Some components of this view set-up require are not obvious nor documented. My methodology is to pick a view that is as similar as possible and it really needs to be from the same Noetix role because one wants these components to behave like the “out of the box” Noetix Views for that role .

4. Are you prepared to support your view as the Noetix Views evolve?

5. Perhaps a clone of Noetix View would better suit your company’s best interest. At some future time I can describe how a clone could be created and weigh the pros and cons of this effort.

That is all for now.