Solved

Extract only part of data

Posted on 2016-08-12
5
189 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 500 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 500 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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 copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

691 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