printing information about sub-type within a nested table

Question

Here's the schema:

CREATE OR REPLACE TYPE Component_ty AS OBJECT (
Code VARCHAR(10),
Component_Description VARCHAR(100),
Component_Type VARCHAR(10))
NOT FINAL;

CREATE OR REPLACE TYPE External_Component_ty UNDER Component_ty(
External_Company REF External_Company_ty);
   
CREATE OR REPLACE TYPE component_ty_nt AS TABLE OF REF component_ty;

CREATE TABLE Component OF component_ty(
Code PRIMARY KEY, 
Component_Description NOT NULL,
Component_Type NOT NULL,
CONSTRAINT component_type__chk
CHECK (component_type IN ('body', 'Body', 'engine', 'Engine', 'equipment', 'Equipment'))
);

CREATE TABLE MODEL (
model_name VARCHAR(20),
model_year NUMBER,
consumption VARCHAR(6) NOT NULL,
Component component_ty_nt,
Distributor distributor_ty_nt,
Car car_ty_nt,
CONSTRAINT t_pk PRIMARY KEY (Model_Name, Model_Year))
NESTED TABLE Component STORE AS component_ty_nt_TAB
NESTED TABLE Distributor STORE AS distributor_ty_nt_TAB
NESTED TABLE Car STORE AS car_ty_nt_TAB

how can I retrieve information of the models for which the value external_company in external_component is null?

Once again, you have not given a complete [MRE] as there are **many** undefined types referenced in the question so your code will not compile. Those tend to be irrelevant to the question so you should ensure that your questions are **MINIMAL** by eliminating everything that is not relevant to the question and including everything relevant in the question (including sample data) so that we can copy/paste and compile your code without errors. — MT0, Apr 14 '23 at 00:43

MT0 · Answer 1 · 2023-04-14T00:41:26.347

You can check that the value in the nested table is of the correct type and has a NULL value using:

SELECT m.model_name,
       m.model_year,
       c.code
FROM   model m
       CROSS JOIN LATERAL (
         SELECT DEREF(c.COLUMN_VALUE).code AS code
         FROM   TABLE(m.component) c
         WHERE  DEREF(c.COLUMN_VALUE) IS OF (External_Component_ty)
         AND    TREAT(DEREF(c.COLUMN_VALUE) AS External_Component_ty).External_Company IS NULL
       ) c

Which, for the minimal example:

CREATE OR REPLACE TYPE Component_ty AS OBJECT (
  Code VARCHAR(10)
) NOT FINAL;

CREATE OR REPLACE TYPE External_Component_ty UNDER Component_ty(
  External_Company NUMBER
);

CREATE OR REPLACE TYPE component_ty_nt AS TABLE OF REF component_ty;

CREATE TABLE Component OF component_ty;

CREATE TABLE MODEL (
  model_name VARCHAR(20),
  model_year NUMBER,
  Component component_ty_nt,
  CONSTRAINT t_pk PRIMARY KEY (Model_Name, Model_Year)
)
NESTED TABLE Component STORE AS component_ty_nt_TAB

INSERT INTO component
SELECT external_component_ty('A', 1) FROM DUAL UNION ALL
SELECT external_component_ty('B', NULL) FROM DUAL UNION ALL
SELECT component_ty('C') FROM DUAL;

INSERT INTO model
SELECT 'A', 1, component_ty_nt((SELECT REF(c) FROM component c WHERE code = 'A')) FROM DUAL UNION ALL
SELECT 'B', 1, component_ty_nt((SELECT REF(c) FROM component c WHERE code = 'B')) FROM DUAL UNION ALL
SELECT 'C', 1, component_ty_nt((SELECT REF(c) FROM component c WHERE code = 'C')) FROM DUAL UNION ALL
SELECT 'D', 1, component_ty_nt(
  (SELECT REF(c) FROM component c WHERE code = 'A'),
  (SELECT REF(c) FROM component c WHERE code = 'C')
) FROM DUAL UNION ALL
SELECT 'E', 1, component_ty_nt(
  (SELECT REF(c) FROM component c WHERE code = 'B'),
  (SELECT REF(c) FROM component c WHERE code = 'C')
) FROM DUAL UNION ALL
SELECT 'F', 1, component_ty_nt(
  (SELECT REF(c) FROM component c WHERE code = 'A'),
  (SELECT REF(c) FROM component c WHERE code = 'B'),
  (SELECT REF(c) FROM component c WHERE code = 'C')
) FROM DUAL;

Outputs:

MODEL_NAME	MODEL_YEAR	CODE
B	1	B
E	1	B
F	1	B

fiddle

this query return those components belonging to a model that are not purchased from external company, but what I need is to get in output those models which have all their component not purchased from external companies. — mikerug88, Apr 14 '23 at 08:51
@mikerug88 The question asked was "how can I retrieve information of the models for which the value external_company in external_component is null?" This finds the models where there is any `external_component_ty` (and not a base `component_ty`) and where the `external_company` column is `NULL`. Which appears to be exactly what was asked for in the question. Your comment is asking an entirely different question and, for that, you should [ask a new question](https://stackoverflow.com/questions/ask) (and make sure the question is what you intended to ask and not something else). — MT0, Apr 14 '23 at 10:01
You’re right, that’s why I was claryfing my question in the last comment. Thank you anyway for your answers — mikerug88, Apr 14 '23 at 10:17

printing information about sub-type within a nested table

1 Answers1