How to select the a field of an object being a field of a table?-CodePudding

to test the code: https://dbfiddle.uk/?rdbms=oracle_21&fiddle=8cf685ff21a69c5e83a9861a56a71cbb

I have a table. One of its fields is an object. I only want to select a field of this object.

create type a as object(
  a  integer
)

-- I have succeeded to select the object
with b(c) as (select a(2) from dual) select c from b

--but I have failed to only select the field of this object
with b(c) as (select a(2) from dual) select c.a from b

ORA-00904: "C"."A": invalid identifier

CodePudding user response：

Give the subquery an alias and then reference the alias:

with b(c) as (
  select a(2) from dual
)
select b.c.a
from   b b

Note: Yes, I gave it the same alias as the table name. It might be less confusing to give it a different alias but the important thing is to use an alias and then refer to it as <table_alias>.<column_identifier>.<attribute_identifier>.

Outputs:

C.A

2

C.A
2

db<>fiddle here