Solved

picking data to the right or left of a character

Posted on 2014-02-10
15
307 Views
Last Modified: 2014-02-11
How can i define a select statement to pick up based on data withing a colomun?

EG:

I have  these results
CHAIN	CUSTOMER	ROUTE	ITEM	QTY	SHIPDATE
	7606-1570	30	2153	12	20140210
	7606-1570	30	61100	12	20140210
	7606-1570	30	61101	24	20140210
	7606-1570	30	61102	12	20140210
	7606-1570	30	61103	12	20140210

Open in new window



I want to put everything left of a "-" or a " " in customer into chain, anything to the right of "-" or " " in customer, in customer.

eg;

CHAIN	CUSTOMER	ROUTE	ITEM	QTY	SHIPDATE
7606	1570		30	2153	12	20140210
7606	1570		30	61100	12	20140210
7606	1570		30	61101	24	20140210
7606	1570		30	61102	12	20140210
7606	1570		30	61103	12	20140210

Open in new window



current select statement
select
  CASE WHEN SUBSTR(CUSTNO,1,1) in ('2','4') then '0' ELSE CHAIN END "CHAIN",
  CUSTNO "CUSTOMER",
  REP "ROUTE",
  ITMNUM "ITEM",
  QTY "QTY",
  to_char(sysdate, 'yyyymmdd') SHIP_DATE
 from credit_imports i;

Open in new window

0
Comment
Question by:FutureDBA-
  • 9
  • 3
  • 3
15 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39848517
Try this:
...
regexp_substr(customer,'^[0-9]+') chain,
regexp_substr(customer,'[0-9]+$') customer,
...
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39848537
are the portions you're interested in always 4 characters?

SELECT SUBSTR(custno, 1, 4) chain,
       SUBSTR(custno, -4) customer,
       rep "ROUTE",
       itmnum "ITEM",
       qty "QTY",
       TO_CHAR(SYSDATE, 'yyyymmdd') ship_date
  FROM credit_imports
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39848539
http:#a39848517   assumes your data is always numeric.  If that's not the case and the data might not be 4 characters then try this..

SELECT REGEXP_SUBSTR(custno, '^[^- ]+') chain,
       REGEXP_SUBSTR(custno, '[^- ]+$') customer,
       rep "ROUTE",
       itmnum "ITEM",
       qty "QTY",
       TO_CHAR(SYSDATE, 'yyyymmdd') ship_date
  FROM credit_imports
0
 

Author Comment

by:FutureDBA-
ID: 39848751
added more data.

When customer starts with 2 or 4, it should stay as is. another way to decipher if it's exactly 5 characters long like the 24568 example below

TABLE
  CREATE TABLE CREDIT_IMPORTS 
   (	XDATE VARCHAR2(20 BYTE), 
	REP VARCHAR2(200 BYTE), 
	ITMNUM VARCHAR2(20 BYTE), 
	QTY VARCHAR2(20 BYTE), 
	CUSTNO VARCHAR2(20 BYTE), 
	CHAIN VARCHAR2(20 BYTE), 
	STATUS VARCHAR2(3 BYTE)
   ) 

Open in new window


Insert into CDC.CREDIT_IMPORTS (XDATE,REP,ITMNUM,QTY,CUSTNO,CHAIN,STATUS) values ('20140213','90','456','5','7603 15','0',null);
Insert into CDC.CREDIT_IMPORTS (XDATE,REP,ITMNUM,QTY,CUSTNO,CHAIN,STATUS) values ('20140213','90','456','5','7603-15','0',null);
Insert into CDC.CREDIT_IMPORTS (XDATE,REP,ITMNUM,QTY,CUSTNO,CHAIN,STATUS) values ('20140213','90','456','5','25468','0',null);
Insert into CDC.CREDIT_IMPORTS (XDATE,REP,ITMNUM,QTY,CUSTNO,CHAIN,STATUS) values ('20140213','90','456','5','1820 - 54','0',null);
Insert into CDC.CREDIT_IMPORTS (XDATE,REP,ITMNUM,QTY,CUSTNO,CHAIN,STATUS) values ('20140213','90','456','5','26451','0',null);

Open in new window


select qry
select 
CHAIN,
CUSTNO,
REP,
ITMNUM,
QTY,
XDATE
from credit_imports;

Open in new window



