Solved

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

Posted on 2014-11-30
10
137 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, 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 37

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 37

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL 2008 Divide by zero error encountered. Error 3 36
SQL NULL vs Blank 26 36
Mysql Left Join Case 10 54
SQL Query assistance 16 25
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

773 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