"SQL command did not complete correctly" when using a subquery
The following query works for me:
select
a.column_value as artigo
from
encomenda e, table(e.artigos) a
where
e.id = 2;
This query returns the following output (one string of type menu_t
and another of type bebida_menu_t
). Note that this is not a simple text, but an object of a specific type for itself, since this is an object-relational database.
From this result, I only want to extract rows menu_t
. So, I tried the following query:
select *
from (select
a.column_value as artigo
from
encomenda e, table(e.artigos) a
where e.id = 2) as subquery
where
subquery.artigo is of (menu_t);
Which gives me the error
- 00000 - "SQL command not executed properly"
and I don't understand why.
UPDATE:
The problem seems to be with the keyword as
. So it should be:
select *
from (select
a.column_value as artigo
from
encomenda e, table(e.artigos) a
where e.id = 2) subquery --removed the 'as' here
where
value(subquery.artigo) is of (menu_t); --added value() because they were inconsistent types
However, I am now getting an error that subquery.artigo
is an invalid identifier.
- 00000 - "% s: Invalid ID"
source to share
So, to fix the first issue, as I said in the update, in Oracle we shouldn't use the as
alias keyword . Thus, the request will look like this:
select *
from (select
a.column_value artigo
from
encomenda e, table(e.artigos) a
where e.id = 2) subquery
where
subquery.artigo is of (menu_t);
But after that I came up with another error as described in the update. What happened was what I was trying to use is of
in REF
and it warned me that it was an invalid id but took me for a while and many are trying to figure out the correct query. So I needed to use deref()
on a column art.column_value
.
After all, I don't even need a nested subquery. Final request:
select value(e).id id, art.column_value artigo
from encomenda e, table(e.artigos) art
where e.id = 2 and
deref(art.column_value) is of (menu_t);
source to share