SQL Queries

This appendix contains some SQL*Plus queries that are useful for calculating the total number of nodes in a configuration model, and the number of different types of nodes in a configuration model.

This appendix covers the following topics:

Number of Nodes by Type

Use the Determine Number of Nodes by Type in a configuration model query to find out how many nodes of each type exist in a configuration model. The Numeric Identification of PS_NODE_TYPE table provides the numeric identification of the node types that need to be inserted in the query. For example, in the example that follows the table below, replace ps_node_type ID with 259 in the script to find out how many Component nodes exist in a configuration model.

Numeric Identification of PS_NODE_TYPE
PS_NODE_TYPE Description
259 Component
261 Feature
262 Option
263 Model Reference (BOM or Non-BOM)
264 Model Connector
272 Total
273 Resource
436 BOM Model Root node
437 BOM Option Class
438 BOM Standard Item

Determine Number of Nodes by Type in a Configuration Model (not including Model References)

This query returns the number of nodes by type in a configuration model, but does not include nodes that belong to referenced Models. .

SQL> SELECT count(*) 
FROM cz_ps_nodes 
WHERE deleted_flag = '0' 
and ps_node_type = ps_node_type ID 
and devl_project_id = devl_project_id 
/

Determine Number of Nodes by Type in a Configuration Model (including Model References)

This query returns the number of nodes by type in a configuration model, including nodes that belong to referenced Models.

SQL> SELECT count(*) 
FROM cz_ps_nodes 
WHERE deleted_flag = '0' 
and ps_node_type = ps_node_type ID 
and devl_project_id in (select distinct component_id from
cz_model_ref_expls where deleted_flag = '0' 
and model_id = devl_project_id)
/

In the examples above, insert a number from the Numeric Identification of PS_NODE_TYPE table for the variable ps_node_type ID, and insert the configuration model ID for devl_project_id. To determine a configuration model's DEVL_PROJECT_ID, see the Determine DEVL_PROJECT_ID of a Configuration Model example.

The following examples show queries that use the 'in' and 'not in' functions to find out how many BOM and non-BOM nodes exist in a configuration model.

Determine Number of BOM Nodes in a Configuration Model (not including Model References)

SQL> SELECT count(*) 
FROM cz_ps_nodes 
WHERE deleted_flag = '0' 
and ps_node_type in (436,437,438) 
and devl_project_id = devl_project_id 
/

Determine Number of BOM Nodes in a Configuration Model (including Model References)

