Categories
- All Categories
- 93 Oracle Analytics News
- 7 Oracle Analytics Videos
- 14.2K Oracle Analytics Forums
- 5.3K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 53 Oracle Analytics Trainings
- 59 Oracle Analytics Data Visualizations Gallery
- 2 Oracle Analytics Data Visualizations Challenge
- 4 Oracle Analytics Career
- 4 Oracle Analytics Industry
- Find Partners
- For Partners
OBIEE 11g: Getting data from Oracle BI Presentation Services using HTTP including null values
We have an on-prem Oracle BI Enterprise Edition 11g set up in our company. As stated in the documentation at
you can pass a http request query to Oracle BI and get back a CSV result. For example I can successfully get back a list of project numbers in CSV by passing the below URL (without the white spaces) just pasting it in my browser.http://<url of local instance of OBIEE server>/analytics/saw.dll?Go&SQL=
SELECT
"Project - Performance".Project"."Project Number"
FROM
"Project - Performance"
&Action=Extract&Format=CSV
My problem is that if the project number value field is null, it does not get included in the result. In normal Oracle SQL I am familiar using something like IFNULL("Project - Performance".Project"."Project Number",0) to ensure it gets included in the result, but this doesn't seem to work when using this http request method. How can I restructure the query sql so null values get included?
Thanks,
Answers
-
An OBIEE 11g? Somebody should start planning an upgrade ;-)
Are you sure that NULLs aren't part of the result? They would just be empty line because you select a single thing that can be null…
Keeping it simple: work on your LSQL query inside OBIEE first, build an analysis, get the result you want, when you get it there, then you should have the exact query you need. But when returning a single column, getting NULLs is a bit … meaningless …
0 -
@Gianni Ceresa an oracle upgrade is at least 4 years away so we have to live with it. sorry I tried to use only the minimum columns just for illustration purposes and so might have been unclear with what i wanted. In the OBIEE query editor the SQL below produces a result showing project numbers with budgets. if you remove the budget column, the result shows more project numbers assuming it includes those that do not have budgets assigned (perhaps null). i want to get an output where it includes all project numbers even those without budgets (showing as zero or blank). but also i need the proper query translated so it works with the http query using Go&SQL.
http://<url of local instance of OBIEE server>/analytics/saw.dll?Go&SQL=
SELECT
"Project - Performance".Project"."Project Number",
"Project - Performance".Project"."Budget"
FROM
"Project - Performance"
&Action=Extract&Format=CSV
0 -
Ok, this is a different need.
To get what you are asking, you should modify the join in the RPD to be an outer join. Currently your project numbers are filtered by the presence of a budget because you probably have an inner join between the project table and the budget table. If you want all the projects, including those without a budget, you need to model the project table as being an outer join to the budget table: this will preserve projects even if they don't have a budget.
Trying to convert NULLs to 0 only work if you have a NULL in the first place, with an inner join you don't have the NULL, therefore there was nothing to convert to 0.
0 -
it's quite an ancient system but an upgrade is at least 5 years away so we have to live with it. sorry, i simplified the query above as i didn't want to include more columns than necessary to illustrate. amending as below. If there are projects that do not have any Organization values (null?) then i still want them listed in the results but will show blank in the CSV output. In LSQL I would use IFNULL("Project - Performance".Project"."Organization",0) so it would show a zero in place of Organization where there is no value. But in the http query it doesn't understand IFNULL. How can I get those project numbers to show up still even though they don't have organisations?
http://<url of local instance of OBIEE server>/analytics/saw.dll?Go&SQL=
SELECT
"Project - Performance".Project"."Project Number",
"Project - Performance".Project"."Organization"
FROM
"Project - Performance"
&Action=Extract&Format=CSV
0