SQL & PL/SQL :: Query On View
May 25, 2010
I have created a table "temp" with one column "c_temp_1"
I have created a view "temp_vw" on table "temp".
When I queried the view, I got the table data i.e., column "c_temp_1".
Now, I added another column "c_temp_2" to table "temp"
When I querid the view, I did not got the column "c_temp_2".
But after recreated the view "temp_vw", I got the column "c_temp_2".
My knowledge is view will hold only select statement and will be executed when ever we call the view.
So, when I call the view after adding the second column, I should be able to see second column data as well but why I have to recreate the view?
-- Because of my PC issues, I was not able to paste SQL commands.
View 1 Replies
ADVERTISEMENT
Dec 5, 2012
I have a dynamic query stored in a function that returns a customized SQL statement depending on the environment it is running in. I would like to create a Materialized View that uses this dynamic query.
View 1 Replies
View Related
Jan 29, 2013
I've generally operated under the assumption that if I have a query that executes properly, I should be able to create a view from that same logic. However, I've had more than one occasion where this seems to NOT be the case.
Today, I have a query using ListAgg that operates just fine when run interactively, but (in SQL Developer v 3.0.04) it gives ORA-24344 when I try to create a view using the exact same statement.
View 9 Replies
View Related
Apr 19, 2011
SELECT
DISTINCT CUSTR.TRX_NUMBER trx_number,
CUSTR.TRX_DATE transaction_date,
(
SELECT
MAX(DECODE(fndcatusg.format,'H', st.short_text,NULL,st.short_text, NULL) )
FROM fnd_attachment_functions fndattfn,
[code]....
I have this above query i want to make this query like
SELECT
DISTINCT CUSTR.TRX_NUMBER trx_number,
CUSTR.TRX_DATE transaction_date,
CORRECTED_PROMISE_DATE
FROM
RA_CUSTOMER_TRX_ALL CUSTR
View 2 Replies
View Related
Mar 7, 2010
Quote:View is a precompiled sql query.What do you mean by the word precompiled?
Are views useful or beneficial for performanace,
View 7 Replies
View Related
Jul 18, 2012
I am looking to build a query to update a current value in a DBA view to a new one.i.e. updating directories based on the current value:
CREATE OR REPLACE DIRECTORY 'DIRECTORY_NAME' AS 'DIRECTORY_PATH'(substr(directory_path, 1,5) + '/&dbname' {i.e. this is different for every database name }+ 'DIRECTORY_PATH'(string after /xyz/)
WHERE DIRECTORY_NAME in
( select DIRECTORY_NAME
from DBA_DIRECTORIES
WHERE DIRECTORY_PATH
like '/xyz/%'
)
i.e. resulting output should be:
CREATE OR REPLACE DIRECTORY 'ABC' AS '/xyz/DBNAME/abc/def/';
(when the directory previously was 'xyz/abc/def/') i.e. basically inserting the db name into the directory.where DBNAME is a variable more directories are added frequently so therefore this needs to be a dynamic procedure to change the directories in the db.
View 26 Replies
View Related
Feb 27, 2012
Im running the following query in DB - Prod2
select *
from t_act_rwrd_reimb_stay a
where reimbursed_amt < 0
and invoice_date = '01-JAN-1900'
and not exists ( select 'x' from t_act_rwrd_reimb_stay b
where a.GPM_ID_STAY = b.GPM_ID_STAY
and reimbursed_amt > 0 )
The above table t_act_rwrd_reimb_stay --> is from a view on the database Prod2 and this view is pulling the data from the database prod1 via dblink
When I run the same query on Prod1 it returns 3 rows. Is there any reason this is not fetching data on prod2 view? or whatz wrong with the above query
or for example if I run the above query as follows with the dblink "@prod1" it returns the data properly
select *
from t_act_rwrd_reimb_stay@Prod1 a
where reimbursed_amt < 0
and invoice_date = '01-JAN-1900'
and not exists ( select 'x' from t_act_rwrd_reimb_stay@prod1 b
where a.GPM_ID_STAY = b.GPM_ID_STAY
and reimbursed_amt > 0 )
View 10 Replies
View Related
Apr 9, 2010
which one is better in performance point of view and why between inline view and correlated query.
View 5 Replies
View Related
Nov 15, 2012
I have got a simple materialized view question.
I have a view which is taking long time to get back results and this view is being used in various user queries. I noticed that the SQL of the view access some tables, data of them changes rarely. So it makes sense to retrieve that data in a materialized view and somehow re-use it.
My question is how can I use the materialized view and it's data from a query.
View 2 Replies
View Related
Feb 20, 2012
I don't know what column name would there for my new view. so can i use dynamic SQL in View so that I can get correct column name?
View 2 Replies
View Related
Jun 22, 2011
I have this table
create table ACTIONARI_ARH
(
actionar_id NUMBER(10) not null,
id VARCHAR2(20) not null,
id_2 VARCHAR2(20),
tip VARCHAR2(1),
nume VARCHAR2(100),
prenume VARCHAR2(100),
adresa VARCHAR2(200),
[code]....
and this view
CREATE OR REPLACE VIEW ACTIONARI AS
SELECT "ACTIONAR_ID","ID","ID_2","TIP","NUME","PRENUME","ADRESA","LOCALITATE","JUDET","TARA","CERT_DECES","DATA_REGISTRU" Data_operare,"USER_MODIF","DATA_MODIF","REZIDENT"
FROM (
select
[code]....
The table has about 30 milion records and holds persons names, addresses, personal id (id), and internal id(actionar_id) and date when a new adress has been added.
The view is about getting only the most recent info for one person (actionar_id).
if i run a
a) select * from actionari a where a.actionar_id = 'nnnnnnn', result is returned immediatly, oracle uses index and does not do a full table scan.
b) select * from actionari a where a.actionar_id in ('nnnnnnn','mmmmmm','ooooooo'), result is returned immediatly, oracle uses index and does not do a full table scan.
my problem when i use this view in a join.let's assume i have another table with no more than 500 records, something like
create table SMALL_TABLE
(
actionar_id NUMBER(10) not null,
......
);
and if i run
select *
from SMALL_TABLE s
join actionari a
on a.actionar_id = s.actionar_id;
it takes like forever to process, forever means 1~3 minutes.by looking at the execution plan, oracle does a full table scan, creates the view for all unique 7milion persons, and only then joins the result with the actionar_is's in the small table and returns the desired 500 record result.i am using oracle 10g.
View 2 Replies
View Related
May 28, 2012
I need to view the rows of the result of a select query in table format in GUI application.I have used XMLELEMENT to somewhat match the scenario to display as ','(comma) separate values as b belwo
SELECT RTRIM (XMLAGG (XMLELEMENT (e, EMPNO || ',')).EXTRACT ('//text()'),
',')
AS empid,
RTRIM (XMLAGG (XMLELEMENT (e, ENAME || ',')).EXTRACT ('//text()'),
[code]...
But the case is I need to display the value in table format Horizontally as below
EMPIDemployee nameDEPID
778
278CLARK10
397MILLER
934KING
[code]...
View 14 Replies
View Related
Jul 13, 2010
I have to optimize the queries for user online panel. We have already created 2 materialized views:
mv_channel, mv_area
mv_channel (article_id, area, aweg_txt3, sum(value), sum(units))
mv_area (article_id, area, sum(value), sum(units))
For this query:
SELECT /*+ REWRITE*/
sum(value) as value,
sum(units) as unit,
article_id
from
...
where
...
and area in ('RB','RR','RS')
and aweg_txt3 n ('SB')
...
is the matview mv_channel very useful. The execution time was just 8s-13s instead fo 190s. But if the user also asks for this query but without AWEG_TXT3 in the where-clause, then it lasted 1:25min (longer than if the matview mv_area will be used).
The problem is that, the matview mv_area cannot be used in the case, in which the AWEG_TXT3 is in where clause (because it doesn't have aweg_txt3).
How can i do, so that :
- in case of AWEG_TXT3 in where-clause the matview mv_channel will be used and
- in another one (without AWEG_TXT3 in where-clause) the matview mv_area will be called automatically from the query
View 1 Replies
View Related
Sep 26, 2013
create materialized view ino_crm.MV_IDENTIFICATION_DETAILS REFRESH FAST as SELECT billing_account_id, i.s1 KEYS_ , COALESCE(i.s2, i.b, i.d) VALUES_ FROM ba_personal_details pd , xmltable ( '//entry[string = "identification Details"]//entry' passing xmltype(pd.details) columns s1 VARCHAR2 (255) path 'string[1]' ,s2 VARCHAR2(255) path 'string[2]' ,b VARCHAR2(255) path 'boolean' ,d VARCHAR2(255) path 'date' ) i WHERE pd.mandator_key = 'VSO01' AND pd.valid_to = to_date('01.01.4000', 'DD.MM.YYYY'); and
I get an exception like: SQL Error: ORA-12015: cannot create a fast refresh materialized view from a complex query 12015. 00000 - "cannot create a fast refresh materialized view from a complex query"
*Cause: Neither ROWIDs and nor primary key constraints are supported for complex queries.
*Action: Reissue the command with the REFRESH FORCE or REFRESH COMPLETE option or create a simple materialized view.
I have found this URL....
View 2 Replies
View Related
Aug 24, 2012
i have a little doubt in Materialized view. i created a materialized view and log with the following query,
create table test_mv (a number ,b number )
alter table test_mv add constraint t_pk primary key ( a );
insert into test_mv values (1,2);
[Code]...
Now i want to update the query in the MV as 'Select a from test_mv' . for that i tried with
*'ALTER MATERIALIZED VIEW product_sales_mv AS SELECT a from test_mv;'*
But it throwing error,
Error starting at line 5 in command:
alter MATERIALIZED VIEW product_sales_mv AS SELECT b from test_mv
Error report:
SQL Error: ORA-00933: SQL command not properly ended +00933. 00000 - "SQL command not properly ended"+
*Cause:+
*Action:+
i guess i am doing wrong. i want to update it without drop and recreate
View 2 Replies
View Related
Apr 27, 2011
I'm trying to create materialized view, successfully create logs for all the tables involved.
CREATE MATERIALIZED VIEW LOG ON tbJournal
WITH ROWID, SEQUENCE (tabid, companyid, storelocid, tabstatus, linetype)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON TBTABS
WITH ROWID, SEQUENCE (TSTAMP, COMPANYID, NUMGUESTS, POSITIONID, STORELOCID, TABSSTATUS)
INCLUDING NEW VALUES;
[code]....
The result is ora-12015: cannot create a fast refresh materialized view from a complex query
It does work fine if I remove
AND EXISTS (SELECT 1 FROM tbJournal J
WHERE j.tabstatus NOT BETWEEN -6 AND -4
AND j.linetype = 1
AND T.tabid = J.tabid
AND T.companyid = J.companyid
AND T.storelocid = J.storelocid)
from the where clause
View 10 Replies
View Related
Jan 17, 2013
can we change the existing materialized view to normal view? if yes how?
View 2 Replies
View Related
Dec 11, 2012
I'm trying to create a Materialized View on a remote database from a simple view. The reason is, the data owners don't want to grant explicit tables privileges to external subscribers.
A new schema is created to publish data in the form of a view. I've created mlogs on the master tables, and granted them to the subscriber, but it's still complaining about a missing primary key on the view. A primary key does exist in the master table.
Is there another work around for this situation without having to work inside the data sources' environment?
View 5 Replies
View Related
Oct 8, 2010
is it possible to create primary key on view and use this view for creating foreign key .
View 3 Replies
View Related
Apr 25, 2012
We wrote one data load process to load GZ files into Database.during process we will change client facing view definitions to backup table so that we can work on base tables.
This view definition changes are related to FROM and WHERE clause (not columns/type). during load process, client/user may connect to current server and accessing these views. My question is what will be the reflection of changing view definition while user is accessing view?
I created a scenario-
STEP1: Created a view-
create or replace view view_01 as
select object_name from dba_objects union all
select object_name from dba_objects union all
select object_name from dba_objects union all
[code]....
View definition is replaced by new definition while select is executing on that view. select returned number of records as per view definition one.
View 6 Replies
View Related
Jul 28, 2010
Are oracle view have Dynamic view function?
View 8 Replies
View Related
May 2, 2012
I have a materialized view "pro_mview",I am trying to refresh the MVIEW by using the following statement.
EXEC DBMS_MVIEW.REFRESH('pro_mview','C');
But I am getting the below error.
*
Error at line 1:
ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2256
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2462
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2431
ORA-06512: at line 1
I am able to fetch the data from that materialized view(pro_mview).
View 3 Replies
View Related
Apr 26, 2011
difference between view and materialized view?
View 1 Replies
View Related
Aug 12, 2013
what would be the difference between a view and a materialized view? whether DML possible on a view? i think error occurs if DML tried on a view which is a combination of two or more tables, whether DML possible on a materialized view?
View 7 Replies
View Related
Dec 1, 2011
Can we create view in another view
View 10 Replies
View Related
Dec 8, 2005
I have inherited a query that union alls 2 select statements, I added a further field to one of the select statements ( a date field). However I need to add another dummy field to the 2nd select statement so the union query marries up I have tried to do this by simply adding a
select
'date_on'
to add a field called date on populated by 'date_on' (the name of the column in the first query)
however when I run the union query i get the error Ora-01790 expression must have same datatype as corresponding expression.
View 6 Replies
View Related
Apr 26, 2013
I have data in a table and another in XML file,I used SQL query to retrive the data placed on the table, and link this query with XML query that retrieves the data stored in the xml file. The data stored in the table and xml file sharing a key field, but the xml contents are less than what in the table.I want to show only the data shared between the two queries, how can I do that?
e.g.:
Table emp:
e_id | e_name | e_sal
023 | John | 6000
143 | Tom | 9000
876 | Chi | 4000
987 | Alen | 7800
XML File
<e_id>
143
876
So, I want the output to be:
e_id | e_name | e_sal | e_fee
143 | Tom | 9000 | 300
876 | Chi | 4000 | 100
View 2 Replies
View Related
Aug 7, 2009
I am looking to simplify the below query,
DELETE FROM A WHERE A1 IN (SELECT ID FROM B WHERE BID=0) OR A2 IN (SELECT ID FROM B WHERE BID=0)
Since both the inner queries are same,I want to extract out to a local variable and then use it.
Say,
Array var = SELECT ID FROM B WHERE BID=0;
And then ,
DELETE FROM A WHERE A1 IN (var) OR A2 IN (var)
How to do this using SQLPLUS?
View 8 Replies
View Related
Jun 19, 2012
I have the following four tables with the following structures Table A
ColA1 ColA2 ColA3 ColA4 ColA5 AA 100 CC DD EE
Table B
ColB1 ColB2 ColB3 ColB4 ColB5 AA 100 40452 A9 CDE
when these two tables were joined like the following:
Select colA1,ColA2, ColA3, ColA4, ColB3,ColB4, ColB5 from table A Left outer join (select ColB3, ColB4, ColB5 from table B where colB3 = (select max(colB3) from table B ) on (colA1 = colB1 and ColA2 = col B2)
Now i have to join the next table C with table B
Table C structure is
ColD1 ColD2 ColD3 Desc1 A9 Executive Desc1 A7 Engineer
I have the common column such as ColD2 and colB4 to get the Col D3
how do i join the existing query + join between table b and table c?
View 4 Replies
View Related
Jul 17, 2011
how to achieve F11(Query mode) and Execute Query in Oracle Forms?
View 1 Replies
View Related
