I have a requirement to add all the objects of schema A of database X to schema B of database Y. Schema A and B does not have any common objects.
Apart from tables,indexes schema A also have BLob object, materialize views, type. The size of database X is 50 GB. Through initial research I found data pump , traspoting tablespaces , and RMAN duplication .
Is the any other method I should be looking at? Is RMAN duplication even a option because I just want to copy a schema not the whole database? The data base has other schemas too.
I had to create a new column in a particular table now i want to insert the values in that column though the other columns are already populated I entered the command (insert into Product(STANDARD_PRICE) values(895.99) when i hit return it says cannot enter null value into (SYSTEM .PRODUCT. PRODUCT_ID) product_id is the PK which is the first column STANDARD_PRICE is the last column in my table...how do i enter the values into that column without receiving this error or having to effect the other columns?
select distinct c.process_id a.role_id,a.email_address,c.process_name from role a, notification_role b, process c where a.role_id=b.role_id and b.process_id = c.process_id
I am trying to merge into test table using a cursor. If the contract number in the test table matches with the contract number from the cursor then i need to update the creation_date else i need to insert values from the cursor into the test table.
MERGE INTO test USING cursortest ON (i.contract_number = test.contract_number) WHEN MATCHED THEN [code]......
My goal is to have a de-normalized summary table in which total communication volume for each distinct channel is displayed per customer.Briefly, I would like to reach the following output
When I run the sql below I am not able to de-normalize by customer id; counts are accurate.
SELECT distinct cil.customer_id, (CASE channel WHEN 'SMS' THEN (SELECT COUNT (channel) FROM customer_interaction_log cil1 where cil1.channel='SMS' and cil1.customer_id=cil.customer_id) END) SMS_COUNT, (CASE channel WHEN 'Email' THEN (SELECT COUNT (channel) FROM customer_interaction_log cil2 where cil2.channel='Email' and cil2.customer_id=cil.customer_id) END) EMAIL_COUNT FROM customer_interaction_log cil;
CREATE TABLE TEST11(TNO NUMBER(5), TVAL VARCHAR2(100), TID VARCHAR2(10)); INSERT INTO TEST11 VALUES(1,'VIJAYA','TEST'); INSERT INTO TEST11 VALUES(2,'VIJAYA','TEST'); INSERT INTO TEST11 VALUES(3,'VIJAYA','TEST'); INSERT INTO TEST11 VALUES(4,'VIJAYA',''); INSERT INTO TEST11 VALUES(5,'VIJAYA','');
[Code]....
My requiremen is if record is exists then i wan to update some value, if record not existes the i wan to insert new record
I want to write a simple stored procedure and I want to keep it as simple as possible (no loop, the least amount of parameters ...etc.)
Basically, the procedure receives a Table Of Record as input parameters and needs to merge it with existing table, the table of record is of the rowtype of the existing table.
I have difficulties to merge these data. Below is what I tried
CREATE or REPLACE PACKAGE BIZ_xxx_MERGE IS TYPE xxx_ACTIVITE_Type IS TABLE OF MyTbl%RowType INDEX BY BINARY_INTEGER; PROCEDURE MERGE_xxx_ACTIVITE_SP ( MyLP IN xxx_ACTIVITE_Type ); END BIZ_xxx_MERGE;
CREATE OR REPLACE PACKAGE BODY BIZ_xxx_MERGE AS PROCEDURE MERGE_xxx_ACTIVITE_SP ( MyLP IN xxx_ACTIVITE_Type ) AS BEGIN MERGE INTO MyTbl [code].........
I have two tables lets say TAB_A and TAB_B. I altered table B to include a new column from table A I wrote a merge statement as follows to merge the data
MERGE INTO TAB_AUSING TAB_BON (TAB_A.SECURITYPERSONKEY=TAB_B.SECURITYPERSONKEY)WHEN MATCHED THEN UPDATE SET TAB_A.PPYACCOUNT=TAB_B.PPYACCOUNT;
I know INSERT is for inserting new records UPDATE to my knowledge is to modify currently existing records (loosely) MERGE is one I rarely used, until this particular scenario. The code works perfectly fine, but I was wondering how could I write an update statement? Or in this scenario should I even be using an update statement?
I have two tables with 113M records in DWH_BILL_DET & 103M in prd_rerate_chg_que and Im running following merge query, which is running for 13 hrs to update records, which is quiet longer time.
SQL> explain plan for MERGE /*+ parallel (rq, 16) */ INTO DWH_BILL_DET rq USING (SELECT rated_que_rowid, detail_rerate_flag_code, rerate_sel_key,
I need to take the distinct values from VARRAY.. I have wrote following simple example. But it does not work. how to get the distinct value from VARRAY.
declare type t is varray(10) of varchar2(10); t1 t; type r is table of varchar2(10) index by binary_integer; r1 r; begin t1 := t('A','B','A','B','A','B','C'); select distinct * into r1 from table(select * from t1); END;
I have following query which gives currency code from two different tables. I would like to get the distinct count of currency codes from these two different columns.
SELECT eb.person_seq_id, eb.bonus_amount, eb.currency_cd, ed.currency_cd_host FROM fr_emp_bonuses eb, fr_emp_details ed, fr_periods p WHERE eb.person_seq_id = ed.person_seq_id AND ed.period_seq_id = eb.period_seq_id AND ed.period_seq_id = p.period_seq_id AND p.period_status = 'CURRENT' AND eb.bonus_amount >= 0 AND eb.person_seq_id = 3525125;
select field1,field2,field3 from Table1 union select field1,field2,field3 from table2
In the query from table2 i am getting duplicate rows, HOW can i retrieve only distinct rows...Using distinct keyword did not work...if i have to post create and insert statements for this one...
I need to select multiple columns but only have 2 of them which are distinct. For instance if i have
userid lastname firstname city country time 1 jones tom lon gb 2:25 2 wall paul la usa 2:30 1 jones tom lon gb 2:50 3 smith jane ny usa 2:55
what i would want to do is select all the columns but avoid duplicate lastname-firstname combination rows. The problem is if i use a group by i have to include all the columns and because time is different i will get tom jones twice. a way of getting round this so i can select all the columns but only 1 row of tom jones.
I have got the following error while executing below Query.
ORA-01791 'Not a SELECTed expression'
select distinct sgbstdn_levl_code from sgbstdn,spriden where spriden_pidm = sgbstdn_pidm and spriden_id = '200076543' order by sgbstdn_term_code_eff desc;
The above Query is not working with Distinct & Order By clause are present and by joining two tables. I need the distinct values of levels in Descending order of Terms.
The table creation and Data insertion script is attached with the message.Basically I want to sort all the data based on the order by clause and then remove duplicates from the TSKID column and get distinct TSKIDs in the same order.I have below query to sort data:
SELECT * FROM piwingetworkitems_vd ORDER BY profilepriority, authdptpriority, returnpriority ASC, priority DESC, effdate, tskid
But when I add a DISTINCT to the query, it does some kind of random sort and doesn't return the data as per above ORDER BY query and ignoring the SORT order.
SELECT DISTINCT tskid FROM (SELECT * FROM piwingetworkitems_vd ORDER BY profilepriority, authdptpriority, returnpriority ASC, priority DESC, effdate, tskid)
Is there any way to select the DISTINCT taskids ordered as per requirements?
I've read so many different pages on this topic but I can't seem to get my query the way it needs to be. Here's the query:
select admitnbr, lastname||', '||firstname||' '||finitial, hphone, mobile, wphone, med_rec, dob from patients join schedule using (key_patien) join adtmirro using (key_patien) where appt_state = 'ON HOLD'
Because patients in my database can have multiple appointments "on hold" there are duplicates in the results. I only need 1 record per patient in order to forward this information into an automated dialer to contact that patient. I do NOT want to call the patient over and over again. Once will suffice. I'm trying to make a distinction on the column 'med_rec'. One row per 'med_rec' will be awesome but I can't find a way to create a distinct on that column.