Solved

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

Posted on 2014-11-30
10
144 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup

734 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