SQL & PL/SQL :: Divide One Of Column Value With Other As Duplicate
Feb 6, 2012
below are the create and insert statement, making duplicate row, i mean In table emp_detail, we have row like
ENAMEJOBDEPTNODETAIL
RAJ CS 10 RAJ IS IN CARE OF ROHIT
We need to insert the same record but divide the last column value on the basis of "IN CARE OF" any word after this should come in next new row with the same value for all other column, like
ENAMEJOBDEPTNODETAIL
RAJCS10RAJ IS IN CARE OF
RAJCS10ROHIT
CREATE TABLE "EMP_DETAIL"
("ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"DEPTNO" NUMBER(2,0),
"DETAIL" VARCHAR2(100)
[code]...
The closest I got was with the below, but this also returns duplicates within the same NAME_ID.
select phone_number, name_id from name_phone where (phone_number) in (select phone_number from name_phone group by phone_number having count(*) > 1) group by phone_number, name_id order by phone_number
column sid format 'a5' column serial# format 'a10' column mins_running format 'a15' column sql_text format 'a100' set linesize 200 set pagesize 30
[Code]..
I am running this code, and the output shows multiple lines.
TRIM(S.SID) TRIM(S.SERIAL#) MINS_RUNNING SUBSTR(Q.SQL_TEXT,1,70) ---------------------------------------- ---------------------------------------- --------------- ---------------------------------------------------------------- 700 46592 242.08 Select count(*) as count, case when count(*)>0 then 'FAIL' else 700 46592 242.08 'PASS' end as result from (SELECT cv.code_value FROM code_valu
[Code]...
Is there a way to wrap up the column for SQL_TEXT VARCHAR2(64) so that I can 1 row for the output?
I'm going to do some testing, and for that I require to retrieve some data based on a single column e.g test_data_col, which -
1. Has 3 or more count(test_data_col) for a given set of group by columns e.g grp_col1, grp_col2, grp_col3 2. Within the set of rows retrieved, that particular column holds some duplicate values. I don't need the duplicates displayed, just know if duplicates exist or not.
This might explain what I'm trying to do -
grp_col1, grp_col2, grp_col3, test_data_col
1, A, xyz, HELLO 1, A, xyz, HELLO 1, A, xyz, BYE 1, A, xyz, GOODBYE
2, C, pqr, WELCOME 2, C, pqr, GOOD MORNING 2, C, pqr, BAD MORNING
So for condition 1, I do something like this -
SELECT COUNT(test_data_col) cnt, grp_col_1, grp_col2, grp_col3 FROM test_tab GROUP BY grp_col_1, grp_col2, grp_col3 HAVING COUNT(test_data_col) >= 3;
In this same query, I want to do something that will tell me if the aggregate COUNT(test_data_col) has any duplicate values within it. Again, displaying the duplicates is not important here.
SELECT COUNT(test_data_col) cnt, grp_col_1, grp_col2, grp_col3, /*some logic*/ dup_val FROM test_tab GROUP BY grp_col_1, grp_col2, grp_col3 HAVING COUNT(test_data_col) >= 3;With the proper coding to replace /*some logic*/, I get following values -
cnt, grp_col_1, grp_col2, grp_col3, dup_val
4, 1, A, xyz, Y 3, 2, C, pqr, N
I just gave dup_val column to explain what I'm trying to achieve.. any other way to know the existence of duplicates in the count aggregate will be fine.My Oracle version is Oracle Database 11g Enterprise Edition Release 11.1.0.7.0
I have a form which utilizes 2 canvases that the user can toggle between. There is a database column that I would like to have appear on both and be updatable from either place. In my search here first, I found where I could set up a non-database item and copy to it at the point of Post-Query...and that comes close, but I need both columns to not just reflect the db column but be able to update it.
I am about to try using a second trigger to move things from a non-DB column to a DB-column next, but just wondered if there is a better way. When I first compiled with the designer the duplicate column I set up as a DB column also. It only gave me warnings (that I could have lived with) but the ultimate compile my system does outside of the designer calls it an error.
"This is just for testing 123. This is just for testing 45654. This is just for testing 5567876. This is just for testing 53456547. This is just for testing 123423. This is just for testing 98090. This is just for testing 099473. This is just for testing. This is just for testing. This is just for testing 3. This is just for testing 34983245983. This is just for testing 6432."
I need to divide this sting after every 100 characters, as the length of column to insert is 100. And i do not want to modify the column as it has great impact. I need to divide the string, such that it should be less then 100 characters also the string is not cut in between.
like: first string: "This is just for testing 123. This is just for testing 45654. This is just for testing 5567876."
then 2nd string: "This is just for testing 53456547. This is just for testing 123423. This is just for testing 98090."
then 3rd string: "This is just for testing 099473. This is just for testing. This is just for testing. This is just for testing 3."
I am using 10.2.4.0 of oracle. I am having one requirement, in which i have to divide the set of records into certain groups , so that they can be executed partly but not in one run.
So in the 'SELECT' clause itself i want to assigns particular value (may be 1 )to first 50000 records then another value(may be 2) to next 10000, like wise. And again the total count of records will also varry time to time , if the total count of record set is less than 10000 , then it should only assign '1' to all the records. i will set the group values (1,2,3...) as another column itself.
create table asgnd_agent_bak as (Select * from ASGND_AGENT a, SCN s where a.CNTCT_KEY = s.CNTCT_KEY and a.SCN_NUM=s.SCN_NUM and a.ACTVTY_DT = to_date('03/17/2013','mm/dd/yyyy') and s.SCN_OPEN_DT = to_date('03/15/2013','mm/dd/yyyy') and a.SRC_SYS_DESC = 'FACET'); create table asgnd_agent_bak as (Select * from ASGND_AGENT a, SCN s * ERROR at line 1: ORA-00957: duplicate column name
I have 4 reports on the same page. I have added a checkbox column to each one. On clicking I want to populate a collection with the value of the id of the corresponding row and then have a button acting on the whole list.
My code to get the id of the item that contains the value I want is this
id = 'f02_' + $(this.triggeringElement ).attr('id').substr(4,4);
But there is an f02_0001 for each report, and the code just finds the first one, rather than the one in the context of the report being clicked.
I want to apply divide and conquer approach on the field value, For example I have the field value like this
Value 32,36,12,65,85,9663 43,36,89,65,112,9663 36,63,12,65,95,123 32,36,12,85, 85,9663
Remember that these value are taken from ONLY one field i.e Value. Apply the divide and conquer approach in such a manner that take the value of a single field and each row and divide the value into two half. Continue this process until single value until we reach the single value or single sub problems.
Apply the divide and conquer approach on all the value of field. When we get final value then match the value of two different rows. And check how much field value are similar (match or duplicated). If field values are match more then 80% then delete the duplicated value and keep the original entity.
I am getting [Error] PLS-00402 (182: 1): PLS-00402: alias required in SELECT list of cursor to avoid duplicate column names error in my SP.I have created alias for each column and still i am getting the error.
for my_rec_lot in (SELECT LLP.BOOK_VALUE LLP_BOOK_VALUE,LLP.COMMISSION LLP_COMMISSION,LLP.CURRENCY LLP_CURRENCY,LLP.EXCHANGE_RATE LLP_EXCHANGE_RATE,LLP.EXPENSES LLP_EXPENSES,
I am using Oracle Discoverer Plus and I have some columns in the database table that periodically take the zero value. I have to use that column to divide other values. Is there any way I can handle this to show "0" when it is divided by zero?
Discoverer Plus shows that PL/SQL functions can be used in calculations. But I can't find the Register PL/ SQL function option in the Tools menu.
insert into ashok_temp11 values('A1','1001'); insert into ashok_temp11 values('A2','1001'); insert into ashok_temp11 values('A3','1002'); insert into ashok_temp11 values('A4','1003');
[Code]...
I need the duplicate values in the table. That means
col1 col2 a1 1001 a2 1001 a6 1001 a3 1002 a7 1002
I tried the following query and its not fetching me any records.
SELECT col1, col2 FROM ashok_temp11 GROUP BY col1, col2 HAVING COUNT(col2) > 1 ORDER BY col2
I will be duplicating my production database back to a few weeks ago on my test server, to retrieve certain data. I am running 11g standard edition, not using a recovery catalog.
Below are the basic steps I plan to take in a nutshell.
1. Copy the backup files to the test system
2. In the rman run block (on the test server) A. Set newname for each of the datafiles and tempfiles (don't have many files) B. Duplicate command to point in time, specifying backup location and no filename check.
Trying to delete duplicate rows from a table. The problem is, they aren't exactly duplicate rows. Let me explain.
I am migrating data from a Oracle 8.1.7 db to a 10.2.1 db. In the older db, this certain table does not have a PK/Unique Index, but in the new db there is a unique index. The fields that the index is unique on are:
In the old db, when I run this query I get 1229 rows. With a count of 2 each.
select SUBSCR_NO, SUBSCR_NO_RESETS, EXTERNAL_ID, EXTERNAL_ID_TYPE, ACTIVE_DATE, count(*) from customer_id_equip_map group by SUBSCR_NO, SUBSCR_NO_RESETS, EXTERNAL_ID, EXTERNAL_ID_TYPE, ACTIVE_DATE having count(*)>1;
They are duplicates on those fields, but they are not totally duplicate rows because there is a field called is_current that has 0 in one row and has 1 in the other. What I need to do, is delete the 1229 rows with is_current=0.
how can i get distinct records through this coding, when i add select distinct col1, col2, col3 from tablename where RECD_ON between :control. REC_ FROM and :control.REC_TO; in 1st qry after begin, this form not retrive any data from database, then how i get distinct rows through this coding. is there any option in property plattee to get distinct rows.
declare qry varchar2(5000); n number; alert number; Begin [code]....
