Solved

Extract only part of data

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

Suggested Solutions

Title # Comments Views Activity
join 2 views with 5 conditions 3 57
Oracle dataguard 5 33
Oracle function to insert records? 15 42
error in my cursor 5 35
Why would you want to link Google Analytics to Google AdWords? After linking them, you can: Import goals and transactions from Analytics View  site engagement data from Analytics in AdWordsCreate remarketing lists in Analytics to use in AdWords for…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

825 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