I’m running this query:

SELECT    Item.id,
          Item.Itemid AS sku,
          Item.displayname AS name,
          NULL AS kit_contents,
          InventoryItemLocations.quantityavailable AS available_inventory,
          Item.custitem37 AS old_sku,
          Item.cost,
          Item.custitem_featured_image AS photo_url
FROM      Item
JOIN      InventoryItemLocations
  ON InventoryItemLocations.Item = Item.id
WHERE     InventoryItemLocations.Location = '334'
AND       Item.itemtype = 'InvtPart'
AND       InventoryItemLocations.quantityavailable > 0
AND       Item.isinactive = 'F'

UNION

SELECT    DISTINCT ParentItem.id,
          ParentItem.Itemid AS sku,
          ParentItem.displayname AS name,
          MemberItem.itemtype AS kit_contents,
          NULL AS available_inventory,
          NULL AS old_sku,
          NULL AS cost,
          NULL AS photo_url
FROM      Item AS ParentItem
JOIN      KitItemMember
  ON KitItemMember.ParentItem = ParentItem.ID
JOIN      Item AS MemberItem
  ON MemberItem.ID = KitItemMember.Item
WHERE     ParentItem.Itemid != 'AG Test Item Group'
AND       ParentItem.itemtype = 'Kit'
AND       (NOT EXISTS(SELECT *
                      FROM    InventoryItemLocations
                      JOIN    KitItemMember
                        ON InventoryItemLocations.Item = KitItemMember.Item
                      WHERE   KitItemMember.ParentItem = ParentItem.ID
                      AND     InventoryItemLocations.Location = '334'
                      AND     InventoryItemLocations.quantityavailable = 0)
            OR MemberItem.itemtype = 'Kit')
ORDER BY  name

I can run it successfully in SuiteQL but I cannot run it via a REST request. When I try to do it with a REST request, I get:

An unexpected error occurred. Error ID: l1mnodbx1304sy961081b Error code: UNEXPECTED_ERROR

I can run the individual queries separately just fine. Is there a size limit on the query that is sent via the REST endpoint? I’m guessing that I’ll be able to run both of these queries and knit the results together and sort client side to simulate the actual UNION. But I sure would like to be able to run this as one query programmatically.

@Wes Gamble Sometimes wrapping a UNION in an outer SELECT helps resolve issues. For example:

SELECT * FROM (

SELECT    Item.id,
          Item.Itemid AS sku,
          Item.displayname AS name,
          NULL AS kit_contents,
          InventoryItemLocations.quantityavailable AS available_inventory,
          Item.custitem37 AS old_sku,
          Item.cost,
          Item.custitem_featured_image AS photo_url
FROM      Item
JOIN      InventoryItemLocations
  ON InventoryItemLocations.Item = Item.id
WHERE     InventoryItemLocations.Location = '334'
AND       Item.itemtype = 'InvtPart'
AND       InventoryItemLocations.quantityavailable > 0
AND       Item.isinactive = 'F'

UNION

SELECT    DISTINCT ParentItem.id,
          ParentItem.Itemid AS sku,
          ParentItem.displayname AS name,
          MemberItem.itemtype AS kit_contents,
          NULL AS available_inventory,
          NULL AS old_sku,
          NULL AS cost,
          NULL AS photo_url
FROM      Item AS ParentItem
JOIN      KitItemMember
  ON KitItemMember.ParentItem = ParentItem.ID
JOIN      Item AS MemberItem
  ON MemberItem.ID = KitItemMember.Item
WHERE     ParentItem.Itemid != 'AG Test Item Group'
AND       ParentItem.itemtype = 'Kit'
AND       (NOT EXISTS(SELECT *
                      FROM    InventoryItemLocations
                      JOIN    KitItemMember
                        ON InventoryItemLocations.Item = KitItemMember.Item
                      WHERE   KitItemMember.ParentItem = ParentItem.ID
                      AND     InventoryItemLocations.Location = '334'
                      AND     InventoryItemLocations.quantityavailable = 0)
            OR MemberItem.itemtype = 'Kit')
ORDER BY  name

)

egads, that's an unfortunate workaround

That did the trick. Thanks. And I went ahead and left my queries broken up for debugging purposes later. So:

def self.item_inventory_for_warehouse(warehouse_id:)
    <<~STOP
      SELECT *
      FROM (
        #{item_inventory_for_warehouse_part1(warehouse_id: warehouse_id)}
        UNION
        #{item_inventory_for_warehouse_part2(warehouse_id: warehouse_id)}
      )
      ORDER BY name
  end