fabiano petrone
asked on
Extract only part of data
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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('SEL ECT regexp_substr("Bibliograph ic Details"."ISBN",''[^;]+'') FROM "Physical Items"')}
SQL Issued: SELECT regexp_substr("Bibliograph ic Details"."ISBN",'[^;]+') FROM "Physical Items"
*************************
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
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('SEL
SQL Issued: SELECT regexp_substr("Bibliograph
*************************
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot,
Fabiano
Fabiano
2859527478
Please replace string with column name, also test it thoroughly with multiple scenarios.