当我尝试通过ODBC提取数据时，出现此SQL脚本的语法错误，我不知道如何/为什么

Question

我正在运行以下脚本，但遇到错误。它基本上是两个独立脚本的组合。所以我认为其中一个JOIN已关闭，但不知道在哪里。我正在通过winsql中的ODBC从我们的ERP中提取数据。

此外，我将如何添加月份？也许一些东西：

TO_CHAR(Month(t.TRANDATE,'YYYY-MM-DD')) AS CreatedMonth 

已经得到了问题的查询是：

SELECT 
    t.TRANSACTION_TYPE AS TranType 
    ,tl.ACCOUNT_ID AS AcctId 
    ,a.NAME AS Account 
    ,a.TYPE_NAME AS AcctType 
    ,t.TRANSACTION_ID AS DCOGSTranId 
    ,t.TRANID AS DCOGSNo 
    ,TO_CHAR(t.TRANDATE,'YYYY-MM-DD') AS DCOGSCreateDt 
    ,TO_CHAR(t.TRANDATE,'YYYY-MM-DD') AS CDate 
    ,t.CREATED_BY_ID AS DCOGSCreatedById 
    ,tl.SUBSIDIARY_ID AS SubSidId 
    ,ss.NAME AS SubSidiary 
    ,tl.LOCATION_ID AS LocId 
    ,t.ENTITY_ID AS CustomerId 
    ,tl.ITEM_ID AS ItemId 
    ,NVL(SUM(tl.GROSS_AMOUNT),0) AS DirectCOGS$ 
    ,NVL(SUM(tl.ITEM_COUNT),0) AS DirectCOGSQty 
    ,ilm.ITEM_ID AS ItemId 
    ,ss.NAME AS SubSidiary 
    ,ilm.LOCATION_ID AS LocId 
    ,TO_CHAR(ed.ExpireDt,'YYYY-MM-DD') AS ExpireDt 
    ,TO_CHAR(CURRENT_DATE,'YYYY-MM-DD') AS CDate 
    ,NVL(ilm.AVERAGE_COST,0)*NVL(u.CONVERSION_RATE,1) AS AvgCost 
    ,NVL(ilm.LAST_PURCHASE_PRICE,0)*NVL(u.CONVERSION_RATE,1) AS LastPurchPrice 
    ,NVL(ilm.AVERAGE_COST,0) * NVL(ilm.ON_HAND_COUNT,0) AS OnHandCost$ 
    ,NVL(ilm.ON_HAND_COUNT,0)/NVL(u.CONVERSION_RATE,1) AS OnHandQty 
    ,NVL(bin.BinOnHandQty,0)/NVL(u.CONVERSION_RATE,1) AS BinOnHandQty 
    ,NVL(ilm.AVERAGE_COST,0) * (NVL(ilm.IN_TRANSIT_COUNT,0)) AS InTranCost$ 
    ,NVL(ilm.IN_TRANSIT_COUNT,0)/NVL(u.CONVERSION_RATE,1) AS InTranQty 
    ,NVL(ilm.AVERAGE_COST,0) * (NVL(ilm.AVAILABLE_COUNT,0)) AS AvailCost$ 
    ,NVL(ilm.AVAILABLE_COUNT,0)/NVL(u.CONVERSION_RATE,1) AS AvailQty 
    ,NVL(bin.BinAvailQty,0)/NVL(u.CONVERSION_RATE,1) AS BinAvailQty 
    ,NVL(ilm.AVERAGE_COST,0) * (NVL(ilm.ON_ORDER_COUNT,0)) AS OnOrderCost$ 
    ,NVL(ilm.ON_ORDER_COUNT,0)/NVL(u.CONVERSION_RATE,1) AS OnOrderQty 
    ,NVL(ilm.AVERAGE_COST,0) * (NVL(ilm.QUANTITYBACKORDERED,0)) AS BackOrdrdCost$ 
    ,NVL(ilm.QUANTITYBACKORDERED,0)/NVL(u.CONVERSION_RATE,1) AS BackOrdrdQty 
FROM TRANSACTIONS t 
JOIN TRANSACTION_LINES tl ON t.TRANSACTION_ID = tl.TRANSACTION_ID 
LEFT JOIN ITEMS i ON tl.ITEM_ID = i.ITEM_ID 
LEFT JOIN TRANSACTIONS t2 ON t.CREATED_FROM_ID = t2.TRANSACTION_ID 
LEFT JOIN ACCOUNTS a ON tl.ACCOUNT_ID = a.ACCOUNT_ID 
LEFT JOIN SUBSIDIARIES ss ON tl.SUBSIDIARY_ID = ss.SUBSIDIARY_ID 
WHERE tl.ACCOUNT_ID = 128 
FULL OUTER JOIN ITEM_LOCATION_MAP ilm ON tl.ITEM_ID = ilm.ITEM_ID 
JOIN ITEMS i ON ilm.ITEM_ID = i.ITEM_ID 
LEFT JOIN UOM u ON i.PURCHASE_UNIT_ID = u.UOM_ID 
LEFT JOIN PRIMARY_SUBSIDIARYS_MAP psm ON ilm.ITEM_ID = psm.ITEM_ID 
LEFT JOIN SUBSIDIARIES ss ON psm.SUBSIDIARY_ID = ss.SUBSIDIARY_ID 
LEFT JOIN (
    SELECT ITEM_ID, LOCATION_ID, MIN(EXPIRATION_DATE) AS ExpireDt FROM INVENTORY_NUMBER 
    WHERE EXPIRATION_DATE IS NOT NULL 
    GROUP BY ITEM_ID, LOCATION_ID) ed ON ilm.ITEM_ID = ed.ITEM_ID AND 
    ilm.LOCATION_ID = ed.LOCATION_ID 
LEFT JOIN (
    SELECT ITEM_ID, LOCATION_ID, SUM(ON_HAND_COUNT) AS BinOnHandQty, 
    SUM(AVAILABLE_COUNT) AS BinAvailQty 
    FROM BIN_NUMBER_COUNTS 
    GROUP BY ITEM_ID, LOCATION_ID) bin ON ilm.ITEM_ID = bin.ITEM_ID AND 
    ilm.LOCATION_ID = bin.LOCATION_ID 
    WHERE (ilm.ON_HAND_COUNT != 0 OR ilm.IN_TRANSIT_COUNT != 0 OR 
    ilm.ON_ORDER_COUNT != 0 OR ilm.QUANTITYBACKORDERED != 0) 
GROUP BY 
    t.TRANSACTION_TYPE 
    ,tl.ACCOUNT_ID 
    ,a.NAME 
    ,a.TYPE_NAME 
    ,t.TRANSACTION_ID 
    ,t.TRANID 
    ,t.TRANDATE 
    ,t.CREATED_BY_ID 
    ,tl.SUBSIDIARY_ID 
    ,ss.NAME 
    ,tl.LOCATION_ID 
    ,t.ENTITY_ID 
    ,tl.ITEM_ID 

Answer 1

真的应该张贴的错误是什么来帮助我们...但

LEFT JOIN SUBSIDIARIES ss ON tl.SUBSIDIARY_ID = ss.SUBSIDIARY_ID 
WHERE tl.ACCOUNT_ID = 128 
FULL OUTER JOIN ITEM_LOCATION_MAP ilm ON tl.ITEM_ID = ilm.ITEM_ID 

更换与AND在哪里。

其中是一个单独的子句，而不是连接语句的一部分。您可以使用和键1 =键1和键2 =键2和键3 ='嗨'