OBIEE 11g: Getting data from Oracle BI Presentation Services using HTTP including null values

Question

We have an on-prem Oracle BI Enterprise Edition 11g set up in our company. As stated in the documentation at

https://docs.oracle.com/cd/E14571_01/bi.1111/e16364/apiwebintegrate.htm

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,

User_I6REB · Answer

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

Gianni Ceresa · Answer

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.

User_I6REB · Answer

@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