A reader, March 10, 2003 - 3:35 pm UTC
Hi Tom
Thank you
same error, only in a materialized view
Craig, February 27, 2004 - 5:23 pm UTC
CREATE MATERIALIZED VIEW mv_dm_dne4_order AS
(SELECT me.id AS transaction_number
, me.account_id
, (SELECT NAME FROM v_account WHERE id = account_id) AS account_name
from v_mode_execution me);
I get an ORA-22818 subquery expressions not allowed here
Is there a limitation on materialized views that prevent subqueries in the select statement? Please, help me understand why I'm seeing this error.
Thanks!!
February 27, 2004 - 5:38 pm UTC
it is filed as a doc bug to have this restriction documented and it is in 10g (documented).
</code>
http://docs.oracle.com/docs/cd/B12037_01/server.101/b10736/basicmv.htm#sthref431 <code>
in this case
select me.id, me.acccount_id, va.name
from v_mode_execution me, v_account va
where me.account_id = va.id(+)
would more than satisfy your requirement and could even be "changes only" (aka 'fast') refreshable if the requisite primary key exists.
Not what I wanted to hear...
Craig, March 01, 2004 - 9:21 am UTC
...but it did answer my question as to why I couldn't make it work. Thanks yet again!
Any work around in 8i with scalar subqueries on MV ?..
Reader.., August 30, 2004 - 5:02 pm UTC
Hi Tom. Is there any work around for this bug in 8i for a MV using scalar subqueries ? The ones I am using doesn't imply simple outer joins like the one above.
August 30, 2004 - 5:37 pm UTC
but the "bug" is the documentation did not list this restriction -- in 10g, the documentation does list this restriction. so the "workaround in 8i" is to read this thread as the documentation of this limitation.
show me a scalar subquery that cannot be replaced with a join?
For example..
Reader, August 30, 2004 - 5:57 pm UTC
SELECT a.codigo,
(SELECT SUM(DECODE(GREATEST(ft.total-SUM(NVL(a.importe,0)),0),0,0,1))
FROM facturas ft, aplpagos a
WHERE ft.inm_codigo = a.fac_inm_codigo(+)
AND ft.pfc_codigo = a.fac_pfc_codigo(+)
AND a.estado(+) = 'A'
AND ft.fact_sust IS NULL
AND ft.factura IS NOT NULL
AND ft.inm_codigo = a.codigo
GROUP BY ft.pfc_codigo, ft.total) fact_pend,
(SELECT COUNT(*)
FROM pagos
WHERE inm_codigo = a.codigo
AND origen NOT IN ('R','D')
AND hora_elm IS NULL) pagos
FROM codigos a
August 30, 2004 - 6:21 pm UTC
it would appear that fact_pend is a constant.
that is, A in the fact_pend is aplpagos -- not codigos, so the scalar subquery is just a "constant" for every row. I'm having to GUESS since ambigously use "A" twice in there....
and only pagos varies.
so:
select a.codigo,
b.x,
nvl(c.cnt,0)
from codigos a,
(that fact_pend query, returns a single row) b,
(select inm_codigo, count(*) cnt
from pagos
where origen not in ('R','D')
and hora_elm is null
group by inm_codigo ) c
where a.codigo = c.inm_codigo(+)
but even if fact_pend varies -- and the a.codigo in the fact_pend query is from codigos -- variation on a theme, we'll do the same thing to fact pend.
Question
Reader, August 30, 2004 - 7:55 pm UTC
Sir, wouldn't this query:
select inm_codigo, count(*) cnt
from pagos
where origen not in ('R','D')
and hora_elm is null
group by inm_codigo
cause a full table scan instead of a index scan on the one I previously had ? this can be bad, since this table has a lot of rows (4 millions or something).
August 30, 2004 - 8:01 pm UTC
let the optimizer have a go at it eh?
or, add "and inm_codigo in (...)"
could be bad that you are using/abusing an index equally -- if the size of the driving table is small -- the "in" will reduce it
Dont know how to proceed...
Reader, August 30, 2004 - 8:05 pm UTC
Could you ellaborate a litle bit more ? I just dont know how can I use IN in there since I dont know what to IN' the data with ?
Also, the a in fact pend is indeed from the outer query (codigos), so that doesn't makes it an statement. I see I cannot reference any of the columns from the outer query in the inline view in the FROM clause.
Thanks!
August 30, 2004 - 8:11 pm UTC
select a.codigo from that table of course? the one you want to join to ultimately.
you don't need to reference any of the columns in the inline view from the "outer query" as they are all at the same level and you'll reference them in the join.
Interesting - two follow up questions
A reader, October 28, 2004 - 1:11 am UTC
Tom, can a query in this format be re-written without
using subqueries?
select
nvl(ax.description,bx.description)description,
nvl(ax.cost,0)cost,
(select sum(TS.cost) from A TS
where TS.datemonth = AX.datemonth
group by datemonth)pile_1,
(select sum(TS.revenue) from A TS
where TS.dateoftrade = AX.dateoftrade
group by dateoftrade) trade_revenue,
(select sum(TS.previous_revenue) from pior_time TS
where TS.dateoftrade = BX.dateoftrade
group by dateoftrade)prior_trade_revenue,
FROM
oldtrades AX
FULL OUTER JOIN othertrades BX
ON ( AX.code=BX.code)
Note:3644243.8,Bug No:3644243
says there is no workaround without upgrading.
October 28, 2004 - 7:46 am UTC
yes, it can -- hard, but doable.
a full outer join b
is
a outer join b
union all
b outer join a where a.join_key is null
inline views can remove the scalar subqueries
Unnesting scalar subqueries
Mikito Harakiri, October 28, 2004 - 1:35 pm UTC
Theory indeed says that it's possible to unnest any scalar subquery. Consider
select dname,
(select sum(sal) from emp
where deptno=dept.deptno),
(select sum(cost) from projects
where deptno=dept.deptno)
from dept
This is equivalent to
select dname, ssal, scost
from (
select deptno, sum(sal) ssal from emp
group by deptno
) ed, (
select deptno, sum(cost) scost from projects
group by deptno
) ep, dept
where dept.deptno=ed.deptno=ep.deptno
Next question naturally is if those inline view be merged:-)
October 28, 2004 - 7:30 pm UTC
yes, they can be. we'll be using count of distinct rowids to "divide" with. nasty but I used to do it in 7.0 and before -- before inline views...
how to use subquery in MV
Rama, March 21, 2005 - 3:39 pm UTC
Hi Tom
I know it is very simple but I am not able to use subquery in MV
create materialized view m_test as select a,b......x,(select max(invoice_id) from test ) from test1;
ora 22818 subquery expression not allowed
March 22, 2005 - 11:01 am UTC
correct, you need to join and group -- it is a (documented) restriction of the MV implementation
MV
rama, March 21, 2005 - 4:35 pm UTC
Thanks Tom I got it
Workaround for scaler expression in MVs
Rod West, October 17, 2005 - 3:53 am UTC
Tom,
We use scalar expressions in views, some of these we materialize. We have worked around the ORA-00922: missing or invalid option by recreating the views, as follows:
SQL> create or replace view TESTV
2 as
3 select (select 'A' from dual) c from dual;
View created.
SQL> create materialized view TESTV_MV
2 as
3 select c from TESTV;
select c from TESTV
*
ERROR at line 3:
ORA-22818: subquery expressions not allowed here
SQL> create or replace view TESTV
2 as
3 select 'B' c from dual;
View created.
SQL> create materialized view TESTV_MV
2 as
3 select c from TESTV;
Materialized view created.
SQL> create or replace view TESTV
2 as
3 select (select 'A' from dual) c from dual;
View created.
SQL> exec dbms_mview.refresh('TESTV_MV', 'C')
PL/SQL procedure successfully completed.
SQL> select * from TESTV_MV;
C
-
A
Is this a valid workaround if we always complete refresh the MVs?
October 17, 2005 - 7:49 am UTC
I would be concerned about that yes, That "bug" could be fixed in a later release - invalidating your trick.
actually, in 9ir2 and above, just this worked:
ops$tkyte@ORA9IR2> create or replace view TESTV
2 as
3 select (select 'A' from dual) c from dual;
View created.
ops$tkyte@ORA9IR2> create materialized view TESTV_MV
2 as
3 select c from TESTV;
Materialized view created.
error
Leonardo, April 17, 2007 - 1:37 pm UTC
select * from v$version;
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Solaris: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
create or replace view test_v
as
select (select 'A' from dual) c from dual;
View created.
create materialized view test_vw
as
select c from test_v;
create materialized view test_vw
*
ORA-22818: subquery expressions not allowed here
April 18, 2007 - 11:09 am UTC
ops$tkyte%ORA9IR2> create or replace view TESTV
2 as
3 select (select 'A' from dual) c from dual;
View created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create materialized view TESTV_MV
2 as
3 select c from TESTV;
Materialized view created.
ops$tkyte%ORA9IR2> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
Alternative simple work around
Phil Bermudez, April 16, 2009 - 2:15 pm UTC
Hi Tom,
I'm still a beginner with SQL so excuse me if this is a bad suggestion but when I came across this error I wrapped my scaler(select) subquery in a CAST. I assumed the error was because oracle didnt know what type the column needed to be in the materialised view table just from looking at the subquery.
Is this a valid solution?
Regards,
Phil
April 16, 2009 - 2:45 pm UTC
hmmm, sounds like a bug. I cannot encourage you to use that as I'm going to file one.
Either they should let us use the scalar subquery and the cast( scalar subquery) or they should prevent BOTH.
ops$tkyte%ORA10GR2> create materialized view mv
2 as
3 select dummy, (select count(*) from all_users)
4 from dual;
from dual
*
ERROR at line 4:
ORA-22818: subquery expressions not allowed here
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create materialized view mv
2 as
3 select dummy, cast( (select count(*) from all_users) as number )
4 from dual;
Materialized view created.
ops$tkyte%ORA10GR2> drop materialized view mv;
Materialized view dropped.
ops$tkyte%ORA10GR2> create materialized view mv
2 as
3 select dummy, nvl( (select count(*) from all_users), 0 )
4 from dual;
Materialized view created.
ops$tkyte%ORA10GR2> drop materialized view mv;
Materialized view dropped.
ops$tkyte%ORA10GR2> create materialized view mv
2 as
3 select dummy, (select count(*) from all_users)+0
4 from dual;
Materialized view created.
the documentation states that is not allowed
chaitanya, January 12, 2012 - 2:02 am UTC
Hi,
I am using a query like
select a,b,
NVL(rctl.amount_due_remaining,rctl.extended_amount)* decode(gb.currency_code,rcta.invoice_currency_code,1,
(select gl.conversion_rate
from gl_daily_rates gl
where gl.from_currency=rcta.invoice_currency_code
and GL.TO_CURRENCY=GB.CURRENCY_CODE
and gl.conversion_date=rcta.trx_date)) func_amt_due
from t1,t2
how can i group by the above
January 13, 2012 - 8:44 am UTC
you have no aggregates, why do you want to group by? and what did you want to group by - what attributes?
but basically - take your query and:
select a, b, func_amt_due
from (your query here)
and then you could
select a,b, sum(func_amt_due)
from (your query here)
group by a,b;
or whatever.