The view data should get display as follows
vivaram varavu pattru
sambalam kamishan null 101.00
panam kamishan 51.00 null
panam sambalam 50.00 null
Logic:
Each table row will have only one value either in varavu_patti or in pattru_patti. On selecting the row, thogai must be posted in varavu when varavu_patti is not null or should be posted in pattru when pattru_patti is not posted.on selecting the table row, vivaram should contain all other rows varavu_patti and pattru_patti on equating chitta_enn
I have a table Product as; desc product Name Null Type -------------------------------------------------------------- PRODUCT_ID NOT NULL NUMBER INGREDIENT VARCHAR2(20)
The data in Ingredient is separated by ','. PRODUCT_ID INGREDIENT ---------------------- -------------------- 1 A,B,C 2 A,D 3 E,F
I need to write a sql statement which will retrieve a pair of product and ingredient in each row as;
PRODUCT_ID INGREDIENT ---------------------- -------------------- 1 A 1 B 1 B 2 A 2 D 3 E 3 F
I need to join ISSUED_REMOVED Table with ITL Table. having each quantity each row.
Eg. If a unit Serial no '354879019900009' has a part (1015268) issued 8 times and then unissued 4 times so finally the part was issued 4 times. so I need 4 rows to show for each qty 1 for that part and unit serial number.
create table ISSUED_REMOVED_ITEM (REPAIRED_ITEM_ID, ISSUED_REMOVED_ITEM_ID, ISSUED_PART_ID, OPER_ID, ISSUED_REMOVED_QUANTITY) as select 122013187, 1323938, 1015268, 308, 2 from dual union all select 122013187, 1323939, 1015269, 308, 2 from dual union all select 122013187, 1323940, 1015268, 308, 2 from dual union all select
[code]....
-- The way I need to join the Issued_Removed Table
select * from ITL_TEST ITL left join issued_removed_item iri on iri.REPAIRED_ITEM_ID = ITL.ITEM_ID --ITL.ITEM_ID --rlsn2.item_id --126357561 and iri.oper_id = 308 --in ( 308, 309)
I see that I can use SDO_LRS.SPLIT_GEOM_SEGMENT to split a line at a single point (and get 2 resulting lines).
However, how I could split a line, at multiple points, into multiple segments? I need to do this for many rows, therefore a function or procedure would be good if any exists.
I need to split a column into multiple columns. The data in my column is separated by a Comma (,). But the data is dynamic and I could have any number of data separated by (,).
Quote:FOR Ex: If COL1 contains CRITERIA_ITEM_TYPE_ID, CRITERIA_ITEM_TYPE, DESCRIPTION, ITEM_DATA_TYPE
RESULT: should be 4 columns contains the values CRITERIA_ITEM_TYPE_ID and CRITERIA_ITEM_TYPE and DESCRIPTION and ITEM_DATA_TYPE
Is COL1 contains CRITERIA_ITEM_TYPE_ID, CRITERIA_ITEM_TYPE, DESCRIPTION
RESULT: should be 3 columns contains the values CRITERIA_ITEM_TYPE_ID and CRITERIA_ITEM_TYPE and DESCRIPTION
I need to dump the contents of a very large table into text files for archiving as we retire this old DB. The table has about 16 million rows, and a few of the columns are up to 4000 characters wide (varchar2(40000)). I've got 2 problems:
1) How can I select records that occur in a certain month of a year (there is a date column) and put the selected records into a file?
2) I don't have access to the server OS, so UTL_FILE is not possible. The output is also so large that I'm having trouble with the DBMS_OUTPUT.PUT_LINE.
I'm trying to get the first block of the IF working first, so the rest is just placeholders.
DECLARE v_mm number (2); v_yyyy number (4); min_mm number (2); min_yyyy number (4); max_mm number (2); max_yyyy number (4); min_date date; [code]....
11.2.0.3...just trying to learn the syntax. I have not worked with IOTs and I am exploring a feature I have not really used to try to learn something new. I know about intervals.This exact split syntax below, works on a heap table without errors. When I run the following split against a regular heap table it works.
alter table MYTABLE split partition "FUTURE" at ( to_date('09_MAY_2013_13','DD_MON_YYYY_HH24' ) ) into ( partition "B4_09_MAY_2013_13", partition "FUTURE" ) update global indexes * ERROR at line 1: ORA-00932: inconsistent datatypes: expected BINARY got NUMBER
which contains multiple contact details for users of different types; type 1 is home, type 2 work etc. The following query returns the user's number and the latest home number for that user.
select user, details as latest_home_number from nc_test t where type = 1 and updated = (select max(updated) from nc_test t2 where t2.user = t.user and t2.type = t.type) order by t.user
However I am not very experienced with sql and I am not sure how to create a view which would contain the fields:
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).
I have a PL/SQL procedure which gathers data from multiple places as well as calculates some data. I want to store all this in a materialized view.So, I created an object type (I've shortened the definitions):
CREATE OR REPLACE TYPE mf_record_type AS OBJECT (identifier VARCHAR2(6), name VARCHAR2(100));
Then created the table type of the object:
CREATE OR REPLACE TYPE mf_table_type IS TABLE OF mf_record_type;
Then in the stored procedure defined a variable of the table type:
I'm stuck on the CREATE VIEW part and the SELECT statement to get the output of the CREATE VIEW. We were instructed not to use AND and simply use JOIN on this exercise.
Question - For employee John Smith, give the project(s) he is working on, his department's name and the name of his division. See crows foot diagram on a separate file.
I am attempting to select back multiple values for a specific key on one row. See the example below. I have been able to use the sys_connect_by_path to combine the fields into one field but I am unable to assign them to fields of their own. See the example below
TABLE DETAILS: Policy id plan name 111 A Plan 111 B Plan 111 Z Plan 112 A Plan 112 Z Plan
My desired result is to be able to show the output as follows
Policy ID Plan_1 Plan_2 Plan_3 111 A Plan B Plan Z PLan 112 A Plan Z PLan
Suppose, I am having Oracle BLOCK_SIZE of 16k and my Linux OS level BLOCK SIZE is 4k. then How 16k oracle block will store in OS level? and What will be the internal block splitting process?
I was wondering if there is an Oracle function available to split a string based on a delimiter character. For example, if I have a table consisting of:
Aim: Architecture change in existing application Domain: Health Care Background: There are 2 application ( Front end: one in oracle forms - deals with accounts module and another in some legacy application - deals with patient, clinical and diagnose module) using and sharing the same Oracle 9i database.
Patient related modules are moved into another database ( java as a front end, oracle 10g as backend ) which is normalized - eliminating duplicate tables and column, also its tables and columns names are not matching with existing (patient)system.
Now the requirement is making the existing application related only to Accounts module ( having complicated business logic written in packages ) to work as it is without changing the code, design drastically.
Questions:
1. Now how best this task can be completed without affecting existing Accounts system drastically ( with minimal changes )? 2. what are the possible best approach to achieve this ? 3. what are the best way for communicating the 2 DB in this scenario ( may be creating synonym, views etc ) ? 4. What are challenges that needs to be addressed ?
There is a requirement to make a table data in a database (eg: HR database) available in another database (eg: EMP database), instead of accessing it using database link. In EMP database(where data needs to be cloned), data will only be queried and no write operation will be done. Data in remote database (eg: HR DATABASE) will be occassionally fully truncated and reinserted. The plan is to do a similar truncate and reinsert of data (from HR database) into EMP database monthly once using dbms scheduler job. So basically data in just one table needs to be cloned in another database.
Question: For this situation, is a regular table or Materialized view the right choice to clone the table in EMP database and why? The table in HR database (remote database) is not very big.
My scenario is I need to insert into History table when a record is been updated into a tabular form(insert the updated record along with the additional columns Action_by,Action_type(Like Update or delete) and Action Date Into History table i.e History table contains all the records as the main table which is been visible in tabular form along with these additional columns ...Action_by,action_type and action_date.
So now i dont want to create a befor/after update trigger on base table rather i would like to create a generic procedure which will insert the updated record into history table taking the page alias and pade ID as the parameters(GENERIC procedure is nothing but whcih applies to all the tabular forms(Tables) contained int he application ).
We have to load 10 million rows in a table from another table based on the multiple joins. How much tablespace size we allocate to the table and for performance point of view how much should be the SGA size.
If you have one table called CUST_ACCOUNTS and a view called CUST_ACCOUNTS -- and then you want to select from the view and not the table.Is there a prefix for views or something that I can have in front of the name to specify it's the view I want data from?
CREATE MATERIALIZED VIEW MV_NESTED_DATA NOCACHE LOGGING NOCOMPRESS NOPARALLEL BUILD IMMEDIATE USING NO INDEX REFRESH COMPLETE ON DEMAND START WITH ROUND(SYSDATE) NEXT ROUND(SYSDATE) + 1 WITH ROWID AS select NESTED_TABLE_FIELD from MY_TABLE@Y_DB_LINK;
where NESTED_TABLE_FIELD is a nested table stored as T_NESTED_TABLE
And I get the error: ORA-12014: table 'T_NESTED_TABLE' does not contain a primary key constraint
Why should it if I try to create a MV with "WITH ROWID" refresh option and not "WITH PRIMARY KEY" one?
We are in the process of migrating our databases to a hosting provider (10g Sun -> 11g LINUX.) A (former) data architect at our business had this (nightmare) situation implemented:
- have materialized views created - after creation of mat views(and associated tables), add additional audit columns to the table that are populated by triggers, not by the MV. All data is important.
In order to get all of the data to the hosting provider, we data pump export the full schemas (which include the associated MV tables), have the provider DBA's import (all the materialized views failed on import, but the associated MV tables were created/populated), and I'm now attempting to fix the MV code to get them recreated. Over 100 MV's, most of which have these extra audit columns, and a number of the remote master/source tables do not have primary keys so using the by rowid option. An edited example (object names changed to protect the innocent) - using CAST to include the audit columns:
CREATE MATERIALIZED VIEW SCHEMA1.ACCOUNT ON PREBUILT TABLE USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 512K NEXT 512K PCTINCREASE 0 BUFFER_POOL DEFAULT) TABLESPACE S_TS_01_INDX REFRESH FAST ON DEMAND WITH ROWID USING DEFAULT LOCAL ROLLBACK SEGMENT DISABLE QUERY REWRITE AS SELECT ACCOUNT.GROUP_ID GROUP_ID ... ... cast(null as date) CREATE_PROCESS_DATE, cast(null as varchar2(20 CHAR)) CREATE_PROCESS_ID FROM SCHEMA2.ACCOUNT@REMINSTANCE.WORLD ACCOUNT /
ORA-12058: materialized view cannot use prebuilt table
IF remove "with rowid", get error that cannot create because no primary key on source table.
Online options seem to be (1) do not use prebuilt table (in which case we'd lose the additional audit data) or (2) add a primary key on the master table (we're not in a timeline to make & test changes to various production source tables.)
Other thoughts on how to get this data migrated/populated? This needs to be a lift and drop as much as possible - any type of rewrite/restructuring is out of the question.
there is a diff. problem for me.when i create table through inline view then it shows 2246 records but if i check these records only in select statement then it shows 124 records. i cant understand how table shows 2246 records even then atual records in inline view shows only 124 records.
following is a query
create table sam as select * from (( select distinct stck.item_code from ( select item_code,bal
