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
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??
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.
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]...
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.
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 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 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
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
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.
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.
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.
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'
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
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.
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]....
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.
