Home » SQL & PL/SQL » SQL & PL/SQL » Materialized view ......subquery expression not allowed error
Materialized view ......subquery expression not allowed error [message #301428] Wed, 20 February 2008 10:12 Go to next message
Azhark234
Messages: 9
Registered: February 2008
Location: India
Junior Member
I want to create MV with the following query.Since it contains subquery expressions in it,it is giving error.

Can anybody write an equivalent query for my query without having subquery expressions so that i can use it in MV.

select rownum as ROWNUMBER,f.id Folder_ID, concatyear(f.id) year_name,
(select nvl(fr.requestor,fr.requested_by) from fts_folder_request fr where
fr.folder_id=f.id and fr.status=0 ) Pending_Requestor,
(select nvl(fr.requestor,fr.requested_by)
from fts_folder_request fr,fts_folder_checkout_history fch where
fr.folder_id=f.id and fch.folder_requested=fr.id and fch.return_date is null) CheckOut_To
from fts_folder f;

Thanks.
Re: Materialized view ......subquery expression not allowed error [message #301430 is a reply to message #301428] Wed, 20 February 2008 10:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It would be easier if you format your query.
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code and align the columns in result.
Use the "Preview Message" button to verify.
Have a look at SQL Formatter.
Always post your Oracle version (4 decimals).

In addition, don't just describe what you have, copy and paste your screen.
Are you sure the SELECT is a valid one?

Regards
Michel
Re: Materialized view ......subquery expression not allowed error [message #301442 is a reply to message #301430] Wed, 20 February 2008 11:45 Go to previous messageGo to next message
Azhark234
Messages: 9
Registered: February 2008
Location: India
Junior Member
Yeah...the query is a valid query and a view has been created using it.Also data has been retrieved but it is taking lot of time (more than an hour to fetch 100k records).Thats why we want to go for MV.

As far as formatting code ... I will follow it next time onwards as am new to this forum.

Thanks.
Re: Materialized view ......subquery expression not allowed error [message #301443 is a reply to message #301442] Wed, 20 February 2008 12:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But unless you repost your query formatted it is not easier to read it.
So do it... unless you don't want help.
And post what is requested.

Regards
Michel

[Updated on: Wed, 20 February 2008 12:04]

Report message to a moderator

Re: Materialized view ......subquery expression not allowed error [message #301459 is a reply to message #301443] Wed, 20 February 2008 13:20 Go to previous messageGo to next message
Azhark234
Messages: 9
Registered: February 2008
Location: India
Junior Member
Hi Michel Cadot,

Here is the valid query.Please suggest an alternative without subquery expressions.

SELECT ROWNUM AS RowNumber,
f.Id Folder_Id,
Concatyear(f.Id) Year_Name,
(SELECT Nvl(fr.RequestOr,fr.Requested_By)
FROM fts_Folder_Request fr
WHERE fr.Folder_Id = f.Id
AND fr.Status = 0) Pending_RequestOr,
(SELECT Nvl(fr.RequestOr,fr.Requested_By)
FROM fts_Folder_Request fr,
fts_Folder_CheckOut_History fch
WHERE fr.Folder_Id = f.Id
AND fch.Folder_Requested = fr.Id
AND fch.Return_Date IS NULL ) CheckOut_To
FROM fts_Folder f;
Re: Materialized view ......subquery expression not allowed error [message #301463 is a reply to message #301459] Wed, 20 February 2008 13:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I just would like you follow the links I gave and post your query in a formatted way to help us read it.

Regards
Michel
Re: Materialized view ......subquery expression not allowed error [message #301464 is a reply to message #301428] Wed, 20 February 2008 13:40 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Why are you using sub selects in the select clause when you can use simple joins and make it a very simple query. You are doing it all wrong. I will give one hint, I will give you the from clause and you figure the rest out.

select ...
from  fts_folder f,
      fts_folder_request fr,
      fts_folder_request fr2,
      fts_folder_checkout_history fch
where ...
Re: Materialized view ......subquery expression not allowed error [message #301546 is a reply to message #301464] Thu, 21 February 2008 00:43 Go to previous messageGo to next message
Azhark234
Messages: 9
Registered: February 2008
Location: India
Junior Member
Hi Bill.

I have tried using joins but not getting any data.Whereas using the subqueries am able to get it and it is correct data.

Formatting the query: i formatted it using SQL formatter and then copy paste it here.But the format is missing here.
Re: Materialized view ......subquery expression not allowed error [message #301555 is a reply to message #301546] Thu, 21 February 2008 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Formatting the query: i formatted it using SQL formatter and then copy paste it here.But the format is missing here

Read the section I said in the forum guide, it is explained how you have to do it.

Regards
Michel
Re: Materialized view ......subquery expression not allowed error [message #301556 is a reply to message #301546] Thu, 21 February 2008 00:51 Go to previous messageGo to next message
Azhark234
Messages: 9
Registered: February 2008
Location: India
Junior Member
I hope this is somewhat clear format.

SELECT ROWNUM AS RowNumber,
  f.Id Folder_Id,
  Concatyear(f.Id) Year_Name,
      (SELECT Nvl(fr.RequestOr,fr.Requested_By)
            FROM fts_Folder_Request fr
            WHERE fr.Folder_Id = f.Id
            AND fr.Status = 0) Pending_RequestOr,
      (SELECT Nvl(fr.RequestOr,fr.Requested_By)
            FROM fts_Folder_Request fr,
            fts_Folder_CheckOut_History fch
            WHERE fr.Folder_Id = f.Id
            AND fch.Folder_Requested = fr.Id
            AND fch.Return_Date IS NULL ) CheckOut_To
FROM fts_Folder f;



This is actual query.Want to eliminate subquery expressions
from it so that it can be used to create MV.

[Updated on: Thu, 21 February 2008 01:06]

Report message to a moderator

Re: Materialized view ......subquery expression not allowed error [message #301594 is a reply to message #301556] Thu, 21 February 2008 01:51 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
I posted a solution, but Michel deleted it. I would post it again, but Michel would just delete it again. Good luck getting some actual help.
Re: Materialized view ......subquery expression not allowed error [message #301596 is a reply to message #301594] Thu, 21 February 2008 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No problem, repost it following the rules, you know them, there is no difficulty to follow them, just:
"Make sure that lines of code do not exceed 80 characters when you format...
Use the "Preview Message" button to verify. "
It is very very very easy.

Regards
Michel

[Updated on: Thu, 21 February 2008 02:02]

Report message to a moderator

Re: Materialized view ......subquery expression not allowed error [message #301644 is a reply to message #301556] Thu, 21 February 2008 04:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As Bill said, query can be rewritten as (I think but I don't have a test case):
SELECT ROWNUM AS RowNumber,
  f.Id Folder_Id,
  Concatyear(f.Id) Year_Name,
  Nvl(fr1.RequestOr,fr1.Requested_By) Pending_RequestOr,
  Nvl(fr2.RequestOr,fr2.Requested_By) CheckOut_To
from fts_Folder f, fts_Folder_Request fr1, 
     fts_Folder_Request fr2, fts_Folder_CheckOut_History fch
where fr1.Folder_Id (+) = f.Id
  AND fr1.Status (+) = 0
  AND fr2.Folder_Id (+) = f.Id
  AND fch.Folder_Requested = fr2.Id
  AND fch.Return_Date IS NULL
/

You have to use outer join.

Regards
Michel
Re: Materialized view ......subquery expression not allowed error [message #301674 is a reply to message #301594] Thu, 21 February 2008 05:49 Go to previous messageGo to next message
Azhark234
Messages: 9
Registered: February 2008
Location: India
Junior Member
Can you please post your version of the query also so that i will test it.

Though Michel has given the query using outer joins but it is giving not the correct result.Anyway Thanks to Michel for his efforts.Actually when i tested it for f.id=73763 no rows selected whereas i should get the data for that id.
I am unable to find what is missing.

It would be helpful if anybody give the outer join query.
Thanks.
Re: Materialized view ......subquery expression not allowed error [message #301684 is a reply to message #301674] Thu, 21 February 2008 06:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It would of great help if you posted a test case that can show us why there are differences.
Otherwise how could we fix when we don't where is the error (and if there actually is an error).

Regards
Michel
Re: Materialized view ......subquery expression not allowed error [message #301685 is a reply to message #301674] Thu, 21 February 2008 06:17 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Can ROWNUM even be used in a MV?

Ross Leishman
Re: Materialized view ......subquery expression not allowed error [message #301687 is a reply to message #301685] Thu, 21 February 2008 06:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not allowed if you enable query rewrite:
SQL> create materialized view mv as select rownum rn, t.* from t;

Materialized view created.

SQL> drop materialized view mv ;

Materialized view dropped.

SQL> create materialized view mv enable query rewrite as select rownum rn, t.* from t;
create materialized view mv enable query rewrite as select rownum rn, t.* from t
                                                           *
ERROR at line 1:
ORA-30353: expression not supported for query rewrite

Regards
Michel
Re: Materialized view ......subquery expression not allowed error [message #301693 is a reply to message #301684] Thu, 21 February 2008 07:00 Go to previous messageGo to next message
Azhark234
Messages: 9
Registered: February 2008
Location: India
Junior Member
Hi Michel when I ran this query, I got result :

select rownum as ROWNUMBER,f.id Folder_ID, concatyear(f.id) year_name,
(select nvl(fr.requestor,fr.requested_by) from fts_folder_request fr where
fr.folder_id=f.id and fr.status=0 ) Pending_Requestor,
(select nvl(fr.requestor,fr.requested_by)
from fts_folder_request fr,fts_folder_checkout_history fch where
fr.folder_id=f.id and fch.folder_requested=fr.id and fch.return_date is null) CheckOut_To
from fts_folder f where f.id=73763;



ROWNUMBER  FOLDER_ID YEAR_NAME                                     PENDING_REQUESTOR CHECKOUT_TO
---------- ---------- -------------------------------------------- ----------------- -----------
         1      73763  1977, 1978, 1979, 1980, 1981, 1982  


In the output you might notice that years r concatenated (whereas in DB each year is stored as seperate row) and this is being done using function 'concatyear',which is being called for every f.id and we have more than 100k such f.id's.

Initially we created view but now itis taking lot of time to output. So we r looking for MV...but MV doesn't support subquery.So is my problem.

Thanks.

And when I ran the outer join query given by you for the same f.id=73763 no result got.

I hope this would help you in analyzing it.
Is it now OK with the formatting...

[Updated on: Thu, 21 February 2008 07:14] by Moderator

Report message to a moderator

Re: Materialized view ......subquery expression not allowed error [message #301698 is a reply to message #301693] Thu, 21 February 2008 07:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said, as I don't have your data nor your tables I can't see why there is a difference in the result set.
If you post the table definitions and the data that result in different rows (create table and insert statements) I can see the error.

Regards
Michel
Re: Materialized view ......subquery expression not allowed error [message #301701 is a reply to message #301693] Thu, 21 February 2008 07:17 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Azhark234 wrote on Thu, 21 February 2008 14:00

...but MV doesn't support subquery.So is my problem.



That is a wrong assumption: please re-read Michels post: his examples shows what's wrong/

The problem is not the subquery, but the fact you're using ROWNUM.
Re: Materialized view ......subquery expression not allowed error [message #301704 is a reply to message #301701] Thu, 21 February 2008 07:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Marc,

Scalar subqueries are not allowed in MV (at least in the current versions) and this is why the query must be rewriten:
SQL> create materialized view mv as select (select 1 from dual) x from t;
create materialized view mv as select (select 1 from dual) x from t
                                                           *
ERROR at line 1:
ORA-22818: subquery expressions not allowed here

Regards
Michel

[Updated on: Thu, 21 February 2008 07:24]

Report message to a moderator

Re: Materialized view ......subquery expression not allowed error [message #301707 is a reply to message #301704] Thu, 21 February 2008 07:35 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Ok.

But did we get the Oracle-error the OP got when trying to create the MV? If so, then I've missed it somehow
Re: Materialized view ......subquery expression not allowed error [message #301710 is a reply to message #301707] Thu, 21 February 2008 07:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is in the title (if you know all Oracle errors. Wink )

Regards
Michel
Re: Materialized view ......subquery expression not allowed error [message #301712 is a reply to message #301710] Thu, 21 February 2008 07:51 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Michel Cadot wrote on Thu, 21 February 2008 14:48
It is in the title (if you know all Oracle errors. Wink )

Regards
Michel



Laughing

1 - Who read those titles anyway
2 - Who knows all those Oracle errors? (at my shop they think I know all of them by heart, I usually only get the error-number)
Re: Materialized view ......subquery expression not allowed error [message #301732 is a reply to message #301701] Thu, 21 February 2008 08:24 Go to previous messageGo to next message
Azhark234
Messages: 9
Registered: February 2008
Location: India
Junior Member
Hi,

I have used query to create MV like this....

create or replace materialized view mv1 
refresh fast start with sysdate next sysdate+1/77000 
as 
select rownum rnum, t.* 
from (Actual query (op) with subquery expressions (minus) rownum) t;

Is it what Michel wanted me to do?

And i got error :subquery expressions are not allowed within query

[Updated on: Thu, 21 February 2008 08:28] by Moderator

Report message to a moderator

Re: Materialized view ......subquery expression not allowed error [message #301736 is a reply to message #301732] Thu, 21 February 2008 08:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Hey, this is absolutly not what I want as I know the root of your error is scalar subqueries.

I just want a test case to reproduce the difference in queries.
Can you post it?

Regards
Michel
Re: Materialized view ......subquery expression not allowed error [message #301746 is a reply to message #301736] Thu, 21 February 2008 08:59 Go to previous messageGo to next message
Azhark234
Messages: 9
Registered: February 2008
Location: India
Junior Member
Hi,
Here I am giving details of table with sample data:
If still you want anything...i will provide.

FTS_FOLDER table(ID PK....some other columns r there ,now of no interest)

        ID CODE
---------- ------------------------------
      4787 ACFR03030
      4788 ACFR03031
      4789 ACFR03032
      4790 ACFR03033
      4791 ACFR03034



FTS_YEAR table

select * from fts_year where rownum<6;
 
        ID NAME
---------- -----
         1 2008
         2 2007
         3 2006
         4 2005
         5 2004
 



FTS_FOLDER_YEAR table (here YEAR refers ID of FTS_YEAR table and FOLDER refers ID of FTS_FOLDER ,and FOLDER may repeat for different YEAR values..though not shown)

ID     FOLDER       YEAR
---------- ---------- ----------
         1       4086         22
         2       4087         22
         3       4088         22
         4       4089         22
         5       4090         22


FTS_FOLDER_REQUEST table (here FOLDER_ID refers ID of FTS_FOLDER)

ID  FOLDER_ID  REQUESTOR REQUESTED_BY     STATUS
---------- ---------- ---------- ------------ ----------
      1786       4089               200003241          1
      1787       4090               200003241          1
      1788       4090               200004373          1
      1789       4091               200003241          1
      1790       4092               200003241          1
 


FTS_FOLDER_CHECKOUT_HISTORY table(FOLDER_REQUESTED refers FOLDER of FTS_FOLDER_REQUEST)


      ID FOLDER_REQUESTED
---------- ----------------
      1526             1786
      1527             1787
      1528             1788
      1529             1789
      1530             1790



And in the concatyear function we r concatenating years from FTS_YEAR table for each ID of FTS_FOLDER.

Is it now sufficient to rewrite a query without having subqueries.


Re: Materialized view ......subquery expression not allowed error [message #301749 is a reply to message #301746] Thu, 21 February 2008 09:09 Go to previous message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It would be great if you posted create table and insert statements as we don't have to do it ourself.
If you also be great if you posted the code of your function and then we don't have to reinvent it.
Them ore time we spend to build the case in our environment the less we have to solve the problem.

Regards
Michel

[Edit: typo]

[Updated on: Fri, 23 November 2012 01:19]

Report message to a moderator

Previous Topic: ORA Errors
Next Topic: aborting DDL within trigger
Goto Forum:
  


Current Time: Mon Dec 05 09:18:11 CST 2016

Total time taken to generate the page: 0.09878 seconds