ksql报错

1104797209

SELECT t1.fSequence, t1.fSpecialty AS FSPECIALTY, t1.fMaterialType2 AS FMATERIALTYPE2, t1.fMaterialType AS FMATERIALTYPE, t1.fMaterialName AS FMATERIALNAME, t1.fModel AS FMODEL, t1.fUnit AS FUNIT, SUM(t1.fPlanNum) AS FPLANNUM, AVG(t1.fPlanPrice) AS FPLANPRICE, SUM(t1.fPlanAmount) AS FPLANAMOUNT, SUM(t1.fControlNum) AS FCONTROLNUM, AVG(t1.fControlPrice) AS FCONTROLPRICE, SUM(t1.fControlAmount) AS FCONTROLAMOUNT ,COALESCE(SUM(t6.FACTUALNUM), 0) AS FACTUALNUM, COALESCE(AVG(t6.FACTUALPRICE), 0) AS FACTUALPRICE, COALESCE(SUM(t6.FACTUALAMOUNT), 0) AS FACTUALAMOUNT, COALESCE(SUM(t6.FAPPROVENUM), 0) AS FAPPROVENUM, COALESCE(AVG(t6.FAPPROVEPRICE), 0) AS FAPPROVEPRICE, COALESCE(SUM(t6.FAPPROVEAMOUNT), 0) AS FAPPROVEAMOUNT, COALESCE(SUM(t6.FMATERNUM), 0) AS FMATERNUM, COALESCE(AVG(t6.FMATERPRICE), 0) AS FMATERPRICE, COALESCE(SUM(t6.FA2AMOUNT), 0) AS FA2AMOUNT  ,SUM(COALESCE(t7.fTotalAmount,0))+sum(COALESCE(t8.fSettleAmount,0))+sum(COALESCE(t9.fLoadQuitAmount,0)+ COALESCE (t10.fLeaseAmount,0)+ COALESCE(t11.fotherPay,0)) AS FTOTALAMOUNT,SUM (COALESCE(t7.fImportNum,0))+sum(COALESCE(t9.fleaseNum,0)) AS FIMPORTNUM,AVG(COALESCE(t7.fImportAmount,0)) AS FIMPORTAMOUNT  FROM PM_COST_CostPlanDetail t1 optional JOIN PM_COST_CostPlanMain t2 ON t2 = t1.fMasterID optional JOIN (SELECT SUM(t3.fPlanNum) AS FACTUALNUM, AVG(t3.fPlanPrice) AS FACTUALPRICE, SUM(t3.fPlanAmount) AS FACTUALAMOUNT, SUM(t3.fApproveNum) AS FAPPROVENUM, AVG(t3.fApprovePrice) AS FAPPROVEPRICE, SUM(t3.fApproveAmount) AS FAPPROVEAMOUNT, SUM(t3.fActualNum) AS FMATERNUM, AVG(t3.fActualPrice) AS FMATERPRICE, SUM(t3.fActualAmount) AS FA2AMOUNT, t3.fSpecialtyID AS fSpecialtyID, t3.fMaterialType2ID AS fMaterialType2ID, t3.fMaterialTypeID AS fMaterialTypeID, t3.fMaterialID AS fMaterialID, t3.fModel AS fModel FROM PM_COST_MonthMaterial t3 WHERE t3.fMonthDetailID IN (SELECT t4 FROM PM_COST_MonthDetail t4 JOIN PM_COST_MonthPlan t5 ON t5 = t4.fMasterID WHERE t5.fProjectID = :fProjectID) GROUP BY t3.fSpecialtyID, t3.fMaterialType2ID, t3.fMaterialTypeID, t3.fMaterialID, t3.fModel) t6 ON t6.fSpecialtyID = t1.fSpecialtyID AND (t6.fMaterialType2ID = t1.fMaterialType2ID OR t6.fMaterialType2ID IS null OR t1.fMaterialType2ID IS NULL) AND t6.fMaterialTypeID = t1.fMaterialTypeID AND t6.fMaterialID = t1.fMaterialID AND (t6.fModel = t1.fModel OR t6.fModel IS NULL OR t1.fModel IS NULL)optional JOIN (SELECT SUM(tb.fAmount) AS fTotalAmount, SUM(tb.fNum) AS fImportNum, AVG(tb.fPrice) AS fImportAmount, tb.fSpecialtyID AS fSpecialtyID, tb.fMaterialTypeID AS fMaterialTypeID, tb.fMaterialID AS fMaterialID, tb.fModel AS fModel, tb.fUnit AS fUnit FROM PM_STOR_ImportStore ta optional join PM_STOR_ImportStoreDetail tb on ta = tb.fMasterID WHERE ta.fBizState = 'tesFinished' AND ta.fProjectID = '0F289844B7F649D5A73D5F2D4CBBF95E' GROUP BY tb.fSpecialtyID, tb.fMaterialTypeID, tb.fMaterialID, tb.fModel, tb.fUnit) t7 ON t7.fSpecialtyID = t1.fSpecialtyID AND t7.fMaterialTypeID = t1.fMaterialTypeID AND t7.fMaterialID = t1.fMaterialID AND t7.fModel = t1.fModel AND t7.fUnit = t1.fUnit optional JOIN (SELECT SUM(tf.fSettleAmount) AS fSettleAmount, te.fSpecialtyID AS fSpecialtyID, te.fMaterialTypeID AS fMaterialTypeID, te.fMaterialID AS fMaterialID, te.fModel AS fModel, te.fUnit AS fUnit FROM PM_COST_MonthLabor tf optional JOIN PM_COST_MonthLaborDetail te on tf = te.fMasterID WHERE tf.fBizState = 'tesFinished' AND tf.fProjectID = '0F289844B7F649D5A73D5F2D4CBBF95E' GROUP BY te.fSpecialtyID, te.fMaterialTypeID, te.fMaterialID, te.fModel, te.fUnit) t8 ON t8.fSpecialtyID = t1.fSpecialtyID AND t8.fMaterialTypeID = t1.fMaterialTypeID AND t8.fMaterialID = t1.fMaterialID AND t8.fModel = t1.fModel AND t8.fUnit = t1.fUnit optional JOIN (SELECT SUM(COALESCE(tg.fLoadQuitAmount, 0) + COALESCE(tg.fTotalAmount, 0)) AS fLoadQuitAmount, SUM(COALESCE(tg.fLoadQuitNum, 0)) AS fleaseNum, tg.fSpecialtyID AS fSpecialtyID, tg.fMaterialTypeID AS fMaterialTypeID, tg.fMaterialID AS fMaterialID, tg.fModel AS fModel, tg.fUnit AS fUnit FROM PM_STOR_LeaseRetreat tg WHERE (tg.fStateID = '2' OR tg.fBizState = 'tesFinished') AND tg.fProjectID = '0F289844B7F649D5A73D5F2D4CBBF95E' GROUP BY tg.fSpecialtyID, tg.fMaterialTypeID, tg.fMaterialID, tg.fModel, tg.fUnit) t9 ON t9.fSpecialtyID = t1.fSpecialtyID AND t9.fMaterialTypeID = t1.fMaterialTypeID AND t9.fMaterialID = t1.fMaterialID AND t9.fModel = t1.fModel AND t9.fUnit = t1.fUnit optional JOIN (SELECT SUM(COALESCE(tg2.fLeaseAmount, 0)) AS fLeaseAmount, SUM(COALESCE(tg2.fLoadQuitNum, 0)) AS fleaseNum, tg2.fSpecialtyID AS fSpecialtyID, tg2.fMaterialTypeID AS fMaterialTypeID, tg2.fMaterialID AS fMaterialID, tg2.fModel AS fModel, tg2.fUnit AS fUnit FROM PM_STOR_LeaseRetreat tg2 WHERE (tg2.fStateID = '2' OR tg2.fBizState = 'tesFinished') AND tg2.fKind = '5' AND tg2.fProjectID = '0F289844B7F649D5A73D5F2D4CBBF95E' GROUP BY tg2.fSpecialtyID, tg2.fMaterialTypeID, tg2.fMaterialID, tg2.fModel, tg2.fUnit) t10 ON t10.fSpecialtyID = t1.fSpecialtyID AND t10.fMaterialTypeID = t1.fMaterialTypeID AND t10.fMaterialID = t1.fMaterialID AND t10.fModel = t1.fModel AND t10.fUnit = t1.fUnit optional JOIN (SELECT SUM(tp.fExpenditureAmount) + SUM(tp.fActualPay) AS fotherPay, tp.fSpecialtyID AS fSpecialtyID, tp.fResourceTypeID AS fMaterialTypeID, tp.fResourceID AS fMaterialID, tb.fModel AS fModel, tb.fUnit AS fUnit FROM PM_FUNDS_PayAmt tp optional JOIN PM_FUNDS_PayAmtDetail tb on tp = tb.fMasterID WHERE (tp.fKind = '4' OR tp.fKind = '5') AND tp.fStateID = '2' AND tp.fProjectID = '0F289844B7F649D5A73D5F2D4CBBF95E' GROUP BY tp.fSpecialtyID, tp.fResourceTypeID, tp.fResourceID, tb.fModel, tb.fUnit) t11 ON t11.fSpecialtyID = t1.fSpecialtyID AND t11.fMaterialTypeID = t1.fMaterialTypeID AND t11.fMaterialID = t1.fMaterialID AND t11.fModel = t1.fModel AND t11.fUnit = t1.fUnit WHERE t2.fProjectID = :fProjectID GROUP BY t1.fSequence, t1.fSpecialty, t1.fMaterialType2, t1.fMaterialType, t1.fMaterialName, t1.fModel, t1.fUnit ORDER BY t1.fSequence ASC

1104797209

结帖吧，表概念定义重复了一下