Logical SQL Reference for MCP Server Development (Preview)

This topic contains information specific to using Logical SQL with Oracle Analytics Cloud MCP Server development tasks.

Column Naming Convention

Always use three-part fully qualified names with llm_ prefixed aliases:

"Subject Area"."Table"."Column" AS llm_0

For XSA datasets:

XSA('namespace'.'dataset')."Table"."Column" AS llm_0

Query Templates

Basic Query

SELECT 
  "Subject Area"."Dimension"."Attribute" AS llm_0,
  "Subject Area"."Facts"."Measure" AS llm_1
FROM "Subject Area"
WHERE "Subject Area"."Time"."Year" = 2024
ORDER BY llm_1 DESC
FETCH FIRST 100 ROWS ONLY

XSA Dataset Query

SELECT 
  XSA('[email protected]'.'Dataset')."Table"."Category" AS llm_0,
  SUM(OVERRIDEAGGR(XSA('[email protected]'.'Dataset')."Table"."Amount")) AS llm_1
FROM XSA('[email protected]'.'Dataset')
ORDER BY llm_1 DESC
FETCH FIRST 50 ROWS ONLY

Time Series with AGO

SELECT 
  "SA"."Time"."Month" AS llm_0,
  "SA"."Facts"."Revenue" AS llm_1,
  AGO("SA"."Facts"."Revenue", "Time"."Year", 1) AS llm_2
FROM "SA"
WHERE "SA"."Time"."Year" IN (2023, 2024)
ORDER BY llm_0
FETCH FIRST 1000 ROWS ONLY

Key Functions

Name	Syntax	Description
`AGO`	`AGO(measure, time_level, offset)`	Prior period comparison.
`TODATE`	`TODATE(measure, time_level)`	Period-to-date aggregation.
`TOPN`	`TOPN(measure, n)`	Top-N filtering.
`BOTTOMN`	`BOTTOMN(measure, n)`	Bottom-N filtering.
`RANK`	`RANK(measure)`	Calculate rank.
`OVERRIDEAGGR`	`OVERRIDEAGGR(measure)`	Override default aggregation.
`FILTER`	`FILTER(measure USING condition)`	Conditional aggregation.

Important Rules

No manual joins - Oracle Analytics Cloud handles joins automatically.
Three-Part Naming - Always use "SubjectArea"."Table"."Column".
Column Aliases - Prefix with llm_, for example llm_0 or llm_1.
Time Level in AGO/TODATE - Use two-part naming "Table"."Column". Don't use three-part naming.
FETCH Clause - Always include FETCH FIRST n ROWS ONLY.