In my real view, the diff calculate column is very long (with case etc...) I must reuse many time the diff calculate column to calculate other columns.I do not want use subquery because there is many join to do in the same query. How can I reuse a calculate column in the same view?
i want to find out the last refresh elasped time for materialized view. i do not see last refresh elapsed time in data dictionary. i see only last refresh date in data dictionary.
how to find the last refresh elapsed time for materialized view.
My requirement is; I what create new table/view which contains all value of both table and new column called Total Amount. Total amount=SUM(Quantity*Unit_Price+Central_sales_Tax+Service_Tax+ValueAdd_Tax+Entry_Tax+Professional_Tax)
when trying to calculate the occupied space for a table, I'm using DBA_SEGMENTS, which works fine as long as the table does not have a BLOB column.
As far as I can tell, the size of the BLOB data is stored with the SEGMENT_TYPE = 'LOBSEGMENT', but I cannot find a view that tells me which DBA_SEGMENT row belongs to the BLOB column in the table I'm checking.
To give you an example:
select sum(BYTES) from DBA_SEGMENTS where owner = user and segment_name = 'MY_TABLE' group by SEGMENT_NAME
returns 262144
running:
SELECT sum(length(blob_column)) FROM my_table
returns 821333
There are entries in DBA_SEGMENTS for my user with the type LOBSEGMENT, but I cannot find a way to map the correct DBA_SEGMENTS row to the table I am checking.
How I can build a query with conditions and calculations? E.g. I've got this table
Start | End | Working Place | Mandatory ------------------------------------------------------------------------------------ 01-JAN-13 | 11-JAN-13 | Office | 1 14-JAN-13 | 25-JAN-13 | Home Office | 0 04-MRZ-13| 15-MRZ-13 | Office | 0 11-FEB-13 | 22-FEB-13 | Office | 1
Now if column working place=Office and column mandatory=0 the new column "price" has to calculate: (End-Start)* $25.00 and if working place=Office and column mandatory=1 the "price" column has to calculate: (End-Start)* $20.60 else $0.00
I tried it with the case statement but I didn't know how to calculate my values and display it to the virtual column "price".
Something like case when Working_Place = 'Office' and Mandatory=1 then ... else '0.00' end as PRICE ?????
I have the following situation. There are two selects in the which look like this
UPDATE TABLE_B B SET ( target
[Code]....
DB: Oracle 10i
MyFunction is relatively expensive and the second select works on a subset of the data considered in the first one (because of a.col2 > b.col3). For this reason I am looking for a way to do the job in only one update statement and compute MyFunction (a.x) only once per row.
Is there a way to define a SELECT clause once and reuse it in many other queries?
I have many procedures with same SELECT statement and I'm trying to find a way to not have to write out the SELECT clause in every function or procedure. Same question applies to FROM, WHERE, etc clauses.
I would like to reuse the icon button template for my button in a tabular form.
I tried to add <button type="button" class="uButton iconButton search"> Click </button> in column link text. I display this column like a Standard Report Column. The result it's not good.
How I can have the icon and button template like in my others buttons ?
I'm having trouble creating a view that has a not null column. Using this script you can see that the resulting table doesn't have a not null constraint for the first column even though both source columns for that row are not null. Is there anyway to force the view to mark that first column as not null? (I need it for ODP.NET otherwise I get an error there)
DROP TABLE MYTABLE; CREATE table MYTABLE ( COL1 NUMBER(2) NOT NULL, col2 number(2)) ; drop table mytable2; CREATE table MYTABLE2
My boss make a requirement in exist database as some user can view salary column at employment table by SQL and some user can view salary column at employment table by SQL.
The boss do not like to make changes front SQL. Ooracle 11g vault or Oracle Label Security is best for this requirement? my oS is 2008 32 bit window and DB is 11.2.0.1
basically this view has columns based on the previous tables column ( MEASURE_ID) values and the values will be corresponding value in column Percentage.
I have an issue in materialized view which has got one of the null able column and query on this column taking approximately 2 mins where as other indexed columns takes less than 10 sec.
Here is the summary
SQL> Select Count (1), Count (VAT_NO) From Mv_customer;
If an index is created on VAT_NO will that improve the performance. What kind of index can be created considering very less number of records has got VAT_NO
I need to create a materialized view with a clob column based on a varchar2 column of a table.This is because in the mv the clob column data gets appended one after another.
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?
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.
I am trying to calculate pay with a package which consists of four functions for calculations and a procedure that calls the functions to calculate net pay.
DML DDL and package
I the DML and DDL and the package as an attachment.
Problem
Errors below
32/9 PLS-00103: Encountered the symbol "E" when expecting one of the following:
, ; for group having intersect minus order start union where connect The symbol "having" was substituted for "E" to continue.
32/54 PLS-00103: Encountered the symbol ")" when expecting one of the following:
LINE/COL ERROR -------- ---------------------------------------------------------------- . ( * @ % & - + ; / at for mod remainder rem <an exponent (**)> and or group having intersect minus order start union where connect || multiset
33/9 PLS-00103: Encountered the symbol "INTO" when expecting one of the following:
. ( ) , * @ % & = - + < / > at in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or like like2 like4 likec between || member submultiset
How to calculate exact age for example my date of birth is 10-04-1972 and today current date is 21-10-2011 so i want to calculate age how many years, how many months and how many days.
I need a query to find out the working days. I have attached the sample script to create table and data. Here is the description of the tables.
Emp1 : To record the employe's information and weekly rest day. Attendance :- To record daily attendance. Leave_appovd : To record the approved leaves. Holiday : To record the holidays.
W = Attendance R = Weekly Rest L = Leave H = Holiday A = Absent
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).
Percentage will be calculated as rtons of code not having first digit 0 devided by itons having having code 0 result multiply 100 for the same date code wise e.g. For dated 17-jul-13 meal percentage will be calculated as round((104.304/193.155)*100,3)=54.000
How To Calculate Average in Forms 6i for example a summary column named (Amount = 5000) and i want to calculate 15% average of this amount i want to calculate it like summary column .