SQL> SELECT count(*) 
FROM cz_ps_nodes 
WHERE deleted_flag = '0' 
and ps_node_type in (436,437,438) 
and devl_project_id in (select distinct component_id from
cz_model_ref_expls where deleted_flag = '0' 
and model_id = devl_project_id 
/

Determine Number of non-BOM Nodes in a Configuration Model (not including Model References)

SQL> SELECT count(*) 
FROM cz_ps_nodes 
WHERE deleted_flag = '0' 
and ps_node_type not in (436,437,438) 
and devl_project_id = devl_project_id
/

Determine Number of non-BOM Nodes in a Configuration Model (including Model References)

SQL> SELECT count(*) 
FROM cz_ps_nodes 
WHERE deleted_flag = '0' 
and ps_node_type not in (436,437,438) 
and devl_project_id in (select distinct component_id from
cz_model_ref_expls where deleted_flag = '0' 
and model_id = devl_project_id 
/

Determine DEVL_PROJECT_ID of a Configuration Model

SQL> SELECT devl_project_id, name
FROM cz_devl_projects
WHERE name like '%Sen%'
and deleted_flag=’0’

In this example, replace Sen with part of your model's name. The query returns both the Model's devl_project_id and its name, as shown below:

DEVL_PROJECT_ID             NAME
2020                        Sentinel Custom Desktop(204 137)

Number of Features by Type in a Configuration Model

Use the queries in this section to find out how many Features of a specific type exist in a configuration model. For each query, refer to the table below to determine the value to enter for the feature_type ID variable. Refer to Determine DEVL_PROJECT_ID of a Configuration Modelto determine your configuration model's devl_project_id.

Numeric Identification of FEATURE_TYPE
FEATURE_TYPE Description
0 Option
1 Integer
2 Decimal
3 Boolean
4 Text

Determine Number of Features by Type in a Configuration Model (not including Model References)

SQL> SELECT count(*) 
FROM cz_ps_nodes 
WHERE deleted_flag = '0' 
and ps_node_type = 261 
and feature_type = feature_type ID 
and devl_project_id = devl_project_id 
/

Determine Number of Features by Type in a Configuration Model (including Model References)

SQL> SELECT count(*) 
FROM cz_ps_nodes 
WHERE deleted_flag = '0' 
and ps_node_type = 261 
and feature_type = feature_type ID 
and devl_project_id in (select distinct component_id from
cz_model_ref_expls where deleted_flag = '0' 
and model_id = devl_project_id 
/

Number of Rules by Type in a Configuration Model

Use the query below to find out how many rules of each type exist in a configuration model.

When executing the query, insert the model_id of one or more models where indicated in the example, and enter a value from the table below for rule_type ID.

Numeric Identification of RULE_TYPE
RULE_TYPE Description
21 Logic Rule
22 Numeric Rule
23 Compatibility Rule
24 Compatibility Table
25 Func Preselect
26 Func Validate
27 Comparison Rule
29 Functional Companion
30 Design Chart
33 Runtime DISPLAY Condition
34 Runtime ENABLED Condition
200 Statement Rule
300 Configurator Extension Rule

Determine Number of Rules by Type in a Configuration Model

select
  model_id,
  rule_type,
  count (distinct rule_id) as distinct_rules,
  count (*) as rule_instances_all_models
from
  cz_model_ref_expls,
  cz_rules
where
  cz_model_ref_expls.DELETED_FLAG = '0' and
  cz_model_ref_expls.component_id = cz_rules.devl_project_id and
  cz_rules.deleted_flag = '0' and
  ps_node_type != 264 and
  cz_rules.rule_type = rule_type ID  and
  model_id in (list one or more models here, separated by commas)
group by
  model_id, rule_type
;

There are two rule counts in the above example. The rule counts usually return the same amount but not always. The reason for this is that it is possible for the same child model to be referenced more than once. In this case, a rule is likely to be loaded and active once for each place its model is included in the overall root model. The data storage volume is indicated by the DISTINCT_RULES calculation; this counts each unique rule only once. The runtime memory and processing load will usually be better indicated by the RULE_INSTANCES_ALL_MODELS calculation.

Number of Rules by Relation in a Configuration Model

The Determine Number of Rules by Relation in a Configuration Model example shows the query for determining the number of rules by relation type in a configuration model.

Numeric Identification of EXPR_RULE_TYPE
EXPR_RULE_TYPE Description
1 Requires
2 Implies
3 Excludes
4 Negates
5 Defaults
6 NumSelections
8 Contributes
10 Consumes

Determine Number of Rules by Relation in a Configuration Model

SQL> SELECT count(*) 
FROM cz_rules 
WHERE rule_type = rule_type 
and expr_rule_type = expr_rule_type 
and devl_project_id in ( 
SELECT distinct ps_node_id 
FROM cz_ps_nodes 
WHERE deleted_flag = '0' 
and ps_node_type in(436,263) 
START WITH ps_node_id = devl_project_id 
CONNECT BY DECODE(PRIOR ps_node_type,263,PRIOR reference_id, PRIOR ps_node_id)=
DECODE(PRIOR ps_node_type,263,ps_node_id,parent_id)) 
/

In the Determine Number of Rules by Relation in a Configuration Model example, insert the RULE_TYPE number shown in the Numeric Identification of RULE_TYPE table for the variable rule_type, insert the EXPR_RULE_TYPE number shown in the Numeric Identification of EXPR_RULE_TYPE table for the variable expr_rule_type, and insert the DEVL_PROJECT_ID of the configuration model for the variable devl_project_id (see the Determine DEVL_PROJECT_ID of a Configuration Model example).