ListAgg Query Vs View?

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


ADVERTISEMENT

SQL & PL/SQL :: Getting Distinct Values String Using LISTAGG?

Jun 3, 2013

CREATE TABLE TEST_TAB
(
A NUMBER(5),
B VARCHAR2(20)
) ;
INSERT INTO TEST_TAB VALUES ( 1, 'Manoj' ) ;
INSERT INTO TEST_TAB VALUES ( 1, 'Arun' ) ;
INSERT INTO TEST_TAB VALUES ( 1, 'Varun' ) ;
INSERT INTO TEST_TAB VALUES ( 1, 'Suresh' ) ;

[code].....

Query Output :

1Arun,Arun,Manoj,Manoj,Manoj,Suresh,Varun
2Kamlesh,Manoj,Manoj,Manoj,Suresh,Suresh

Expected Output :

1Arun,Manoj,Suresh,Varun
2Kamlesh,Manoj,Suresh

Expectation here is duplicate values should not be repeated.

View 1 Replies View Related

SQL & PL/SQL :: USING Listagg Function For More Than 8000 Char

Oct 28, 2011

using listagg function for more than 8000 char.I did the below sample SQL and in "e_orig" and "d_orig" for upto 4000 char it is working fine but I have to use it for more than 8000 char. and it is giving error,I checked the listagg function is having limitation of 4000 char.I tried but I am unable to achieve this.

select d.dname,d.loc,e.hiredate
,listagg(e.ename,',' ) within group (order by e.deptno) over (partition by e.deptno) as e_orig
,listagg(e.ename, ',') within group (order by e.sal) over (partition by e.deptno) as d_orig
from emp e, dept d
where e.deptno=d.deptno;
[code]....

View 17 Replies View Related

Listagg Function In 11g For Concatenating Values From Different Rows

Sep 11, 2013

I'm Trying to use Listagg function in oracle 11g for concatenating values from different rows,but i'm getting error as FROM KEYWORD NOT FOUND.

Query is:
select listagg(column_name,'') within group (order by column_name) "column_name"
from table_name;

View 2 Replies View Related

SQL & PL/SQL :: Create View From Dynamic Query (or Function Returning Query)

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

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 View Related

SQL & PL/SQL :: How To Implement Inner View Query

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

SQL & PL/SQL :: View Is A Recompiled Query

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

SQL & PL/SQL :: Query To Update Current Value In DBA View To New One

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

SQL & PL/SQL :: Query From The View Returning Zero Records

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

SQL & PL/SQL :: Correlated Query And Inline View

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

PL/SQL :: How To Use Materialized View And Its Data From A Query

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

SQL & PL/SQL :: Dynamic Query In View For Correct Column Name

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

Query Optimization On Join With A View On Huge Table?

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

SQL & PL/SQL :: How To View Rows In Table Format Using Select Query

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

SQL & PL/SQL :: How Query Call Embedded Materialized View Automatically

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

PL/SQL :: Refresh Fast Materialized View On Complex Query

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

PL/SQL :: Materialized View - Update Query Without Drop And Recreate

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

SQL & PL/SQL :: ORA-12015 / Cannot Create Fast Refresh Materialized View From Complex Query

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

Server Administration :: How To Change Existing Materialized View To Normal View

Jan 17, 2013

can we change the existing materialized view to normal view? if yes how?

View 2 Replies View Related

Creating Materialized View On Remote Simple View

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

SQL & PL/SQL :: Create Primary Key On View And Use This View For Creating Foreign Key?

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

SQL & PL/SQL :: Change View Definition When Select Is Running On That View

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

SQL & PL/SQL :: Oracle View Have Dynamic View Function?

Jul 28, 2010

Are oracle view have Dynamic view function?

View 8 Replies View Related

SQL & PL/SQL :: Materialized View - Table Or View Does Not Exist

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

SQL & PL/SQL :: Difference Between View And Materialized View

Apr 26, 2011

difference between view and materialized view?

View 1 Replies View Related

SQL & PL/SQL :: Difference Between View And Materialized View?

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

SQL & PL/SQL :: Create View In Another View?

Dec 1, 2011

Can we create view in another view

View 10 Replies View Related

SQL & PL/SQL :: Union Query Need To Add Extra Dummy Field To One Side Of Query

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

Reports & Discoverer :: Excluding SQL Query Data When Linked With XML Query?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved