Solved

How can I use SQL query in oracle to do this?

Posted on 2014-11-30
10
130 Views
Last Modified: 2014-12-14
Hi,

I have around the following example:

accountnumber;newaccount;oldaaccount
123456789;9876;654
123456789  ;9876  ;654
123456789;9875;654
123456789; 9876 ; 654

Open in new window


so the problem is that I have around 13 million records like that and I want to grab only the one record out of those records which is:

123456789;9876;654

in example data I have put SPACES myself which means the data in table have spaces, the accountnumber unique although some have spaces either before or after which is correct for other two as well, so based on that example I would like to have query to grab the first record and leaving others behind even if it has space or different newaccount or oldaccount for me it does not matter as long as I get unique acccountnumber with oldaccount and newaccount whichever it would be from the list.

so what i'm looking for the result is:

accountnumber;newaccount;oldaaccount
123456789;9876;654

Open in new window



thanks
0
Comment
Question by:hi4ppl
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 37

Expert Comment

by:Gerwin Jansen
Comment Utility
What have you tried already? I'm thinking something like this may work:

select distinct (to_number(accountnumber),to_number(newaccount),to_number(oldaaccount)) from your_table;
0
 
LVL 1

Author Comment

by:hi4ppl
Comment Utility
Hi thanks for replay... I have not done anything the only thing I did to use trim and still I get duplicates... your solution don't work as in newaccount and oldaccount I have character also.

regards
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
Comment Utility
You could also try following:

1. add a virtual column
[...] v_col_concat INTEGER GENERATED ALWAYS AS (accountnumber || newaccount || oldaaccount) VIRTUAL [...]

2. create index on that "new" column:
CREATE INDEX idx_v_col_concat_01 ON your_table(v_col_concat);

3. gather/update table/index statistics...

4. do the query:
select * from your_table where v_col_concat = '123456789;9876;654';
0
 
LVL 37

Accepted Solution

by:
Gerwin Jansen earned 500 total points
Comment Utility
Mistaken about parenthesis, query is this:

select distinct to_number(accountnumber),to_number(newaccount),to_number(oldaaccount) from table1;

Used this for testing:
CREATE TABLE table1 (accountnumber VARCHAR(32),newaccount VARCHAR(32), oldaaccount VARCHAR(32))
/
insert into table1 (accountnumber, newaccount, oldaaccount)
values ('123456789','9876','654')
/
insert into table1 (accountnumber, newaccount, oldaaccount)
values ('123456789  ','9876  ','654')
/
insert into table1 (accountnumber, newaccount, oldaaccount)
values ('123456789','9875','654 ')
/
insert into table1 (accountnumber, newaccount, oldaaccount)
values ('123456789',' 9876 ',' 654')
/

Open in new window


Result of select: (all)
ACCOUNTNUMBER      NEWACCOUNT      OLDAACCOUNT
123456789      9876      654
123456789      9876      654
123456789      9875      654
123456789      9876      654

Result of query:
TO_NUMBER(ACCOUNTNUMBER)      TO_NUMBER(NEWACCOUNT)      TO_NUMBER(OLDAACCOUNT)
123456789      9875      654
123456789      9876      654
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
select distinct trim(accountnumber), trim(newaccount), trim(oldaccount)
from yourtable;
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 31

Expert Comment

by:awking00
Comment Utility
>>I want to grab only the one record out of those records which is:
123456789;9876;654<<
I assume that is because the one record with a newaccount value of 9875 is a typo and should have been 9876.
0
 
LVL 1

Author Comment

by:hi4ppl
Comment Utility
hi,

no it's not type, it's what I want the result to be.. and also some records have carriage return I forget to mention it in their and give example of that...
0
 
LVL 37

Expert Comment

by:Gerwin Jansen
Comment Utility
>> and also some records have carriage return I forget to mention it in their and give example of that...
That makes it kind of hard to answer your question. I suggest you post real examples and the output you require.
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
>>no it's not typo [sic], it's what I want the result to be<<
accountnumber;newaccount;oldaaccount
123456789;9876;654
123456789  ;9876  ;654
123456789;9875;654   ==> This record is not a duplicate of the others, with or without spaces (or other white space)
123456789; 9876 ; 654
>>so what i'm looking for the result is:<<
If it's not a typo, then the result should be -
accountnumber;newaccount;oldaaccount
123456789;9876;654
123456789;9875;654
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
In Oracle, you might also use the Translate function to deal with spaces, linefeeds and carriage returns -
select distinct
translate(accountnumber,'1 '||chr(13)||chr(10),'1') accountnumber,
translate(newaccount,'1 '||chr(13)||chr(10),'1') newaccount,
translate(oldaccount,'1 '||chr(13)||chr(10),'1') oldaccount
from yourtable;
You can wrap the translate statements in the to_number function if you need numeric values.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now