Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

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

Received Response
35
Views
4
Comments
Rank 1 - Community Starter

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,

Tagged:

Welcome!

It looks like you're new here. Sign in or register to get started.

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 …

  • Rank 1 - Community Starter

    @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

  • 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.

  • Rank 1 - Community Starter

    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

Welcome!

It looks like you're new here. Sign in or register to get started.