Does any one know what am doing wrong here? I am trying to convert a sql statement to Domo Mysql but I am getting an error
Original sql:
SELECT
ta.Item
, ta.Warehouse
, ta.ShipDate
, tb.ActivityDate
FROM
copy_of_cuts_w_forecast ta
JOIN
RunningCases tb
ON tb.Item = ta.Item
AND tb.Warehouse = ta.Warehouse
AND tb.ActivityDate = (SELECT TOP 1
ActivityDate
FROM
InventoryAvailability tb
WHERE
Item = ta.Item
AND Warehouse = ta.Warehouse
AND ActivityDate <= ta.ShipDate
ORDER BY
ActivityDate DESC);
How I tried to convert:
SELECT
`Item Trim_Fill Rate`,
`Trim Whse`,
`Ship Date`
FROM
`copy_of_cuts_w_forecast`
JOIN
`inventoryavailability`
ON `Item Trim_Fill Rate` = `Item`
AND `Trim Whse` = `Whse`
AND `ActivityDate` = ( MAX 1
`ActivityDate`
FROM
`RunningCases`
WHERE
`Item` = `Item Trim_Forecast`
AND `Whse` = `Trim Whse`
AND `ActivityDate` <= `Ship Date`
ORDER BY
`ActivityDate` DESC)
end