Updating the status from view

posted by | Leave a comment

I searched some stuff and it seems that UNION ALL doesn't become an updateable view but going my your thougts in these forums the INSTEAD of triggers can do the MAGIC. We are not considering to range partition since there will be too many partitions (more than 5 yrs of data ~1800 partitions)to hold and local indexes at that level doesn't give that optimal performance .... But, if there is no record for a particular select query, the spool file only contains "no rows selected". Can we get the column names under which "no rows selected" message??? Thanks and Regards, William Sorry, The creates are: CREATE TABLE DCF_FUND ( "CODE" VARCHAR2(10), "NAME" VARCHAR2(30), CONSTRAINT "DCF_FUNDS_PK" PRIMARY KEY ("CODE"), CONSTRAINT "DCF_FUNDS_UK1" UNIQUE ("NAME") ) ; CREATE TABLE DCF_AB_STOCK_FUND_POSITION ( "CODE" VARCHAR2(10), "NAME" VARCHAR2(20), "POSITION" NUMBER, CONSTRAINT "DCF_AB_STOCK_FUND_POSITIO_PK" PRIMARY KEY ("CODE") ) ; May 10, 2012 - am UTC In the first query - you have (select 1 from dual where dummy = x) and X is an attribute from the outer query and that attribute comes from the with view and is "select 'X' from dual" Now, select 'X' from dual might be 'X' or it might be NULL - we don't know until runtime. And that is causing the database to just say "cannot update this" In the second query, the scalar subquery isn't reliant on anything - it stands alone, it doesn't need the with subquery at all.

So we are considering T to be a regular heap table and regular B*tree indexes and consider T_TMP as a range based partition table where in the temp records will be dropped after the workflow gets finished, and we have defined the retention to be a max of 10 days for a workflow to get finished. Normally, whenver we use spool file for listing the selected records, that will contain column name and corresponding data. is there a really inefficient way to write a query to make sure it returns at least one row? If you need this sort of control over the output format, I would suggest, no - I would actually state "you need to use a report writing tool or write a program" sqlplus is a simple, very very very simple command line interface for ad-hoc queries. Hi, Tom, I've a view as: CREATE OR REPLACE VIEW test AS WITH v1 AS (SELECT f.code, f.name, pcode, pname, (SELECT 'X' FROM dual ) x FROM dcf_fund f, dcf_ab_stock_fund_position p WHERE f.code=) SELECT (SELECT 1 FROM dual WHERE dummy=x ) v1, v1.* FROM v1 and it's not updatable: select column_name, updatable from dba_updatable_columns where table_name='TEST'; COLUMN_NAME UPDATABLE ------------------------------ --------- V1 NO CODE NO NAME NO PCODE NO PNAME NO START_DATE NO But, if I: CREATE OR REPLACE VIEW test AS WITH v1 AS (SELECT f.code, f.name, pcode, pname, (SELECT 'X' FROM dual ) x FROM dcf_fund f, dcf_ab_stock_fund_position p WHERE f.code=) SELECT (SELECT 1 FROM dual WHERE dummy='X' ) v1, v1.* FROM v1 It's updatable: COLUMN_NAME UPDATABLE ------------------------------ --------- V1 NO CODE YES NAME YES PCODE YES PNAME YES X NO And if I: CREATE OR REPLACE VIEW test AS WITH v1 AS (SELECT f.code, f.name, pcode, pname, 'X' x FROM dcf_fund f, dcf_ab_stock_fund_position p WHERE f.code=) SELECT (SELECT 1 FROM dual WHERE dummy=x ) v1, v1.* FROM v1 It's also updatable: COLUMN_NAME UPDATABLE ------------------------------ --------- V1 NO CODE YES NAME YES PCODE YES PNAME YES X NO Why?

updating the status from view-85updating the status from view-72updating the status from view-73

I have Never used INSTEAD Of trigger and hope you can illustriate some example on the above case to take it forward. For example: A table contain 2 columns and i am running select query for a particular condn. The third query is the same as the second for that fact.

I'm going to learn more about workspace manager and test it.

We already do this (and more) Tom, It's optimistic that we choose the right way to maintain inherited history model (views and "instead of" triggers) in our solution - the same as Oracle in version-enable tables.

COM select column_name, UPDATABLE, 2 insertable,deletable 3 from user_updatable_columns 4 where table_name = 'V_MYVIEW' 5 / COLUMN_NAME UPD INS DEL ------------------------------ --- --- --- CNY# NO NO NO RECORD# NO NO NO STATUS NO NO NO TIMEUNIT NO NO NO that is what you are seeing now... In my case, whatever the variable from "select 'X' from dual" is null or not, the each row from DCF_FUND and DCF_AB_STOCK_FUND_POSITION will be returned at most once in my test view.

And that each row in a table will be returned at most once in the result view will be said as a key preserved table and thus can be updated from the result view.

Leave a Reply

Meet and sex chat manila chatroom