SQL & PL/SQL :: To Remove Duplicates From Concatenate String
May 12, 2011
I have a query like
SELECT country_name,
substr(SYS_CONNECT_BY_PATH(product_name,','),2) as PRODUCT_NAME,
substr(SYS_CONNECT_BY_PATH(SPEED_VALUE,','),2) as SPEED_VALUE,
substr(SYS_CONNECT_BY_PATH(i.SUPPLIERNAME_ACCESSPROTYPE,','),2) as SUPPLIERNAME_ACCESSPROTYPE
FROM (SELECT b.country_name,b.product_name,b.speed_value,(supplier_name|| supplier_product || access_product_type)as
[code].......
In the result , I am getting repeated values for product_name and speed value,something like 'ALL Products,All Products,All Products'in the product_name column and '128Kbps,128Kbps'in Speed_vale.i am not able to remove the repeated values here.
View 2 Replies
ADVERTISEMENT
Oct 11, 2013
Removing duplicates from a string that contains years and "-".
Example: 1988-1997-2000-2013-1998-1965-1997-1899
I know this can be done in regular expressions but have no experience in this subject.
select REGEXP_REPLACE(.....) from dual;
View 16 Replies
View Related
Mar 24, 2011
remove duplicates from my collection(table type).Or an alternate solution for my issue is get clean data into my collection which i am not able to get to either.
Object creation
create table testingtype_table(ordernumber number,org_id number , company_name varchar2(10))
insert into testingtype_table values (1124,2424,'cbaaa');
insert into testingtype_table values (1124,2424,'cbaaa');
create or replace type testingtype_obj as object (ordernumber number,org_id number , company_name varchar2(10));
create or replace type testingtype_tab as table of testingtype_obj;
Code Block
declare
l_testingtype_tab testingtype_tab := testingtype_tab();
begin
select distinct testingtype_obj(ordernumber
,org_id
,company_name)
bulk collect into l_testingtype_tab
from testingtype_table;
end;
If only i can get a way to bulk collect only distinct values into the table type that will just do great but when i try the above (with distinct highlighted in red) it throws an error
ORA-22950: cannot ORDER objects without MAP or ORDER method
View 4 Replies
View Related
Aug 3, 2011
i have two tables
Quote:
Table has following data.
id indicator
---------------
1 A
2 A
3 A
Quote:
Table2 has
id indicator
---------------
1 X
1 X
2 X
3 X
I would like to have following output ( Am running query on Toad)
t1_id t1_indicator t2_id t2_indicator
---------------------------------------------
1 A 1 X
1 X
2 A 2 X
3 A 3 X
View 4 Replies
View Related
Nov 8, 2012
I want to remove duplicates from a column MAIN_TABLE based on TLEVEL Column:
create table UNIQ_TEMP
( TLEVEL NUMBER(10,0),
TABLE_NAME VARCHAR2(30),
MAIN_TABLE VARCHAR2(30)
);
[code]....
My Requirement is:
MAIN_TABLE= MARKETING_OPTIN
EXISTS IN MAX(TLEVEL)
REMOVE DUPLICATE MARKETING_OPTIN FROM OTHER LEVELS
This should apply to all the values in column MAIN_TABLE
View 12 Replies
View Related
Jun 6, 2013
I've got a slowly changing dimension table for products with some duplicate attributes -
UNIQUE_ID | DATE_FROM | DATE_TO | PRODUCT_ID | ATTRIBUTE_1 | ATTRIBUTE_2
1 01-JAN-13 02-JAN-13 423 MONKEY 5
2 03-JAN-13 04-JAN-13 423 MONKEY 5
3 05-JAN-13 08-JAN-13 423 MONKEY 4
4 09-JAN-13 10-JAN-13 423 SUPERMONKEY 4
5 01-JAN-13 08-JAN-13 378 BANANA 2
6 09-JAN-13 10-JAN-13 378 BANANA 3
The natural key should be PRODUCT_ID, ATTRIBUTE_1 and ATTRIBUTE_2. The table should therefore be recreated as follows:
UNIQUE_ID | DATE_FROM | DATE_TO | PRODUCT_ID | ATTRIBUTE_1 | ATTRIBUTE_2
1 01-JAN-13 04-JAN-13 423 MONKEY 5
2 05-JAN-13 08-JAN-13 423 MONKEY 4
3 09-JAN-13 10-JAN-13 423 SUPERMONKEY 4
4 01-JAN-13 08-JAN-13 378 BANANA 2
5 09-JAN-13 10-JAN-13 378 BANANA 3
View 6 Replies
View Related
Mar 5, 2008
I am trying to replace the string_name in the following execute immediate statement with a concatenated
string_||v_variable.
execute immediate
'select
table_name
from
user_tables
where table_name = :a'
into v_table
using 'string_name';
I tried different declaration and concatenations but nothing worked so far.
View 2 Replies
View Related
Jul 29, 2010
i m create one function in which i have to concatenate result likeDate is 19/07/2010'. i tried to concatenate in select statement. in this case function is created successfully but it gives error when it run.
CREATE or replace function TEST(FINYEAR VARCHAR2)
RETURN VARCHAR2 AS
QRY_STRING VARCHAR2(1000);
BALANCE VARCHAR2(100);
[code]...
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
ORA-06512: at "TEST", line 11
View 19 Replies
View Related
Jun 3, 2011
I am running the following delete query and it has been running for over 2hrs:
delete from dw.ACCOUNT_FACT
where rowid in
(select rowid from DW.ACCOUNT_FACT
minus
select max(rowid) from DW.ACCOUNT_FACT
[Code]..
Here is the explan plain result:
explain plan for delete from dw.ACCOUNT_FACT
where rowid in
(select rowid from DW.ACCOUNT_FACT
minus
select max(rowid) from DW.ACCOUNT_FACT
group by CRTORD_FIPS_CD, LAST_PAYMENT_DT, ORDER_NUM,
[Code]....
PLAN_TABLE_OUTPUT
Plan hash value: 611392786
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 2604G| 260T| | 9018K (91)| 30:03:37 |
| 1 | DELETE | ACCOUNT_FACT | | | | | |
|* 2 | HASH JOIN | | 2604G| 260T| 369M|
[Code].....
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(ROWID="$kkqu_col_1")
I have all constraints disabled. How do I make this delete finish faster? We're trying to remove duplicates from this table using the criteria giving in the statement.
View 16 Replies
View Related
Oct 4, 2011
I need to concatenate string to the number field in an update statement like this:
update test1 set model_pin = seq_no || '_' || model
where eoc_code like 'AEW%'
When I run this command in sql , I get ERROR at line 1:
ORA-01722: invalid number
View 7 Replies
View Related
Jun 4, 2013
version : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
i want to ,remove consecutive occurance from string
Example I/P: 'POWELL POWELL BRIAN K AND BONNIE POWELL JARRELL JARRELL'
to O/P : 'POWELL BRIAN K AND BONNIE POWELL JARRELL'I tried the below code is Working fine , But i wanted to do this using Regexp or Some other Better Method
WITH T
[Code]....
View 8 Replies
View Related
Aug 5, 2010
I have a following table,
create table test1(col1 varchar2(20));
insert into test1 values('4711-3/01');
I believe we need to use Translate function to get rid of special characters, But I would not be knowing what sort of special charecters which appear in the string, In that case how do I use Translate?
View 29 Replies
View Related
May 22, 2013
I am getting string from my tool like this.... .. ‘ ‘PH1234’,’Ph3456’,’PH5678’ ‘
I wanted to remove single codes and take the each value and I have to process. Need output like this....
PH1234,
Ph3456,
PH5678.
View 4 Replies
View Related
Oct 29, 2012
I want to remove more than one space from a string by excluding double quotes.
For example:
I/P: Item .getChildByType(" Agreement").getParent( ) .hasChildByType("Agreement ")
O/P : Item.getChildByType(" Agreement").getParent().hasChildByType("Agreement ")
View 17 Replies
View Related
Feb 7, 2013
I am on 11g.
I need to remove the alpha characters from a string, leaving only numbers, but I am getting unexpected results:
SQL> SELECT TRANSLATE('3N', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', NULL) a FROM DUAL;
A
-
I thought this would leave the 3 from the 3N, but it is returning an empty string. For my application, the string '3N' could be any length, will only contain letters and numbers, and the letters will always come at the end, but there could be more than one letter
VALID INPUT samples:
4
25
11F
361NG
8ABC
View 6 Replies
View Related
Aug 29, 2012
To remove the last comma end of string and load the Clob data into table. create table test(name clob)
View 2 Replies
View Related
Dec 4, 2012
Database version: 11.2.0.3.0
I need to remove duplicate values from concatenated long string of state codes(comma separated). Ex: 'VA,VA,PA,PA,CT,NJ,CT,VA'. I tried following query and did not get required out put.
select regexp_replace('VA,VA,PA,PA,CT,NJ,CT,VA,CT,PA,VA,CT','([^,]*)(,1)+($|,)', '13') new_str from dual;
Define Meta-character's format in regular expression to get desired result. Out put required: VA,PA,CT,NJ (with out any duplicates).
View 4 Replies
View Related
May 10, 2010
I have a string like below:
string = 'HEADER||MEAL||15'
How to get 'MEAL' string? The length of the string can be various. Means, 'MEAL' can be 'INFLIGHT'. So, i cant use the substr. Is there a function that can recognize the pipeline? so that i can remove all the string before the pipeline and after the pipeline to get the string between the pipeline?
View 9 Replies
View Related
Feb 17, 2010
I have a table which I will call 'fruit basket' which contains the following data:
basket_ref, fruit, fruit_serial
1, apple, 1
1, banana, 2
1, pear, 3
2, apple, 1
2, lemon, 2
What I want to produce is a table in the following format
basket_ref, all_fruit
1, apple banana pear
2, apple lemon
There could theoretically be any amount of fruit in a given basket, so I will need to set up some kind of loop in order to read each record from the original table. I am not sure how it would be best to do this.
View 2 Replies
View Related
Jul 26, 2012
I have the following info in a table:
LEVELPARENTPOSCHILD
1100571010055
1100572010053
2100552010056
2100551010054
The output that I need is:
LEVELPARENTPOSCHILD
1100571010055
21005510.1010054
21005510.2010056
1100572010053
If there are even more levels, I also need those childs to be shown right below their parent and with POS as 10.20.10 )(for the first item below POS 10.20.
View 4 Replies
View Related
Feb 21, 2012
I'd like to concatenate my 3 columns into one. I have this text item(NAME) and i want my columns lname, fname and mname to be bound into name text item.
this is so far my code to do the concat:
SELECT lname
INTO :ADVISEMENT.name
FROM students
WHERE :ADVISEMENT.name = lname || ',' || fname || ',' || mname;
but it didn't appear at all.
View 17 Replies
View Related
Jan 26, 2012
This is a simple question, but I cannot seem to find a solution. Here's the basic query:
select distinct accountno, parcelno, streetno||' '|| predirection ||' '|| streetname||' '|| streettype||' '|| postdirection||' '|| unitname||', '|| propertycity
from tblacctpropertyaddress ....
What I want to do is add is this logic: If Predirection is null, then no space between streetno & streetname. Same for postdirection and unitname. (for example, if both postdirection and unitname are null, there are no spaces between streettype and the comma before propertycity)
Also, when unitname is not null, I want to add the string "Unit " prior to the returned value in unitname.
View 5 Replies
View Related
May 27, 2013
I have three database fields on my form , field_1 , field_2 and field_3 , user will enter the data in field_1 , and sometime he may not enter in field_2 ,
I want to bring the value of field_1 as it is into field_3 if he does not enter anything in field_2, if he enters the data in field_2 , then field_1 will get concatenated with field_2 and gets copied into field_3.Is there a way to do this automatically at form level using a trigger when-validate-item.In short this should be done after user presses the enter key on field_2.
create table ot_Sale_code_head ( sale_code varchar2(12),code1 varchar2(6),code2 varchar(6),code3 varchar2(12));
insert into ot_Sale_code_head ('A','01.01',NULL,NULL);
--inserted record in form will be 'A','01.01',NULL,'01.01'
insert into ot_Sale_code_head ('B','01.01','PS-P',NULL);
--inserted record in form will be 'B','01.01','PS-P','01.01.PS-P'
View 2 Replies
View Related
Aug 19, 2010
I am running SQLLDR on unix.
LOAD DATA
INFILE F56100c.dat
APPEND
INTO TABLE tstdta.F56100C
(
Z59SFU13 POSITION(14;21),
[Code]...
I have already used
1.Z59SFU17 POSITION(55:59)||POSITION(51:53)||POSITION(47:49) getting err:Illegal combination of non-alphanumeric characters
2.Z59SFU17 POSITION(55:59,51:53,47:49) this is also not working.
3. i can not use ":Z59SFU15 || :Z59SFU16" this option.
View 6 Replies
View Related
Sep 30, 2010
SELECT OM.ORG_NAME||' Unit - ':p_unit name
FROM org_mst om
In the above query I want to concatenate Unit number passed by a parameter.
View 4 Replies
View Related
Oct 8, 2010
In table_A , the primary key is Col_A which is of data type number.I want to concatenate it with ' '
Col_A
______
123
124
select '|| col_A ||'||','
from
Table_A;
The output should be
'123',
'124'
I can concatenate a comma but not single quotes.
View 3 Replies
View Related
Mar 19, 2013
I'm a fledgeling to start of with databases. Query below :
Select CONCAT(last_name,'',first_name)
from employees
When I run this query in SQL developer, I'm getting the following error :
ORA-00909: invalid number of arguments
+00909. 00000 - "invalid number of arguments"+
The aim is to concatenate last_name and first_name fields with a space in between.
View 4 Replies
View Related
Dec 6, 2012
create or replace procedure ab(a in varchar2, b in varchar2)
is
test varcha2(8);
begin
if (a is not null) then
for i in(select c
from t
where c between ||'''||a||'''|| and ||'''||b||'''||)
loop
test:=i.c
end loop;
end if;
end;
I want both parameter input values to be enclosed in quotes so that it considers both parameter values as char.Receiving ora 00936 missing expression error.
View 7 Replies
View Related
Jul 26, 2010
I want to load a delimited file that contains many records which contained within the table where I'm going to load a date type field and I need to do this by concatenating three fields
field1 = 1 - this is the day
field2 = 11 - this corresponds to the month
field3 = 5 - this corresponds to the year
I need is in the field Save as type date 01/11/2005 i don´t know how to do it but I tried as follows but I get error loading.
"TO_DATE (TO_CHAR (: field1 ||'/'|| DECODE (: field2, 1, 'JAN', 2, 'FEB', 3, 'MAR', 4, 'APR', 5, 'MAY', 6 , 'JUN', 7, 'JUL', 8, 'AUG', 9, 'SEP', 10, 'OCT', 11, 'NOV', 12, 'DEC': field2 )||'/'|| : field3)) "
View 3 Replies
View Related
Jun 13, 2012
I am loading data using sqlldr command in UNIX to an oracle table and want to concatenate timestamp to a file name in the "create_file_name" column in the code below.
I have the below code within the control file..
LOAD DATA
TRUNCATE
INTO TABLE TABLEA
TRAILING NULLCOLS
(
file_type POSITION(1:5) CHAR,
business_date POSITION(16:23) DATE "YYYYMMDD",
create_file_name "FILE_NAME" EXPRESSION "SELECT TO_CHAR(CURRENT_TIMESTAMP(3), 'YYYYMMDDHH24MISS') FROM DUAL")
The load fails with SQL Loader error: "Expecting valid column specification, ",", ")", found keyword EXPRESSION found instead of column. How the timestamp to a filename can be appended?
View 5 Replies
View Related
