can we create table with copying of another table with some specific condition.
example.suppose we have one table which name is emp with three columns.
empid
empname
empjoindate
i want create a table emptemp by using emp table where empjoindate between two dates.
I need to create a table which contains the details of what companies a specific seeker applied to when he logs in into his account.... Also, when an employer logs in, he needs to get the list of seekers who applied to him...give database schema required for such a situation asap.Say, the user has a primary key UID, and each employer has a primary key EID..
View 1 Replies View RelatedI'd like to know if it is possible to track DML actions issued on a specific table by a specific user, for example , i tried :
AUDIT SELECT on SCOTT.DEPT by HR by ACCESS;
I get an error, where is my syntax error ?
i want to know if it's possible to do it without trigger ?
Let's say we have Table - A and we would like to replicate specific row transaction to Table B.
Here are the rows in *Table A*
Time: Lets say 15:00
A1 Just Updated @15:00
A2 Just inserted @15:01
A3
B1 - Daily Delete Row -i.e just deleted a while back - Non scheduled process --executed by application @15:02
B2 -
B3 - Daily Delete Row - i.e just deleted a while back -- Non Schduled process --executed by application @15:05
B4 - Just recently purged (As part of 180 Day purge ) - Scheduled process executed by operations team @15:10
B5 - Just recently purged (As part of 180 Day purge ) - Scheduled process executed by operations team @15:10
B6 -Just recently purged (As part of 180 Day purge ) - Scheduled process executed by operations team @15:10
Current Data in Table B (Before Replication) @15:00
A1 (without updates)
A3
B1
B2
B3
B4
B5
B6
Expected rows in Table B (via replication/snapshot/materialized view / or any other method)
*Replication at 15:30*
Table B - Read Only
Expected rows after replication-
A1 -- Newly updated details
A2 -- Newly inserted row
A3
B1 - Daily delete row is expected to be replicated
B2
B3 - Daily delete row is expected to be replicated
***Note row B4 is not expected to be replicated to table B.
Questions:
1) How can we get updates, inserts and daily deletes replicated while ignore large purges?
2) How can large purge changes be reflected in replicated tables as well without deleting daily deletes?
What is the difference between CREATE EXTERNAL TABLE Vs CREATE TABLE .?
Is CREATE EXTERNAL TABLE included in CREATE TABLE?
I am trying to execute dynamic SQL in Stored Function and I don't know how to do this.
Explanation:
In the function I am calling pr_createtab is procedure which will create a physical table and return the table name in the out variable v_tbl_nm.
I need to query on this dynamic table and return the result as return result. But i am not able to do it.
Here T_web_loylty_report_table is a type.
CREATE OR REPLACE function CDW_DSS.f_ReturnTable(i_mrkt_id in number, i_cmpgn_year in number)
return T_web_loylty_report_table is
v_tbl_nm varchar2(50);
i_cntry_cd varchar2(20);
v_sql_str varchar2(32567);
[code]......
We have a table in the client database that has two columns - column parent and column child. The whole hierarchy of DB table dependencies is held in this table.If Report 1 is dependent on Table A and Table A in turn is dependent on two tables Table M and Table N. Table N is dependent on table Z it will appear in the db table as,
Hierarchy Table
Parent Child
Report1Table A
Table ATable M
Table ATable N
Table NTable Z
Requirement :
From the above structure, we need to build a table which will hold the complete hierarchy by breaking it into multiple columns.The o/p should look like this
-ParentChild 1Child 2 Child 3
-Report1Table ATable M
-Report1Table ATable N Table Z
Child 1, Child 2, Child 3 ....and so on are columns.The number of tables and the no of hierarchical relationships are dynamic.
SQL Statements to create hierarchy table:
create table hierarchy (parent varchar2(20), child varchar2(20));
insert into hierarchy values ('Report1','Table A');
insert into hierarchy values ('Report1','Table B');
insert into hierarchy values ('Table A','Table M');
insert into hierarchy values ('Table B','Table N');
insert into hierarchy values ('Report2','Table P');
insert into hierarchy values ('Table M','Table X');
insert into hierarchy values ('Table N','Table Y');
insert into hierarchy values ('Report X','Table Z');
Approached already tried :
1) Using indentation : select lpad(' ',20*(level-1)) || to_char(child) P from hierarchy connect_by start with parent='Report1' connect by prior child=parent;
2)Using connect by path function :
select *
from (select parent,child,level,connect_by_isleaf as leaf, sys_connect_by_path(child,'/') as path
from hierarchy start with parent='Report1'
connect by prior child =parent) a where Leaf not in (0);
Both the approaches give the information but the hierarchy data appears in a single column.Ideally we would like data at each level to appear in a different column.
what command is used to create a table by copying the structure of another table including constraints ?
View 2 Replies View RelatedHow to identify the specific sequence name applied on a table.
View 4 Replies View RelatedIn details table i am having loc is column in which data is like this(Jones,180),(US,host name),(job, company),(id,0122)
output should be it should take only first value from ().
tell the syntax for setting pctfree and pct used for a specific table.
View 4 Replies View RelatedI want my query to return the rows of the table where a column contains a specific values first in a certain order, and then return the rest of the rows alphabetized.
For Example:
Country
ALBANIA
ARGENTINA
AUSTRALIA
....
CANADA
....
USA
....
Now i want USA and CANADA on top in that order and then other in alphabetized order.
While creating external table how can I specify a particular decode condition for a date field that comes in as '2099-99-99' i want to change it to '2099-01-0001', how i can translate it
I already have this in the access parameters..
Incoming_DATE CHAR(20) DATE_FORMAT DATE MASK "YYYY/MM/DD"
Now am on a need to take sum of data from one table but three different condition with three column.I give example Lets consider I have a table called Sales in which it has three columns
1.Dept
2.sales_amt
3.status
There are 3 different flag for status A,B & C.I want display data in the following format in five columns.
Dept Status_A Status_B Status_C
1 30 50 20
1 10 60 30
total 40 110 50
But data is stored in the table in rows like
1 30 A
1 50 B
1 20 C like all rows.
For this I wrote union operation and sum.Am
creating a view for this with there columns dept,Sales_A,Sales_B & Sales C for sum of data.My view is
select dept,Sum(sales_amt),0,0 from sales where status='A' group by dept union
select dept,0,sum(sales_amt),0 from sales where status='B' group by dept union
select dept,0,0,sum(sales_amt) from sales where status='C' group by dept
Am assigning 0 for other two status of data because i want the data to be displayed on different columns based on row level condition.The view will return data like
Dept Sales_A Sales_B Sales_C
1 30 0 0
1 10 0 0
1 0 50 0
1 0 60 0
1 0 0 20
1 0 0 30
So finally am using sum function again and selection total sum of value for each status from this view.So by using union operation how can i display data from different condition of data into seperate columns.
Our oracle database has been running great. This past wendesday the application was unable to access a specific table. Sutting down and restarting the database seemed to have fixed the issue. Last night the database has been working since that restart on wednesday morning. Suddenly stopped working. We stopped and restarted the database again this time getting ORA-00600 error as shown below. We are able to make a connection to the database and can even access the tables in question. The application is complaining about the connection to the database. I understand that ORA-00600 is an internal error what I need to know is what the internal error code 549 actually is. I have tried googling to no avail.
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Dec 3 09:47:20 2010
Copyright © 1982, 2002, Oracle Corporation. All rights reserved.
Connected.
ORA-00600: internal error code, arguments: [549], [], [], [], [], [], [], []
ORA-03113: end-of-file on communication channel
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
C:\Documents and Settings\tieoduser>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Dec 3 09:49:31 2010
Copyright © 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect sys/as sysdba
Enter password:
Connected to an idle instance.
SQL> startup
ORACLE instance started.
[code]....
There are 4 rows in table with stat_flag 'Updated Record' and stat_date with todays date.
stat date has date & time both, for that reason just trying to format with yyyy.mm.dd
I am getting zero rows as result.
where STAT_FLAG = 'Updated Record' and to_date(stat_date,'yyyy.mm.dd') = to_date(sysdate(),'yyyy.mm.dd')
I've created a query so I can easily compare two sets of data for two different instruments:
select a.CalId, a.AtName, a.NRef, a.VaLoat, a.ValTime, a.ValRing,
cvs.NRef, cvs.CalId, cvs.AtName, cvs.VaLoat, cvs.Valtime, cvs.ValRing
from CalcAttribute a, CalcAttribute cvs
where a.NRef like '438815' and cvs.NRef like '438813'
and a.CalId *= cvs.CalId
and a.AtName *= cvs.AtName
union
[Code]...
This works great - however I want to add an addtional condition, basically so it only returns where the two are not equal.
I thought I should just be able to add an extra:
and a.ValLoat *<> cvs.ValLoat
and a.ValLoat <>* cvs.ValLoat
But it doesnt seem to like this (Incorrect syntax near '<'.)
what are the type of ways to update the 10 million records table with certain condition?
View 2 Replies View RelatedI have a table structure and data as below.
create table production
(
IPC VARCHAR2(200),
PRODUCTIONDATE VARCHAR2(200) ,
QUANTITY VARCHAR2(2000),
PRODUCTIONCODE VARCHAR2(2000),
MOULDQUANTITY VARCHAR2(2000));
[Code].....
Now here i want to fetch data having condition as
PRODUCTIONDATE >= Monday of current week
so i would skip only first two rows and will have to get all rows.
I tried using below condition but it would give not give data for 2013 values.
to_number(to_char(to_date(PRODUCTIONDATE,'yyyymmdd'),'IW')) >= to_number(to_char(sysdate, 'IW'))
I want to create temp table, for this i am using:
CODEcreate global temporary table help_temp
as
select * from help;
but this is creating only the table structure, not copying the table data.
I have to create a table which contain history of a main table. like this:
if the main table is
========================
nametypelengthnot null
Avarchar5Y
Bvarchar5N
Cvarchar5N
Dvarchar5N
========================
[code]....
I've plan to so this by create a trigger in main_table. my problem is my main table have a lot of fields and I can't write a code to control it 1 by 1 like :
if old.A <> new.a
insert into history("A",old.A,new.a)
if old.B <> new.B
insert into history("B",old.b,new.b)
......
I decided to select column name from the data dictonary using this SQL:
SELECT column_name FROM user_tab_columns WHERE table_name = '<<Table Name>>';
and then do a loop over the resultset and use the column name I've got , like this (its just an idea, may be not a write syntax):
BEGIN
.....
FOR i IN 1..:result.COUNT LOOP
if ld.colname[i] <> :new.colname[i]
INSERT INTO history
VALUES ( colname[i], ld.colname[i], :new.colname[i]);
END LOOP;
END;
but I can't write a "old.colname". I try with " old.'colname' ", " ld.'colname' " but it won't work.how to create a history file like I've describe.
I have more than 100 records in CSV format. I have to import these records to a particular table which already contains data. I have to do a multiple update at a time, based on the condition . ie., if field1 is '1' then update field2 as 'A0001' and if field1 is '5' then update field2 as 'A0007' . The values are not in an order. Is it possible.
View 1 Replies View Relatedcan we delete multiples table through the single query?
suppose we have 2 table first one is emp and second is client
i want delete all data from emp and client through the single line query
i want to display records from a table in tabular form with one condition.
i made a tabular form.and in WHEN-NEW-FORM-INSTANCE i write
DECLARE
CURSOR hascr IS
SELECT
BILL_ID,
BILL_DATE,
MAT_CODE,
PLACE_FROM,
[code]....
when i run the form its not showing any errors but displaying only one record.that is the first record whose bill_type is send to other store.
am i using the right trigger or my select statement has problem?
I have 1M Records coming from an External Data source as a Flat File (using ETL). Now I need only Yesterday's data only to load in my Database Table.
this can be done using Bulk Load and Filter.
write the CODE.
Second Part:-
Hint: if I need to update only those records been updated Say the Address1 field is updated. So this records need to update in my Master Customer Table.
If I have many fields in table and any records that are modified (coming to me from External Datasource as a Flat file) how to identify and update that record in my Master Customer Table?
CREATE TABLE test1
(strt_num NUMBER ,
end_num NUMBER ,
des VARCHAR2(5),
CONSTRAINT pk_strt_num PRIMARY KEY (strt_num)
);
INSERT INTO test1 VALUES (5, 8, 'GC');
INSERT INTO test1 VALUES (10, 25, 'AB');
INSERT INTO test1 VALUES (12, 35, 'PC');
INSERT INTO test1 VALUES (22, 65, 'LJ');
SELECT * FROM test1
STRT_NUM END_NUM DES
-------------- ------------ -------------
5 8 GC
10 25 AB
12 35 PC
22 65 LJ
The requirement is the records should be split based on below conditions
1. Split only those records WHERE (end_num - strt_num) > 10
2. If TRUNC((end_num - strt_num)/10) = n, then n + 1 number of rows should be created for that record
3. While splitting the records,
-> For first record , START_NUM = Original STRT_NUM and END_NUM = START_NUM + 10
-> Second record , STRT_NUM = previous END_NUM + 1 and END_NUM = previous END_NUM + 10
And this should continue for all records except the last record
-> For last record, STRT_NUM = previous END_NUM + 1 AND END_NUM = Original END_NUM
This table has 5 million records. Only for 2000 records (end_num - strt_num) > 10.
Expected Output.
STRT_NUM END_NUM DES
------------- -------- ---------
5 8 GC -- No chage, END_NUM - STRT_NUM < 10
10 20 AB
21 25 AB
12 22 PC
23 32 PC
33 35 PC
22 32 LJ -- STRT_NUM = Original STRT_NUm, END_NUM = STRT_NUM + 10
33 42 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = previous END_NUM + 10
43 52 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = previous END_NUM + 10
53 62 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = previous END_NUM + 10
63 65 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = Original END_NUM
I have a table that partitioned into six partitions. each partitions placed in different table space and every two table space placed it on a different hardisk
when I will do query select with the non-partition keys condition, how the search process ? whether the sequence (scan sequentially from partition 1 to partition 6) or partition in a hardisk is accessed at the same time with other partition in other hardisk. ( in the image, partition 1,4 accessed at the same time with partition 2,5 and 3,6)
Can i alter the table to create partition on non partition table, i have tried and could not create it. Do we have some other means to do it as this is the live table and cannot drop them else will lose the data.
View 1 Replies View RelatedI have table test1(id,name) and table test2(id,,name)
Now when I update name column of a row on test1 I want the same value to be updated for the same id in test2.
So I wrote this trigger but its not working
create trigger test_trigger after update on test1 for each row
begin
update test2 set name=new.name where test2.id=id
end
/
I want to create a new table 'b' from table 'a'.I know the query will be " create table b as select * from a;" But there is some twist in my question. I do not know how many table are there in table 'a' all I know is that there is one column named "timestamp" in table 'a'.
Now I want to create table 'b' with the difference that the column "timestamp" should contain the value "sysdate" instead of the earlier values which were there in table 'a';
Note: I have to do this in single query. I can not first create table 'b' from 'a' and then update the values of column "timestamp" to sysdate.
