SQL & PL/SQL :: Avoid Divisions By Zero?
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
ADVERTISEMENT
Mar 2, 2011
Presently have one instance, one Schema on my DB Server. Want to split the existing schema into 3 logical divisions. Have created the 3 Schemas. Through Oracle Export from the old Schema am able to Export (using wild cards) the tables and indices for each grouping and am able to Import into the new specific schema. The problem i'm having is that i cannot seem to Export the specific sequences due for each of the new schemas. Is there an easy way to accomplish this?
View 6 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
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
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
Oct 17, 2013
am trying to add a validation in Oracle forms using Oracle DB.in a Oracle table consider there is a record named 'Netbook' then if I try to create another record with name 'Netbooks', then a Oracle warning message should be displayed stating similar name 'Netbook' is already available. same way, if NETBOOKS is already available in table and if user try to create another record NETBOOK then same warning message netbooks is already available should be shown.
View 17 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
Oct 21, 2010
I am working with oracle 10g / 11. I need to operate with CLOB values of some table, unknown for me: I read them from .Net (3.5), and have to save them to files (Windows) to be loaded after this with DBMS_LOB. Lets say that table contains 3 columns (1 - CLOB) and several rows.
I want to store each CLOB value to separate file, and to store it after this into another DB table (another Instance, also). I cannot use dblinks and other techniques like this. The point is - how to avoid dealing with encoding?
I mean, that in the best scenario, I just want to save the CLOB values to the files, with no meter about Database Instance character set, language, and so on. How this can be accomplished? If I use default for .Net StreamReader UTF-8 format, the loading with DBMS_LOB failed.
If this is not possible, what will be the best way to determine the encoding, and to convert Oracle used encoding to .Net one?
View 5 Replies
View Related
Jun 28, 2013
Our client requirement is to avoid table data to physical standby database.
View 3 Replies
View Related
May 26, 2010
I have granted connect,resource privilege to a user 'TestU' and have create several synonyms in the schema using the public database link. What worries me is that the user 'TestU' can view the all_ and users_ dictionary views, which makes him accessible to all public database links and all user specific objects definition.
Is there a way to restrict the user to see only the synonyms and avoid looking at their definitions
View 6 Replies
View Related
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
Jul 20, 2010
As data completeness, data consistency, data integrity are some of features of good quality data. What is best approach and best practice to deal with missing or null values in database? I am facing such type of situation. In a main table, there are several columns have null or missing values.
Get rid of null or missing values. Should I consult source documents from where data was fed into database to achieve maximum data completeness?
View 14 Replies
View Related
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
View Related
Jul 23, 2013
is there any way to refer a table which is in different schema other than using DB links.
View 5 Replies
View Related
Jul 27, 2012
I have a query which is using literals
strquery:='SELECT SUMTOTAL FROM tab1 WHERE BATCHNO = '''
|| gBNo
|| ''' AND A_ID = '''
|| g_id
|| ''' AND L_ID = '''
|| g_LId
|| ''' AND S_Code = ''C_3'' ';
execute immediate strquery; I have been asked to use a bind variable to avoid hard parsing.How do i do it?
View 2 Replies
View Related
Oct 17, 2011
My SQLPULS script below generates 2 million records and it works fine without any errors, which I run from my batch file. (sqlplus -S %CONNECT% @"SCRIPTSmysql.sql"
The script itself runs 2 hours and exits via sqlplus.exe properly without any errors based on errorlevel from my batch and proper generated data. However, I have noticed that spool output file stops generating any records after 1 hour. It appears that client sqlplus.exe is sleeping or verifying something after spooling completed exactly the same period of time that takes to generate my out file. Is there a way to configure my script to avoid this 1 hour sleep/verification process?
whenever sqlerror exit sql.sqlcode
alter session set current_schema=blah;
/
set linesize 1000
set feedback off
[Code]....
View 3 Replies
View Related
