Ad-hoc reporting API query fails when joining standard and custom ERP tables

BA/BI Snowflake
, , , , , , , ,
1

Our Snowflake 7.0 ad-hoc reporting API queries are failing when we try to join standard ERP tables with custom tables we’ve created. The error indicates ambiguous column names, but we’re not sure how to properly alias columns in the API query payload.

The issue involves column aliasing in joins, custom table schema review, and API query payload structure. We can run these queries successfully in SQL workbench with explicit aliases, but translating that to the API’s JSON query format isn’t working.

Error:


Query execution failed
"Column 'id' is ambiguous"
"Found in: orders (standard), custom_orders_ext (custom)"

How do we properly structure API query payloads with column aliases for joins between standard and custom tables?

2

The reporting API requires explicit table prefixes for all columns when joining multiple tables. In your query payload’s fields array, use dot notation like orders.id and custom_orders_ext.id instead of just id. This disambiguates columns with the same name across tables.

3

One thing that caught me: custom tables need explicit schema qualification in the API payload. Standard ERP tables use default schema, but custom tables might be in a different schema namespace. Use schema.table.column format for custom tables if they’re not in the default schema. Check your custom table schema with the metadata API first.

4

Here’s the complete solution for joining standard and custom ERP tables in ad-hoc reporting API queries:

Column Aliasing in Joins: Use explicit table.column notation in the fields array:


{
  "fields": [
    "orders.id",
    "orders.order_date",
    "custom_orders_ext.priority_score"
  ]
}

Never use bare column names like id when joining multiple tables. The API won’t auto-resolve ambiguity like some SQL engines do.

Custom Table Schema Review: First, verify your custom table’s schema location:


GET /api/metadata/tables/custom_orders_ext
// Check response for schema: "custom_schema" or "default"

If custom tables are in a non-default schema, use full qualification:


"fields": ["custom_schema.custom_orders_ext.priority_score"]

API Query Payload Structure: Structure joins using the joins array with explicit conditions:


{
  "from": "orders",
  "fields": [
    "orders.id",
    "orders.order_date",
    "custom_orders_ext.priority_score"
  ],
  "joins": [
    {
      "table": "custom_orders_ext",
      "type": "LEFT",
      "on": {
        "left": "orders.id",
        "right": "custom_orders_ext.order_id"
      }
    }
  ]
}

Key points: specify join type (INNER, LEFT, RIGHT), use table.column format in the on condition, and include schema qualification for custom tables if needed. For multiple joins, add additional objects to the joins array. Each join builds on the previous result set.

Best practice: review your custom table naming conventions. If you’re frequently joining custom extensions to standard tables, consider prefixing custom column names (e.g., ext_id, ext_priority) to avoid ambiguity. This reduces the need for verbose table qualification in every query. With proper payload structure and schema qualification, your joins should execute successfully.

5

Also check your custom table schema. If it mirrors standard table column names without good reason, consider renaming custom columns to avoid ambiguity. For example, use ext_id instead of id in your custom extension tables. This makes queries cleaner and reduces the need for constant aliasing.

6

The join syntax in the API payload uses a joins array with explicit join conditions. Each join object needs table, type (inner/left/right), and on with left and right column specifications. Make sure you’re using the full table.column format in the join condition as well as in the select fields.