SQL & PL/SQL :: Duplicate On A Single Column
Sep 23, 2010
I have a table with multiple columns and I need to retrieve the ones where column a stores duplications for records where column b is discrepant
this is the table
NAME_ID PHONE_NUMBER
12345 +41 22 595 5555
12345 +41 22 595 5555
12342 +41 22 595 5500
12340 +41 22 595 5555
the query should return
NAME_ID PHONE_NUMBER
12345 +41 22 595 5555
12340 +41 22 595 5555
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
View 3 Replies
ADVERTISEMENT
Sep 10, 2012
CREATE TABLE prim_tbl
(id NUMBER,--- id is not primary key here
description VARCHAR2(30));
INSERT ALL
INTO prim_tbl VALUES (1,'aad')
INTO prim_tbl VALUES (1,'aads')
INTO prim_tbl VALUES (2,'bb')
INTO prim_tbl VALUES (2,'cc')
INTO prim_tbl VALUES (2,'dd')
SELECT * FROM dual;
I want to select the ids only one time, i.e my output will have only two rows: one row with id as 1 and other row with id 2 whatever be the description.
desired output sample:
Quote:1, aad
2, bb
I used:
select distinct(id),description from prim_tbl;
but it did not give the required result.How can I get it??
View 6 Replies
View Related
Apr 30, 2010
i'm trying to duplicate my RAC database with ASM and 2 nodes to a single node, non ASM with rman duplicate database for standby with active database on windows. The problem is i dont know how to remove RAC configuration when duplicating the database.
Here is the procedure:
On standby i've:
- installed database software,
- configured listener and tnsnames,
- added instance service (oradim -new -sid STANDBY -intpwd PASSWORD -startmode M),
- configured initora (with only db_name = STANDBY)
- configured orapwd file
- startup nomount
on RAC node1:
rman target sys/PASSWORD@RAC
connected to target database
RMAN> connect auxiliary sys/password@STANDBY
connected to auxiliary database: STANDBY (not mounted)
now for the duplicate command:
RMAN> duplicate target database
2> for standby
3> from active database
4> spfile
5> parameter_value_convert = '+DATA/RAC','D:\oracle\11.2.0\DATAFILES\DATA\STANDBY'
6> set db_unique_name='STANDBY'
7> set log_file_name_convert = '+DATA/RAC','D:\oracle\11.2.0\DATAFILES\DATA\STANDBY'
8> set db_file_name_convert = '+DATA/RAC','D:\oracle\11.2.0\DATAFILES\DATA\STANDBY';
i have tried adding this parameter - set cluster_database='false' but no luck.
View 7 Replies
View Related
Jul 19, 2013
CREATE TABLE TYPE
(
c1_type VARCHAR2 (10),
c2_type VARCHAR2 (10),
c3_type VARCHAR2 (10),
c4_type VARCHAR2 (10),
c5_type VARCHAR2 (10),
c6_type VARCHAR2 (10),
[code]......
actual output of the below query, but i want to display in different way
select * from type;
C1_TYPE C2_TYPE C3_TYPE C4_TYPE C5_TYPE C6_TYPE C7_TYPE C8_TYPE C9_TYPE
Region_D Region_E Region_F Region_D Region_E Region_D Region_M Region_D Region_E
The expected output should be like this below, how to write a query or which built in function used to get the below result,
Region_D
Region_D
Region_D
Region_D
Region_E
Region_E
Region_E
Region_F
Region_M
View 4 Replies
View Related
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]...
View 3 Replies
View Related
Dec 16, 2010
I am trying to write SQL which finds records which are duplicated in more than one column.
Requirement : When ever i have duplicates in Col2 and Col3 both i need that record...
My Source table:
COL1COL2COL3COL4
163kg87
263fh87
1ab23
2ab24
3cd98
4fg87
5xy77
6xy67
Desired Output
COL1COL2COL3COL4
1ab23
2ab24
5xy77
6xy67
For Table generation
CREATE TABLE TEMP_TEST
(
COL1 NUMBER,
COL2 VARCHAR2(10 BYTE),
COL3 VARCHAR2(10 BYTE),
COL4 NUMBER
)
Insert statements
Insert into TEMP_TEST
(COL1, COL2, COL3, COL4)
Values
(163, 'k', 'g', 87);
Insert into TEMP_TEST
(COL1, COL2, COL3, COL4)
Values
[code]....
View 4 Replies
View Related
Mar 8, 2010
I want to avoid duplication on old_nic_no and new_nic_no number in a Table, for this i am using a following trigger
create or replace TRIGGER chk_ip_duplications
BEFORE UPDATE
ON cb_insured_person_bak
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
[Code]...
when i use following command
update cb_insured_person_bak set new_nic_no = '34601-0774284-9' where eobi_no = '4300G043746'
34601-0774284-9 already exist with another eobi_no , i expect error described in rise_application_error clause but i give following error
ORA-04091: Table cb_insured_person_bak is mutating..........
How i can resolve this problem.....
View 15 Replies
View Related
Mar 30, 2013
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?
View 14 Replies
View Related
Mar 18, 2010
I am working on a assignment, How to prevent duplication with Trigger. I create following compound trigger for this, but it is not doing so.
CREATE OR REPLACE TRIGGER CHK_DUPLICATION FOR INSERT ON TST COMPOUND TRIGGER
R_BCOUNT NUMBER;
R_ACOUNT NUMBER;
D_VAL NUMBER;
BEFORE STATEMENT IS
BEGIN
[code].....
View 12 Replies
View Related
May 13, 2013
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
View 2 Replies
View Related
Mar 28, 2013
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.
View 7 Replies
View Related
Mar 23, 2013
I'm getting an error as follows .
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
View 1 Replies
View Related
Mar 27, 2013
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.
<input name="f02" id="f02_0001" type="hidden" value="2072"/>
....
<input name="f02" id="f02_0001" type="hidden" value="2052"/>
...
etc etc.
All that is ever picked up is 2072
They have different table ids but I don't know how to reference the correct ones in my code.
View 3 Replies
View Related
Oct 25, 2013
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,
[Code].....
View 6 Replies
View Related
Mar 26, 2013
I just want to see single rows data as column.
select * from emp where rownum=1;
EMPNOENAMEJOBMGRHIREDATE SAL COMMDEPTNO
7369SMITHCLERK790217-DEC-80 00-00-00 800 20
How can it show as
column_name column_value
EMPNO 7369
ENAME SMITH
JOB CLERK and so on...
View 13 Replies
View Related
Jun 17, 2013
I have been trying to spool a table into a .lst. The table is spooled correctly, but there is a column which has xmlType data into. The problem is it does not write the xml into a single row. Every time it find a xml node, the spooling gets indented.
My code is as follows:
-- Establece opciones de formato.
set echo off;
set feedback off;
set heading off;
set recsep off;
set verify off;
set embedded off;
set long 1000000;
set pagesize 0;
SET LINESIZE unlimited;
set trimout off;
set trimspool on;
set serveroutput on size unlimited;
set term on;
-- Definicion de variables del script
prompt Introducir Creador de la tabla
prompt Creador de la tabla: &&1
prompt Introducir Nombre de la tabla
prompt Nombre de la tabla: &&2
prompt Introducir Directorio del fichero
prompt Directorio del fichero: &&3
prompt Introducir Nombre del fichero
prompt Nombre del fichero: &&4
prompt Introducir Caracter separador de campos
prompt Caracter separador de campos: &&5
prompt Campo fecha: &&6
-- Redirige la salida al fichero
spool /&&3/&&4
SELECT H5279_CD_EMP || '&&5' ||
H5279_LOCAL_ID || '&&5' ||
H5279_LOG_TYPE || '&&5' ||
H5279_GLOBL_ID || '&&5' ||
TO_CHAR(H5279_CREATED, 'YYYY-MM-DD HH24:MI:SS.FF3') || '&&5' ||
[Code] .......
where the field LOGDATA contains the xml. And the output im getting is as follows(I have attached the file)
0049|16052B0E44E3A30037E3CB59|4|16052B0E44E3BF0037D9CB4E|2013-06-12 11:51:16.387|16052B0E44E3A90037E2CB58|n31001|172.31.184.2|trxOpService|BDPESP_CIC_SAN_ENS_V1|||INTERNET|ALN_BDPGPO_Localizperscomun|| OI_LocalizarPersPorDocumentoComun|||ISWSBKSD149|frNixc6Vlic01jhixt5TkfT|16052A9553AE930037FAC740| <?xml version="1.0" encoding="ISO-8859-1"?>
[Code]....
View 5 Replies
View Related
Nov 16, 2012
I am trying to load a single column into a Table. Below is the ctl file:
OPTIONS (ERRORS=10000)
LOAD DATA
TRUNCATE
INTO TABLE "ECOMM"."CC_ECOMM"
TRAILING NULLCOLS
(EMAIL_ID)
Calling loader:
sqlldr silent=ALL userid=username/password@DB control=/home/DEV/control/CC_ECOMM.CTL log=/home/DEV/log/Unsubs.log bad=/home/DEV/log/Unsubs.bad discard=/home/DEV/log/Unsubs.dis data=/home/DEV/files/CC_ECOMM.TXT
Though the count in the Table after load is the same as the input file, they are all NULL.
I tried adding FIELDS TERMINATED BY X'A' for new line and also FIELDS TERMINATED BY X'D' for carriage return. Both times bad file was created and the records that were loaded were again NULL.
The input file has a list of emails:
iatraveler2008@aol.com
iaz65@aol.com
2blue2brown@comcast.net
2c3mwilson@embarqmail.com
abigailolschan@comcast.net
imisskoco@aol.com
I tried FIELDS TERMINATED BY X'10' and FIELDS TERMINATED BY X'13' too for new line and carriage return respectively. This time there were no bad file created, but the Table has Null values.
Log File contents:
Number to load: ALL
Number to skip: 0
Errors allowed: 10000
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Silent options: FEEDBACK, ERRORS and DISCARDS
[code]....
View 4 Replies
View Related
Jun 7, 2011
I am facing some problem, while fetching the result that I want to. I have a table with name "test", there are two columns:
"id" type int
"text_data" type varchar2(2000)
Sample Data:
ID TEXT_DATA
------- ------------
10 Hi Deepak, My designation id is dsha21. Thanks Rohit
Now I tried to replace the value for "Deepak","dsha21" and "Rohit" using nested replace function and I succeded but that was for static. Now while creating SQL procedure where I am going to make the values of "Deepak","dsha21" and "Rohit" some static variables. I want to pass the values to be replaced with static parameter.
If I give you simple example of my requirement that would be example of a sms send to all customers by a telephone company. Content is same only the Name of customer is replaced everytime.
View 10 Replies
View Related
May 27, 2010
How can i copy a single column from one table to another. Table 1 has a column with data in it, table 2 has the column but is empty, i want to copy data for a single column from table1 to table 2. By the way these table have multiple columns.
View 8 Replies
View Related
Sep 2, 2011
i need a query for update..the logic is
i have to update a single column(x.c) in x table.here the condition is x.a is not null and x.b is not null x.d is null then update x.c=x.b for each row.
View 2 Replies
View Related
Nov 15, 2012
Is it possible to create a table without any single column in it?
View 1 Replies
View Related
Apr 9, 2013
need to create a table with single column by using select statement with multiple columns
For Ex- i have 1 row with 10 columns (may be more than 10) like
'A','B','C','D','E','F','G','H',I','J'
i written sql like
select 'A','B','C','D','E','F','G','H','I','J' from dual
result is - 'A','B','C','D','E','F','G','H','I','J' with 10 columns
Now i need output lik this using SQL
Text
------
'A'
'B'
'C'
'D'
[code]...
sort out this problem.
View 6 Replies
View Related
Dec 11, 2012
I have a table where i want the output to be a single column
ex: select from t1;*
query output_
rownum col_1
1 8217
2 6037
3 5368
4 5543
5 5232
i would like the result to be : *8217,6037,5368,5543,5232*
i did look on the web but can't find a solution that is easily understood.
View 9 Replies
View Related
Jun 25, 2012
I am trying to write a script where a particular post code from a table is having more than 3 telephone numbers.Both the columns are in the same table. How to fetch.
Table is P_Order
Columns are DELIVERY_POSTCODE and TEL_NO...
Condition DELIVERY_POSTCODE has more than 3 TEL_NO
View 1 Replies
View Related
Aug 5, 2010
I has a table of structure of varchar2 datatype.
NO
----------
1-2
3-4
5-6
desired output is:
SQL>1
2
3
4
5
6
The table has single column & the values may differ,that is, they may have 1-2-3-...-n in a single row, but the desired output is to be in the rows as shown above.
I tried concepts of SQL up to my knowledge, but I failed. The query to be done only in SQL.complete this query.
View 10 Replies
View Related
Oct 11, 2012
I have a below requirement let me know the optimized query.
Create table test_123 ( sr varchar2(1000));
Insert into test_123 values ('1,2,3,4,5');
Insert into test_123 values ('6,7,9,10,11');
Insert into test_123 values ('9,2,3,8,5');
I need output as :
Sr_output
1
2
3
4
5
6
7
8
9
10
11
I need all the values in the column sr in a single column and mutiple rows.
View 2 Replies
View Related
Oct 5, 2011
I have following tables with data as under:
table1: table2:
column1 (char) column1 (char) column2 (num)
A A 10
B A 20
C B 15
D C 12
E D 25
D 9
I need to generate output as :
column1 column2
A A10, A20
B B15
C C12
D D25,D9
E null
Is there anyway to achieve this thru simple SELECT ...and if not, then thru any PL/SQL construct..?
View 5 Replies
View Related
May 25, 2012
How to update two table column in single query ?
example :
update table1 t1 ,table2 t2
set t1.column = 'Yes',t2.column='Yes'
where t1.emp_code =t2.emp_code ;
View 1 Replies
View Related
Apr 9, 2013
I have a query which returns nearly 20k rows, as per the requiremnet we need to append all these rows in specific format and insert into single clob column.in the below procedure test_clob.textt is clob field.
CREATE OR REPLACE PROCEDURE pro_test
v_mas_seq NUMBER (9);
v_gov_total NUMBER (20, 2);
v_emp_total NUMBER (20, 2);
v_text_exp CLOB;
v_pageaccess VARCHAR2 (15);
v_dto NUMBER (7) := 4011486;
v_batchno NUMBER (20) :=
[code]....
View 15 Replies
View Related
Oct 2, 2012
We have to update a single column data in about 10 tables which has child/parent table relations, pk/fk constraints.. The column that we are updating is a part of primary key in half of the tables and part of foreign key in the other half tables.. I'm thinking of disabling all the foreign key constraints in the tables then update the column data then enable the foreign key constraints in these tables.
View 7 Replies
View Related
