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

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
LVL 1
hi4pplAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gerwin Jansen, EE MVETopic Advisor Commented:
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
hi4pplAuthor Commented:
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
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Gerwin Jansen, EE MVETopic Advisor Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Commented:
select distinct trim(accountnumber), trim(newaccount), trim(oldaccount)
from yourtable;
0
awking00Commented:
>>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
hi4pplAuthor Commented:
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
Gerwin Jansen, EE MVETopic Advisor Commented:
>> 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
awking00Commented:
>>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
awking00Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.