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.
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?
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?
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 .
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!
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.
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)
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]....
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,
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?
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.
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.
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.
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.
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.
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
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?
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
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,
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?
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,
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
