Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Extract only part of data

Posted on 2016-08-12
5
Medium Priority
?
247 Views
Last Modified: 2016-08-22
Hello, dear Experts.
I'm  using occasionally  Oracle Business Intelligence 11.1.1.9.160119 and I've a column with number data separated by semicommas.
here is a sample row:

2859527478; 9782859527471

The number sequence has variable lenght, both before and after the semicomma.

Is there a way to filter (within OBI) these data for maintaining only the values before the semi-comma?

Thanks a lot in advance for the reply,

Fabiano
0
Comment
Question by:fabianope65
[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
  • 2
  • 2
5 Comments
 
LVL 5

Expert Comment

by:Abhimanyu Suri
ID: 41753879
select substr('2859527478; 9782859527471',1,(instr('2859527478; 9782859527471',';')-1)) from dual

2859527478

Please replace string with column name, also test it thoroughly with multiple scenarios.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 41756733
select regexp_substr(your_column,'[^;]+') from your_table
1
 

Author Comment

by:fabianope65
ID: 41764850
Hi,
excuse the delay 'cause illness
I've tried the sdstuber solution but I've the error output reported at the end of this mail.
I guess that the sdstuber  solution is right and that the query does not produce any data 'cause a wrong configuration of our Oracle Business Intelligence.
Please can you confirm that, so I can search an alternative solution to the problem?
Thanks,
Fabiano

here follows the error msg:

***************
Error Codes: OAMP2OPY:OPR4ONWY:U9IM8TAC:OI2DL65P:OI2DL65P
Odbc driver returned an error (SQLExecDirectW).
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 27042] Function regexp_substr is not defined by administrator.
Please have your System Administrator look at the log for more details on this error. (HY000)
SQL Issued: {call NQSGetQueryColumnInfo('SELECT regexp_substr("Bibliographic Details"."ISBN",''[^;]+'') FROM "Physical Items"')}
SQL Issued: SELECT regexp_substr("Bibliographic Details"."ISBN",'[^;]+') FROM "Physical Items"
*************************
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 total points
ID: 41765355
Looks like your BI tool is trying to parse the statement before executing it.
Try configuring it to use a "pass through" query - i.e. one where it simply sends the query as is to the database.
1
 

Author Closing Comment

by:fabianope65
ID: 41766390
Thanks a lot,
Fabiano
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

This article was initially published on Monitis Blog, you can read it here . When it comes to deciding which approach to website performance monitoring is best for your business, unfortunately, like so many options in life . . . it depends. In th…
One event, two days, a great line-up of speakers, and 48% female presence. Still have no idea what I’m talking about?
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

636 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