Solved

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

Posted on 2014-11-30
10
138 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 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 40472649
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
ID: 40473289
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]
ID: 40473611
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 38

Accepted Solution

by:
Gerwin Jansen, EE MVE earned 500 total points
ID: 40473634
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 32

Expert Comment

by:awking00
ID: 40474164
select distinct trim(accountnumber), trim(newaccount), trim(oldaccount)
from yourtable;
0
 
LVL 32

Expert Comment

by:awking00
ID: 40474165
>>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
ID: 40483330
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 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 40483443
>> 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 32

Expert Comment

by:awking00
ID: 40484654
>>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 32

Expert Comment

by:awking00
ID: 40484679
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

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup

830 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