Logical SQL more comparable to Oracle SQL

Karen Costakes

Description

Many times OTBI  logical sql is needed to pull the appropriate data from the subject areas and join multiple subject areas as needed into one subject area.

However, using logical sql can be tedious and daunting based on syntax requirements and the need to alias tables multiple times.  It would be incredibly helpful if logical sql more closely resembled Oracle SQL or if there was a very detailed document on certain 

Use Case and Business Need

Many times business users that know how to use OTBI, but can't get all of the data they need from one subject area (IE they need data from benefits and payroll).  They can create separate OTBI analysis to get the appropriate data, but when they combine them, the way they are joined is not what is needed.   They could use logical sql, but it is tricky because it has some idiosyncrasies in required syntax that make it even more challenging than a standard BI publisher report - making it inaccessible to most users.

Original Idea Number: 9c57822b2f

User_BUU5W

I need to update an existing OTBI (non-Logical SQL) to use Logical SQL and finding out how to do this without rebuilding the whole OTBI from scratch is quite a challange.
My analysis to update just contains "Payables Invoices - Transactions Real Time" SA and my change is to include the {check date} from "Payables Payments - Disbursements Real Time". Using a join between the 2 subject areas would yield only paid invoices and I need to pick up Paid and Unpaid invoices and hence the need for an outer join between the subject areas and hence Logical SQL.
I have got the logical SQL working and it yields paid and unpaid invoices, but I don't want to rebuild the whole analysis from scratch with formatting, etc. Any ideas?
I did try using an "inline select" to just add in the check date from "Payables Payments - Disbursements Real Time", using invoice_id as the joining column. Any ideas on how to use "inline selects" in OTBI analysis?
Many thanks, Chris.

Brendan J. Doyle-Oracle

Hi there,

Thank you for your idea. I am relocating it to our Transactional Reporting (OTBI) Idea Lab for proper review: https://cloudcustomerconnect.oracle.com/resources/b2df648d2a/summary 

Best,

Brendan J. Doyle