Utilising Logical SQL in Oracle Fusion Cloud Applications
How Does Logical SQL Work?
Logical SQL interacts with the Oracle BI Server, which translates user queries into requests against presentation layer objects, such as subject areas in the business intelligence repository. When you run a report or dashboard, you don’t need to know how tables are joined or which data source powers the results. The BI Server handles all the physical complexity and returns unified, accurate answers for your request.
Key Features and Syntax
-
SELECT statement: Central to Logical SQL, allowing you to specify columns, filters, groups, and sorts.
-
Automatic joins: You never need to write join logic; relationships are defined in the repository and applied automatically.
-
Aggregations: SUM, AVG, COUNT, MEDIAN, TOPN, and many advanced analytics functions are supported.
-
Variables: Use session/system variables such as
VALUEOF(NQSESSION.USER)to personalize results. -
Subqueries & set operators: Logical SQL supports UNION, INTERSECT, and EXCEPT to broaden analysis scope.
-
Time Series Functions: Functions like AGO, TODATE and PERIODROLLING make period-over-period and YTD reporting easy.
-
Advanced expressions: CASE, EVALUATE, and custom database functions can be embedded.
Example Logical SQL query:
SELECT_PHYSICAL ExpExpenseCategoryCode
, ExpMerchantName
, SUM(ExpOrigReimbursableAmount) AS TotalExpOrigReimbursableAmount
, ExpReceiptCurrencyCode
FROM "FscmTopModelAM.FinExmEntrySharedAM.ExpensePVO"
WHERE ExpOrigReimbursableAmount > 1000
AND ExpMerchantName IS NOT NULL
GROUP
BY ExpExpenseCategoryCode
, ExpMerchantName
, ExpReceiptCurrencyCode
SELECT_PHYSICAL *
FROM "HcmTopModelAnalyticsGlobalAM.AssignmentAM.AssignmentFactPVO"
ORDER
BY AssignmentPEOAssignmentNumber
FETCH FIRST 10 ROWS ONLY
Need some assistance?
Ready to bring clarity to customer, supplier and employee communications? With BI Publisher, we specialise in customising customer, supplier, and employee-facing documents such as invoices, purchase orders, payslips, and statements to meet your exact branding, compliance, and operational needs. From layout design to data logic enhancements, we help you deliver clear, consistent, and impactful outputs across your organization. Contact us today to discover how our expertise in BI Publisher can streamline your document processes and elevate your business communications.
