SQL & PL/SQL :: Merge Statement Required With Logic
Jul 10, 2013
Table Name: F_SCENARIO
System : Dataware house
Oracle version : 11g
Record Count : 2 Million records
Correct scenario records
F_Key F_Bridge_key Record_type
1 1 1
2 1 2
3 1 3
Wrong scenario records
F_Key F_Bridge_key Record_type
1 1 1
2 -5 2
3 -6 3
I want to write a Merge statement to update the negative values into 1.
View 6 Replies
ADVERTISEMENT
Sep 13, 2013
In the following merge statement in the USINg clause...I am using a select stament of one schema WEDB.But that same select statement should take data from 30 schemeas and then check the condition below condition
ON(source.DNO = target.DNO
AND source.BNO=target.BNO);
I thought that using UNIONALL for select statement of the schemas as below.
SELECT
DNO,
BNO,
c2,
c3,
c4,
c5,
c6,
c7
[code]....
View 5 Replies
View Related
Jun 22, 2012
how to update or insert another (third table ) table with merge statement
View 6 Replies
View Related
Jul 18, 2011
how to use the MERGE Statement. actually I've used oracle Merge Statement before and it works very well. However today I tried to use and perform a command like that:
Merge into myTable mt using ( select 'data' field1, 'data2' field2, ect from dual
union
select 'data' field1, 'data2' field2, ect from dual
union
[code]...
This has not worked.What am I doing wrong?What could I do to solve this problem and axecute this statement sucessfully?
View 3 Replies
View Related
May 9, 2011
in Toad I am using this Merge statement , working accordingly.I want to use this merge statement in my form where can I use it.
I created a Procedure in Form Named Proc_merge and call it when validate item of :bankcode
PROCEDURE proc_merge IS
BEGIN
declare
v_com varchar2(5000);
BEGIN
V_COM := '
[code]...
when matched then
update set
trg.v_chq = src.cheque_no, trg.v_bankcode = src.bank_code ,trg.v_debit=src.debit, trg.v_credit=src.credit, trg.v_narration=src.narration
when not matched then
insert (trg.v_type, trg.v_no, trg.v_date, trg.v_chq, trg.v_bankcode, trg.v_debit, trg.v_credit, trg.v_narration)
values (src.voucher_type, src.voucher_no, src.voucher_date, src.cheque_no, src.bank_code, src.debit, src.credit, src.narration)
';
FORMS_DDL(V_COM);
end;
END;
no any result.
View 2 Replies
View Related
Dec 12, 2012
I am using Merge statement to copy data from one table to other,
merge into tabA a
using tabB b
on a.id = b.id
when match
update (
)
when not matched
(insert )
This is working all fine, as SQL, but when there is large volume, it blows out as there are intermediate commits for this ?
View 3 Replies
View Related
Feb 7, 2013
I have two tables have almost the same columns, how can I use merge statement to update the target table only when there is difference between source and target table. Is there any easier way not compare each column one by one? I am using Oracle 11.2
Here is the MERGE statement:
Merge into tb_trgt trgt using tb_src src
on (src.id = trgt.id)
when not matched then insert (trgt.id, trgt.nm, trgt.addr) values (src.id, src.nm, src.nm)
when matched then update set trgt.nm = src.nm, trgt.addr = src.addr
where trgt.nm <> src.nm or trgt.addr <> src.addr
;
Is there any easier way to specify the where clause in the NOT MATCHED? I don't want compare each column of the tables. Since I may have many columns in the tables.
View 3 Replies
View Related
Oct 29, 2012
{code{
i want to apply merge stmt on single table.
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
View 3 Replies
View Related
Mar 14, 2013
I was reading about merge statement and tried some variations,
create table MERGE_TEST(
C1 number,
C2 varchar2(10 char),
c3 number);
insert into MERGE_TEST values(1, 'Name 3', 300);
insert into MERGE_TEST values(1, 'Name 2', 200);
insert into MERGE_TEST values(1, 'Name 1', 100);
commit;
[code]...
why is result different in this querys?
View 14 Replies
View Related
Apr 6, 2011
mbr has 60,000 rows and member has 60,000 rows approx. two tables have indexes on ssn, and citi_no on them.
PK of mbr : mbr_id
PK of member : mbr_id
other columns are not PK, and have no index on it.
I'm wondering why the statment doesn't use index while ssn and citi_no have index.
MERGE INTO mbr t
USING (SELECT mbr_id,citi_no
FROM member) a
ON (t.ssn = a.citi_no)
WHEN MATCHED THEN
UPDATE SET t.asis_mbr_id = a.mbr_id
where t.ssn not in(select ssn from mbr group by ssn having count(*) > 1)
View 19 Replies
View Related
Dec 2, 2010
I am getting a (PL/SQL: ORA-00903: invalid table name) compile error in a procedure using a merge statement. I have seen many examples using this technique and am at a loss as to why I can't compile.
the pipelined function is:
FUNCTION f_crcli_pipe(pi_source_data IN sys_refcursor,
pi_limit_size IN PLS_INTEGER DEFAULT pkg_crcli_variables.c_cursor_limit_def)
RETURN CRCLI_AA
PIPELINED
PARALLEL_ENABLE(PARTITION pi_source_data BY ANY)
IS
[code].....
the error is pointing to the TABLE function in the USING clause of the merge statement.
View 7 Replies
View Related
Aug 10, 2010
Can I have all the rows in a table updated at once in the merge statement?
MERGE INTO providermaster a
using
(
SELECT * FROM PROVIDERMASTER@INGEST) b
ON (b.MASTERPROVIDERID=a.MASTERPROVIDERID)
WHEN MATCHED THEN
UPDATE ....... I want to update all the other rows at once..
View 24 Replies
View Related
May 19, 2010
I am using MERGE statement first time in my application but I am facing error.
begin
merge into store.comm_dept a
using factory_data.comm_dept b
on (a.cd_dcode = b.cd_dcode)
when matched then
[code]........
ERROR IS: encountered the symbol 'INTO' when expecting on of the following:
:=.(@%;
View 7 Replies
View Related
Jun 26, 2013
I am writing below MERGE statement. In this cardinality between table_a and table_b is 1:2. I.e. each record in table_b corresponds to 2 records in table_a based on columns in ON clause.
Well this query throws below error.
----Error---
ORA-12801: error signaled in parallel query server P011
ORA-30926: unable to get a stable set of rows in the source tables
However, the same statement executes successfully when PARALLEL hint is removed altogether. (There are no duplicates in table_b based on unit,group,loc columns.)
-----Query--------
MERGE /*+ PARALLEL(8) */
INTO table_a a
USING table_b b
ON (a.unit = b.unit AND a.group = b.group AND a.loc = b.loc)
[Code]....
View 1 Replies
View Related
Feb 9, 2011
I have a merge statement in one procedure , this job runs on daily . It is running successfully since Jan-19-2010 and giving the below error now.
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcbgcur_9], [4224596], [1],
[4294967250], [2], [], [], []
It is giving error in the beginning of the MERGE statement.
View 11 Replies
View Related
Jul 19, 2012
How to handle the exception on below MERGE statement?
MERGE INTO COMM_EXSTS_COMIT_AGGR TARGET
USING
(
select * from ABC ) SRC
ON
(
SRC.COMMITMENT_ID = TARGET.COMMITMENT_ID
)
WHEN MATCHED THEN
UPDATE
WHEN NOT MATCHED THEN
INSERT ;
View 4 Replies
View Related
Sep 15, 2013
When I am executing merge statement I am getting the below error.
ORA-30926: unable to get a stable set of rows in the source tables
MERGE INTO TAN_LIST t
USING (SELECT * FROM (SELECT row_number () over (partition by TANNO order by null) rn,
dno,
TANNO,
SOL,
[Code]....
The query is fectching below data.
SELECT dno,TANNO,SOL,DESC,class,ct_dt,tcost
FROM MAT_LIST;
DNOTANNOSOLDESC CLASS CT_DT TCOST
63007565ADclass A A12345 08/28/131
[Code]...
Intially thers is no records in the TAN_LIST table. When I run the merge statemnt I am able to insert all 15 records into that table.
When I run the same merge statemment second time I am getting the below error.
ORA-30926: unable to get a stable set of rows in the source tables
So that I have used partition by cluase in the slect statement and I am able to resolve the error.
But it's inserting only 14 records not all 15. How to process all 15 records without the error..
View 16 Replies
View Related
May 24, 2013
I am using the below code to update specific sub-partition data using oracle merge statements.
I am getting the sub-partition name and passing this as a string to the sub-partition clause.
The Merge statement is failing stating that the specified sub-partition does not exist. But the sub-partition do exists for the table.
We are using Oracle 11gr2 database.
Below is the code which I am using to populate the data.
declare
ln_min_batchkey PLS_INTEGER;
ln_max_batchkey PLS_INTEGER;
lv_partition_name VARCHAR2 (32767);
lv_subpartition_name VARCHAR2 (32767);
begin
[code]....
View 2 Replies
View Related
Oct 31, 2011
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,
[code].....
View 39 Replies
View Related
Dec 28, 2010
my table have four cols such as
start_value trans_value result_value create_time
1 4 5 20101018 22:33:40
5 -2 3 20101018 22:33:40
3 3 6 20101018 22:33:40
6 -4 2 20101018 22:33:40
2 +3 5 20101018 22:33:40
5 9 14 20101018 22:33:40
but the data is not in the logic order they are confusing and there no origina sequence such as
start_value trans_value result_value create_time
1 4 5 20101018 22:33:40
3 3 6 20101018 22:33:40
5 -2 3 20101018 22:33:40
2 +3 5 20101018 22:33:40
5 9 14 20101018 22:33:40
6 -4 2 20101018 22:33:40
how to get they in logic order.now i have to create a combination in mem and use loop to get the logic order.
View 6 Replies
View Related
Aug 22, 2011
I have to implement a new logic such that , Old logic which pulls data will stay in place.now New logic pull data form SAP . So i have implemented the new logic in to the existing cursor as below.
i.e.
Cursor new_sap
old_query
Union
New_query;
I have 3 new cursor like dis. How to process these old and new cursors in FOR loop.. Ex:
For c1 in C2(order_number)
if order_source is sap then ( dis filters old data which is not in SAP)
new query in cursor should process
else
old query in cursor should process
end if;
How will i do this programatically.
View 1 Replies
View Related
Apr 28, 2012
i have three tables: ot_req, ot_po_breakup, and ot_po. when a row is inserted into the ot_po table reffering to ot_req,there is a trigger which creates a seperate reocrd in ot_po_breakup with the details of ot_req ot_req.ri_sys_id as pb_ri_sys_id and ot_req.ri_qty as pb_ri_qty ,pi_sys_id as pb_pi_sys_id .upto this part is okand when i insert also the logic is okay.
i have created a trigger to update the rows in the ot_po_breakup table after insert on ot_poin order of pb_ri_sys_id and pb_pi_sys_id and try to update the values in the columns pb_ves1q, pb_ves2q, and pb_ves3q order. i am trying to take the quantities in ot_po.pi_qty and insert them into the pb_ves1q, pb_ves2q, and pb_ves3q columns of ot_po_breakup where those columns are empty (0 or null) such that the sum of those three columns for that row does not exceed the pb_ri_qty in that row.
My problem is i need to mofify my trigger to do the update and delete , that is whenever the user is updating the column ofot_po.pi_qtythe qty should in ot_po_breakup should get updated accordingly and sum of pb_ves1q,pb_Ves2q and pb_ves3q should be equal to pb_ri_qty and do the same for delete as well
CREATE TABLE OT_REQ
(
RI_ITEM VARCHAR2(20 BYTE),
RI_SYS_ID NUMBER,
RI_QTY NUMBER
[code]...
View 14 Replies
View Related
Feb 19, 2013
what is the right syntax to use the below decode logic.
Decode(EXTRACT(MONTH FROM sysdate) > 2, ltrim_blanks(GLAMOUNTS.CR_AMOUNT_02) + ltrim_blanks(GLAMOUNTS.DB_AMOUNT_02), 0)
View 12 Replies
View Related
Mar 1, 2011
I need to run a query for students that took ALL the following courses: 6710, 6711, 1032, 1035 and ANY of the following courses: 3061,3065
here is my query:
=SELECT distinct student_number, last_name, first_name, gender, ethnicity, students.grade_level, lunchstatus, course_name, course_number ,termid
from students inner join storedgrades on students.id=storedgrades.studentid
where students.schoolid='0976111'
and (students.grade_level >'9' and students.grade_level <= '12')
and (course_number = all('6710', '6711', '1035')and course_number=any('1031','1032','3061','3065','3062', '3401', '3082'))
order by last_name, first_name, grade_level, course_number
The problem is that I'm not getting any results but I know for a fact that some students took the required courses. On a side note, if I change the ALL to ANY - I do get results - but it is not what I want because it would be hard to keep track of the students that met the requirements.
View 7 Replies
View Related
Oct 21, 2010
I have a BEGIN Block of the code where a loop is running .I also have a CONTINUE statement in the EXCEPTION Block of the code where I return the control to the BEGIN Block in case of any exception.Now as per ORACLE Company, CONTINUE has a old issue which is already registered by Oracle Company Bug 7306422.
The issue is where CONTINUE statement does not run properly when your code Optimization level is 2. However it will work properly if we have the Optimization Level as 0 and 1.are facing the same issue here and have a workaround.
Is there any code which I can permanently use instead of CONTINUE but have the same logic incorporated.i.e returning to the next iteration whenever I say to pass the control.
View 10 Replies
View Related
Jul 29, 2013
i am trying to upgrade my existing system (6i) to 11g. I have installed Weblogic 10.3.5 with forms and reports 11.1.2.1.0 on windows 7 32 bit. I can connect to my database (10g) from Form builder.
But when I try to open a form/pll of 10g or 6i, it says PDE-PLI018 could not find library and it shows the path of my earlier folder, which now does not exist.
I have made changes in registry FORMS_PATH, FORMS_BUILDER_PATH, UI_ICON And in default.env file FORMS_PATH and CLASSPATH
I have added entry to tnsnames.ora, through which I can connect to DB. And have followed below site to configure weblogic and FM forms and reports;
[URL]
1) Do we have to connect (create a bridge) to DB from weblogic except tnsnames.ora.
2) For library error I think I am missing some settings to be done.
3) If I am able to rectify above error then can I directly use [URL] and access the system from another pc, or still there are some modifications needed.
View 2 Replies
View Related
Oct 23, 2012
create table t (a varchar2(20),b number(8));
insert into t values ('aa',4);
insert into t values ('ba',6);
insert into t values ('ca',7);
insert into t values ('da',8);
in place of 8 there can be any number between 1 to 100
if in place of 8 number is <10
insert into t values('ea',10); ---- this i need dynamic insert
if in place of the number between 11-19 then
insert into t values('ea',20); ---- this i need dynamic insert
and so on
i tried as below
select case when max(b) <10 then 10 when max(b) between 10 and 20 then 20 end from t;
but i cant write case for again and again upto nth
View 3 Replies
View Related
Sep 5, 2012
I have oracle 10g R2 on windows.I have two tables and say table A ant Table B. Both have column Total_amount. There are primary key's on Both tables.
Sample query
SELECT A.EMPNO,A.EMP_CODE,nvl(A.TOTAL_AMOUNT,0) "A_TOTAL_AMOUNT",
nvl(B.TOTAL_AMOUNT,0) "B_TOTAL_AMOUNT",(nvl(A.TOTAL_AMOUNT,0) - nvl(B.TOTAL_AMOUNT,0)) "DIFFERENCE"
FROM A, B
WHERE A.EMPNO =B.EMPNO (+)
AND A.EMP_CODE = B.EMP_CODE (+)
AND round((nvl(A.TOTAL_AMOUNT,0) - nvl(B.TOTAL_AMOUNT,0)),3) <> 0Above query retrives only Non-matching rows.
I need to retrive :-
1)output of the query shown above and
2)Records which are present in B and not in A.
View 2 Replies
View Related
Feb 19, 2011
SQL> CREATE OR REPLACE TRIGGER TRI_COMPL_FEATURES
2 AFTER INSERT OR UPDATE ON COMPLEMENTS FOR EACH ROW
3 DECLARE
4 v_fno NUMBER;
5 v_tab VARCHAR2(30);
6 v_unique_id VARCHAR2(40);
[code]....
Trigger created.When I am trying to insert into complements table it is throwing error as follows:
SQL> insert into complements values(19,NULL,'5',6,7,NULL,'W2023648',NULL,NULL);
insert into complements values(19,NULL,'5',6,7,NULL,'W2023648',NULL,NULL)
*
ERROR at line 1:
ORA-20010: ORA-04091: table TEEMNGWS.COMPLEMENTS is mutating, trigger/function
may not see it
ORA-06512: at "TEEMNGWS.TRI_COMPL_FEATURES", line 19
ORA-04088: error during execution of trigger 'TEEMNGWS.TRI_COMPL_FEATURES'
I can understand that I am trying to perform DML operation(i.e select) on table which trigger is fired.But how can I implement the same logic when AFTER INSERT OR UPDATE trigger is written.
View 1 Replies
View Related
Feb 3, 2013
I want to retrieve the data which is "n" moths old.
To compare that I want use only SYSDATE without timestamp. I want to use standard timestamp '23:59:59' along with SYSDATE.
The condition should be as below.
UPDATE_DATE <= ADD_MONTHS(15/01/2013 23:59:59,-3)
UPDATE_DATE <= ADD_MOTHS(30/01/2013 23:59:59,-4)
UPDATE_DATE<=ADD_MONTHS(sysdate||' '||'23:59:59',-3);
View 6 Replies
View Related