current results
CHAIN	CUSTNO  	REP	ITEM	QTY	XDATE
0	7603 15		90	456	5	20140213
0	7603-15		90	456	5	20140213
0	24568		90	456	5	20140213
0	1820 - 54	90	456	5	20140213
0	26451		90	456	5	20140213

Open in new window



Expected results

CHAIN	CUSTNO  	REP	ITEM	QTY	XDATE
7603	15		90	456	5	20140213
7606	15		90	456	5	20140213
0	24568		90	456	5	20140213
1820	54		90	456	5	20140213
0	26451		90	456	5	20140213

Open in new window

0
 

Author Comment

by:FutureDBA-
ID: 39848775
also,

if chain has data value other than 0, leave untouched.. see below what happens.

good
good

bad
bad
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39848807
In the future you will get your answer a lot faster if you provide the exact requirements.

See if this works for you (I used sdstuber's regex just in case it isn't just numbers):
...
case when chain = '0' and substr(custno,1,1) in ('2','4') then chain else  REGEXP_SUBSTR(custno, '^[^- ]+') end chain,
case when chain = '0' and substr(custno,1,1) in ('2','4') then custno else  REGEXP_SUBSTR(custno, '[^- ]+$') end custno,
...
0
 

Author Comment

by:FutureDBA-
ID: 39849121
it's on the right path, it fixes chain / cust when cust has a combo like '7620 - 190'

but when my chain is setup the correct way '7620' and custno 190.. I get 190 for both

Current Results using slightwv last suggestion

190	190	100	3160	12	20140210
190	190	100	3161	12	20140210

Open in new window




original data
7620	190	100	3160	12	20140210
7620	190	100	3161	12	20140210

Open in new window


I am looking for the data to stay the same since it is already in the format that I need it
0
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.

 

Author Comment

by:FutureDBA-
ID: 39849124
i tried this exactly

WITH Q AS (select
case when chain = '0' and substr(custno,1,1) in ('2','4','7') then chain else  REGEXP_SUBSTR(custno, '^[^- ]+') end chain,
case when chain = '0' and substr(custno,1,1) in ('2','4','7') then custno else  REGEXP_SUBSTR(custno, '[^- ]+$') end custno,
  REP "ROUTE",
  ITMNUM "ITEM",
  QTY "QTY",
  to_char(sysdate, 'yyyymmdd') SHIP_DATE
from credit_imports)

select * from Q where custno like '%190%';

Open in new window

0
 

Author Comment

by:FutureDBA-
ID: 39849130
I am also getting records like this

0      7606-1570      30      1031      12      20140210
0      7606-1570      30      1032      12      20140210
0      7606-1570      30      1203      12      20140210
0      7606-1570      30      1206      12      20140210
0      7606-1570      30      1208      12      20140210
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39849132
>>it's on the right path, it fixes chain / cust when cust has a combo like '7620 - 190'
>>but when my chain is setup the correct way '7620' and custno 190.. I get 190 for both

Please add actual insert statements to the sample above.  Just 'guessing' I cannot reproduce the results you posted.
0
 

Author Comment

by:FutureDBA-
ID: 39849137
Please see attached file
ee1.sql
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39849146
what are the desired results for this row?

Insert into CREDIT_IMPORTS (XDATE,REP,ITMNUM,QTY,CUSTNO,CHAIN,STATUS) values ('20140210','50','1020','12','26292','0',null);


I think this does it



SELECT custno,
       chain originalchain,
       CASE
           WHEN chain != '0' OR LENGTH(custno) = 5 THEN chain
           ELSE REGEXP_SUBSTR(custno, '^[^- ]+')
       END
           chain,
       CASE WHEN LENGTH(custno) = 5 THEN custno ELSE REGEXP_SUBSTR(custno, '[^- ]+$') END customer,
       rep "ROUTE",
       itmnum "ITEM",
       qty "QTY",
       TO_CHAR(SYSDATE, 'yyyymmdd') ship_date
  FROM credit_imports
0
 

Author Comment

by:FutureDBA-
ID: 39849166
for that row, chain 0, custno 26290.

 testing now
0
 

Author Comment

by:FutureDBA-
ID: 39849170
that looks good so far.

I will test in the AM with a full days worth of data.  full day worth of data will process about 9k rows for this process.

thanks for the help. always appreciated.
0
 

Author Closing Comment

by:FutureDBA-
ID: 39850907
Thank you sir, worked perfectly
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.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

747 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

17 Experts available now in Live!

Get 1:1 Help Now