Solved

Extract only part of data

Posted on 2016-08-12
5
129 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
  • 2
  • 2
5 Comments
 
LVL 4

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 73

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 73

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

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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 take different types of Oracle backups using RMAN.

896 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

15 Experts available now in Live!

Get 1:1 Help Now