Logical SQL Execution (Preview)

oracle_analytics-execute_logical_sql

Input Schema
{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "type": "object",
  "properties": {
    "maxRows": {
      "type": "integer"
    },
    "query": {
      "type": "string"
    }
  }
}

Executes Oracle Analytics Logical SQL against the configured OAC server and streams results.

When to Use Logical SQL

Use the Logical SQL Execution tool when the ANSI SQL tool can't provide the required semantic behavior. For example, use Logical SQL for OAC time-series functions such as AGO, TODATE, and PERIODROLLING; advanced ranking and analytics; moving or running calculations; forecasting; trendline, cluster, or regression functions; model-aware default aggregation; or a true aggregation override.

Important Parameters
Parameter Type Notes
query string Logical SQL query. Required.
maxRows integer Maximum rows to stream. Keep this less than or equal to the query FETCH FIRST limit.

Example
{
  "jsonrpc": "2.0",
  "id": 40,
  "method": "tools/call",
  "params": {
    "name": "oracle_analytics-execute_logical_sql",
    "arguments": {
      "query": "SELECT \"Sales History Subject Area\".\"CHANNELS\".\"CHANNEL_DESC\" AS llm_0, \"Sales History Subject Area\".\"SALES\".\"AMOUNT_SOLD\" AS llm_1 FROM \"Sales History Subject Area\" ORDER BY llm_1 DESC FETCH FIRST 100 ROWS ONLY",
      "maxRows": 100
    }
  }
}

Logical SQL Rules to Follow

For standard analytical queries such as filtering, grouping, multiple measures, calculated fields, and Top-N results, use oracle_analytics-execute_oac_ansi_sql instead.

  • Use fully qualified column names: "Subject Area"."Table"."Column".
  • Use the quoted subject area name in the FROM clause.
  • Do not write manual joins. OAC resolves joins through the semantic model.
  • Do not write explicit GROUP BY for normal presentation-layer aggregation. OAC infers grouping from selected dimensions.
  • Include ORDER BY when using FETCH FIRST.
  • Keep row limits bounded with FETCH FIRST n ROWS ONLY.
  • Alias selected columns as llm_0, llm_1, and so on for deterministic downstream handling.

Example - Subject Area
SELECT
  "Sales History Subject Area"."CHANNELS"."CHANNEL_DESC" AS llm_0,
  "Sales History Subject Area"."SALES"."AMOUNT_SOLD" AS llm_1
FROM "Sales History Subject Area"
ORDER BY llm_1 DESC
FETCH FIRST 100 ROWS ONLY

Example - Dataset/XSA
SELECT
  XSA('admin'.'sales_data')."Orders"."Customer" AS llm_0,
  SUM(OVERRIDEAGGR(XSA('admin'.'sales_data')."Orders"."Amount")) AS llm_1
FROM XSA('admin'.'sales_data')
ORDER BY llm_1 DESC
FETCH FIRST 100 ROWS ONLY

Aggregation Guidance
  • If a measure's default aggregation is what you want, use the measure directly.
  • Use OVERRIDEAGGR only when intentionally overriding the default aggregation.
  • For XSA datasets, full column references must include the XSA expression, table name, and column name.
  • Do not mix columns from different subject areas or XSA datasets in the same query.
  • Use IN for subqueries. Avoid = with subqueries.

Time-Series Guidance
  • AGO, TODATE, and PERIODROLLING require valid time dimensions.
  • In time-series functions, the time level parameter may require two-part naming such as "Time"."Year" instead of the full subject-area prefix, depending on the model.
  • Validate time columns with oracle_analytics-describe_data before writing time-series expressions.