SQL & PL/SQL :: Avoid ORA-38104 - Columns Referenced In The ON Clause Cannot Be Updated
May 9, 2012
this is my test data :
create table tab_1(c1 number,c2 number,c3 number);
create table tab_2(c1 number,c2 number,c3 number,c4 number,c5 number,c6 number);
insert into tab_1 values(1,1,1);
insert into tab_1 values(2,2,2);
insert into tab_2 values(1,1,1,3,3,3);
insert into tab_2 values(2,2,2,4,4,4);
The final result would be, update columns c1,c2,c3 from table tab_1 whith the values of columns c4,c5,c6 from tab_2 where tab_1(c1,c2,c3) exist in tab_2(c1,c2,c3).
My first aproach was :
merge into tab_1
using (select c1,c2,c3,c4,c5,c6 from tab_2) tab_2
on (tab_1.c1=tab_2.c1 and
tab_1.c2=tab_2.c2 and
tab_1.c3=tab_2.c3
)
[code]....
but gets error : ORA-01779
create table temp_table as
select tab1.c1,
tab1.c2,
tab1.c3,
tab2.c4 new_1,
[code]....
View 1 Replies
ADVERTISEMENT
Jun 4, 2013
avoid duplication of **where** clause in my query.
In my below query, **JOIN** condition is same for both the queries and **WHERE** condition also same except this clause "and code.code_name="transaction_1" In **IF ** condition only credit and debit is swapped on both queries, due to this **Credit and Debit** and this where clause "and code.code_name="transaction_1" I am duplicating the query. avoid this duplication. I am using oracle 11g
SELECT day AS business_date,
SUM(amount) AS AMOUNT,
type_amnt AS amount_type,
[Code]....
View 2 Replies
View Related
Apr 4, 2011
I am running a fairly busy Oracle 10gR2 DB, one of the tables has about 120 columns and this table receives on average 1500 insertions per second. The table is partitioned and the partitioning is based on the most important of the two timestamp columns. There are two timestamps, they hold different times.
Out of these 120 columns, about 15 need to be indexed. Out of the 15 two of them are timestamp, at least one of these two timestamp columns is always in the where clause the queries.
Now the challenge is, the queries we run can have any combination of the 13 other columns + one timestamp. In reality the queries never have more than 7 or 8 columns in the where clause but even if we had only 4 columns in the where clause we would still have the same problem.
So if I create one concatenated index for all these columns it will not be very efficient because after the 4th or 5th column the sorting would no longer be very useful and I believe the optimiser would simply not use the rest of the index. So queries that use the leading columns of the index in sequence work well, but if I need to query the 10th column the I have performance issues.
Now, if I create multiple single column indexes oracle will have to work a lot harder to maintain all these indexes and it will create performance issues (I have tried that). Besides, if I have multiple single column indexes the optimiser will do nested loops twice or three times and will hit only the first few columns of the where clause so I think it will kind of be the same as the long concatenated index.
What I am trying to do is exactly what the Bitmap index would do, it would be very good if I could use the AND condition that a Bitmap index uses. This way I could have N number of single column indexes which the optimiser could pick from and serve the query with exactly the ones it needs. But unfortunately using the Bitmap index here is not an option given the large amount of inserts that I get on this table.
I have been looking for alternatives, I have considered creating multiple shorter concatenated indexes but this still would not address the issue since many queries would still not be served properly and therefore would take a very long time to complete.
What I had in mind would be some sort of multidimensional index, I am not even sure if such thing exists. But essentially it would be some sort of index that could serve a query efficiently regardless of the fact that the where clause has the 1st, 3rd and last columns of the index.
So considering how widely used Oracle is and how many super large databases there are out there, this problem must be common.
View 12 Replies
View Related
Nov 29, 2012
is there any possibility or hint to avoid updating record if columns do not change? For example, I have UPDATE:
UPDATE MY_TABLE SET COLUMN_A = 'ABC' WHERE COLUMN_B = 12
if the value in COLUMN_A is 'ABC' I do not want to do this update. Of course I can add in WHERE 'AND COLUMN_A<>'ABC' but when I am updating a lot of columns it will be annoying
View 1 Replies
View Related
Aug 4, 2011
i want a query/function/procedure to check recently updated columns/tables in a database...
View -1 Replies
View Related
Jun 19, 2013
select * from nrc_trans_descr where type_id_nrc=60013 -- it has 18 columns and i have hard coded 60013 for simplification here.60013 is derived from 3 other table Output is ( it can have many rows too.typically for each type_id_nrc there is one row ).
TYPE_ID_NRC TRIGGER_STATUS INSTALLMENT_TYPE_ID_NRC
---------------------------------------------------------------------
60013 0 61013
i have to pass TYPE_ID_NRC and INSTALLMENT_TYPE_ID_NRC to restriction_id column in a different table. currently i am doing like this
select * FROM DISCOUNT_RESTRICTIONS WHERE discount_id in (12085,12086)
and (restricted_id in ( select type_id_nrc from nrc_trans_descr where type_id_nrc=60013)
or restricted_id in ( select installment_type_id_nrc from nrc_trans_descr where type_id_nrc=60013));
am using ORACLE 10GR2(solution for 11gr2 is welcome too)
View 16 Replies
View Related
Mar 18, 2011
I want to Suppress the Dummy columns from the UNION Clause of Query.
SELECT
'HEADER' AS record_type
,null C2
,null C3
,null C4
,null C5
FROM dual
UNION ALL
SELECT empid
[code]....
Now I need to remove the all the Dummy fields associated to "HEADER" and "TRAILER" records.
View 2 Replies
View Related
May 4, 2010
The below sql is giving different number of result sets while adding further columns in select clause.i.e After adding the columns 4,5,6 in the below query its giving different number of result set.In this case the result set count would be 5.
Before adding the columns 4,5,6,the result set count was 11.
SELECT PAYMENT_METHOD_MAP.NETTINGGROUP_ID,
PAYMENT_METHOD_MAP.CREDITPAYMENTMETHOD_CD,
PAYMENT_METHOD_MAP.DEBITPAYMENTMETHOD_CD,
PAYMENT_METHOD_MAP.AGENT_ID,
SETTLEMENT.NETTINGGROUP_ID,
SETTLEMENT.SETTLEMENTDATE
[code]....
View 8 Replies
View Related
Sep 26, 2007
I have a java class that I'd like to be able to call from PL/SQL. The class when executed by Java alone works fine. So I'm trying to do a loadjava to get it into the database as an object.
Testing
I run:
loadjava -user myuser/mypass -oci8 -resolve testClass.class
fails with :
errors : class testClass
ORA-29521: referenced name java/lang/CharSequence could not be found
The following operations failed
class testClass: resolution
exiting : Failures occurred during processing
Debug :The same loadjava command works with a simple hello.class that is below:
public class Hello {
public static void main(String[] args) {
System.out.println("hello world");
}
}
Quick thought The other class testClass.class has many imports, so I'm thinking thats the problem, such as:
import java.util.ArrayList;
import java.util.Collections;
import java.util.Enumeration;
import java.util.Hashtable;
import javax.naming.Context;
import javax.naming.NamingEnumeration;
import javax.naming.NamingException;
[code]....
So what do I need to do to use loadjava with a class that uses many imports?
View 1 Replies
View Related
Mar 3, 2011
I have a table with around 80 columns. All i need is to select first 40 columns.
Is there any way to select first 40 columns without giving all the 40 Column Names in select clause.
View 2 Replies
View Related
Oct 23, 2012
i what to update a column data type am geting this error
ORA-02267: column type incompatible with referenced column type
my query is
when i run
SELECT b.table_name||
' joins to the '||
a.table_name||
' table on '||
c.column_name||
' = '||
d.column_name Table_relationships
[code]....
View 9 Replies
View Related
Apr 19, 2011
We have Oracle 8i, 9i, 10g and 11g. I installed oracle client 11gR2 and when I tried connecting to the database, its erroring out saying "ORA-03134 connections to this server version are no longer supported". What client version should I install in order to avoid this error?
View 2 Replies
View Related
Feb 22, 2010
I have two tables where I have to find the record for Max value of the column sap_pkid for every sap_id as in given table create script. This script is giving correct value but looking for a better way so that when data increses it doesn't hit the performance.
way where max can be avoided or a more tuned query .
create table tab1 (sapid number,
denid number);
create table tab2 (sap_pkid number ,sapid number,
denid number,
[code]...
View 2 Replies
View Related
Feb 25, 2013
I have a query that performs the sum of a field and divides by the number of occurrences, got me a problem because if there are no occurrences would cause a divide by zero error in Query.
The solution I found might not have been the best, but at the time was possible.
I put a Case, when the sum of the number of occurrence is zero then return zero, otherwise performs the division of the sum of the values by the number of occurrences.
SELECT CLIENT_ID,
TO_NUMBER(TO_CHAR(DTPROC, ''YYYYMM'')),
DTPROC, EXCHANG_VAL,
[Code]....
Now this query is very slow, because have CASE,SUM and GROUP BY, so I need to optimize it.
do this validation (if the divisor is zero) more performance?
View 5 Replies
View Related
May 14, 2013
select T2.name,sum(T1.area)
from (select * from country) T1,T2i wrote very simple just to show you the problem, bu i think here we should add "group by".
but is there any way to avoid group by?because i don't want to group any thing!
oracle11g
View 4 Replies
View Related
Mar 5, 2013
I have created a procedure which sends mail to the users by using the smtp operation. I am collecting the email id of the users and then concatenating the same using the loop.
Everything is working fine, but if some person is coming multiple times in the loop, then his/her name is shown multiple times in the CC/BCC part (Although mail is sent once), it should be restricted to one time.
what i am doing is:
I have declared id_collector and it is concetenating the email as per the condition loop
........ some code here....
id_collector:=id_collector||email_id||';';
end loop;
Now, I want to check before concatenating that email already exists or not.
Example:
suppose value of id_collector= 'abc@gmail.com;pqr@gmail.com;rst@gmail.com;xyz@gmail.com;'
and again if email id comes - 'abc@gmail.com', then it should get rejected.
I think it can be done using reg_exp but I have messed up with that.
View 2 Replies
View Related
Aug 10, 2012
i am facing problem with date column.when i am trying to do execute a query this works fine
select null as link,'S'||to_char(DATE_OF_JOIN,'YY'),COUNT(STUDENT) "Blue Acres" from STUDENT_RECORD where PROCESSOR=1 and DATE_OF_JOIN >= to_date('0620' || to_char(sysdate, 'YYYY'), 'MMDDYYYY') and DATE_OF_JOIN < to_date('0920' || to_char(sysdate, 'YYYY'), 'MMDDYYYY') group by to_char(DATE_OF_JOIN,'YY')
and when i am trying the below query its showing the only this year dates its not referring the previous years
select null as link,'S'||to_char(DATE_OF_JOIN,'MMDDYY'),COUNT(STUDENT) "Blue Acres" from STUDENT_RECORD where PROCESSOR=1 and DATE_OF_JOIN >= to_date('1220' || to_char(sysdate, 'YYYY')-1, 'MMDDYYYY') and DATE_OF_JOIN < to_date('0320' || to_char(sysdate, 'YYYY'), 'MMDDYYYY') group by to_char(DATE_OF_JOIN,'MMDDYY')
when i am trying below query its showng the error ORA-01843: not a valid month
select null as link,'Fall'||to_char(to_char(DATE_OF_JOIN,'YY')-1),COUNT(STUDENT) "Blue Acres" from STUDENT_RECORD where PROCESSOR=1 and DATE_OF_JOIN >= to_date('0620' || to_char(sysdate, 'YYYY')-1, 'MMDDYYYY') and DATE_OF_JOIN < to_date('0920' || to_char(sysdate, 'YYYY')-1, 'MMDDYYYY') group by to_char(to_char(DATE_OF_JOIN,'YY')-1)
View 11 Replies
View Related
Jan 31, 2013
I'm creating oracle form to allow the user to register a new record in a table, I want to check the duplication in Four tables! which means, the user is able to save the record if it's NOT already registered in the other four tables.By checking P_ID (user parameter). this is my
Declare
v_count number;
begin
select count(*) into v_count ---Checking the 1st table
from 1_table
where p1_id=:p_id;
if (v_count>0) then
message ('Duplicate');
[code]....
View 4 Replies
View Related
Jul 23, 2013
I have a function that is being called three time using UNION and wanted to know if this can be improved to just one call while incorporating all the table joins.
select field1,fdate,fname,username,stepnum from (
SELECT M.FIELD1,
TO_CHAR (M.FIELD_DATE, 'MM/DD/YYYY HH24MISS') AS FDATE,
M.FIELDNAME AS FNAME,
M.USERNAME,
View 10 Replies
View Related
Oct 16, 2008
While I am inserting and updating the values in the oracle database its showing a message 'FRM-40401 - No changes to save.' I don't want this message to be shown. How can I do that?
View 5 Replies
View Related
May 16, 2011
Best solution to avoid mutating table error?
View 11 Replies
View Related
Aug 21, 2010
i have created form. one field is there "Payment_terms" at line level. for that i created LOV containing 3 fixed Payment terms. my requirement is that Avoid the user to select twice the same value from LOV.
View 8 Replies
View Related
Dec 5, 2012
In my database is present undesirable parallelization of the query. All involved tables has degree=1,
if I right understood there is no method to avoid this except reducing of degree of indexes ? I'm using 11.2.0.3
View 3 Replies
View Related
Oct 24, 2012
must generate a Cartesian join, but I do not know why it happens. dt.debtorkey, cl.clientkey, inv.invoicekey, ag.agingkey are primary keys of each table. The problem is: I got same tuple for 8 times.
select dt.debtorkey, cl.clientkey, inv.invoicekey, ag.agingkey, dt.DebtorNo, dt.Name as "debtor Name", dt.State, cl.ClientNo, cl.Name as "Client Name", inv.InvNo, inv.PurchOrd, inv.Amt,
to_char(inv.InvDate, 'MM-DD-YY') invoice_date, to_char(ag.DateLastBuy, 'MM-DD-YY') aging_lastbuy, to_char(ag.DateLastPmt, 'MM-DD-YY') aging_lastpmt
[code]...
View 14 Replies
View Related
Apr 23, 2010
can we use something like this
"select ... order by emp from emp"
what is to be done? so that this qurey runs. no co-related subquery to be used.
View 6 Replies
View Related
May 17, 2011
How can i avoid duplicate entry at entry level in form rather than when i pressed save button
View 2 Replies
View Related
May 20, 2011
Below query is getting delayed becasue of BitMap Indexes on the table. I am trying to avoid indexes by using Hints in the query but unable to do so, Details are as follows.
explain plan for
SELECT cbu_cid, cbu_cid_customer_en_nm,
COUNT (billg_acct_no) AS billg_acct_no,
SUM (subscriber_cnt) AS subscriber_cnt
FROM daily_view
WHERE (billg_system_id = 'TM' AND mktg_sub_segment_a_nm = 'TM')
AND (cbu_cid NOT IN ('0001988048', '0001379962', '0001350469'))
GROUP BY cbu_cid, cbu_cid_customer_en_nm
HAVING SUM (subscriber_cnt) > 10
ORDER BY subscriber_cnt DESC;
[code]....
I have tried with ALL_ROWS & PARALLEL.how to avoid above two indexes in a query.
View 28 Replies
View Related
Dec 21, 2009
I have one table in which I want to restrict some records from being inserted. I don't want to put any checked constraints. e.g. consider following table
transaction(
id number primary key,
txn_date timestamp(7),
payee varchar2(40),
amount number,
memo varchar2(40),
ref_num number
)
I want to write SQL which should not inset duplicate record.
e.g.
I have written one as bellow:
insert into transaction
select 1, to_date('2009-12-12','YYYY-MM-DD'), 'Payee1', 12, 'Test', 212 from dual where
(select count(*) from transaction where txn_date=to_date('2009-12-12','YYYY-MM-DD') and
payee='Payee1' and amount=12)=0;
Can I use exists/not exists, which query will be more appropriate. (Please consider that fields which I am using to filter out the duplicate transactions does not contain primary key.)
Can I write such SQL. Or do i check for duplicate rows one by one and then filter the duplicate records.
View 21 Replies
View Related
Feb 1, 2012
I have index on column column1 and because of the nvl, the optimizer is not picking up the index
How can I restructure the query to avoid nvl giving same result using index on column1?
now column1 and column2 are varchar2 fields here
where nvl(column1,'*')=(nvl(:param1,nvl(column2,'*'))
can I use case statement like
case when column>'' then .. stuckup here
View 7 Replies
View Related
Nov 8, 2011
how to avoid blocking locks.
View 27 Replies
View Related