ORDER BY导致查询不起作用？

问题描述：

我有一个在访问中按预期工作的查询。我已经“转换”它为一个它不想工作的网络系统。但是，当我删除ORDER BY（在所发布示例的最底部）时，该查询正常工作。ORDER BY导致查询不起作用？

我不确定可能导致问题的原因。出现错误消息是： Expected lexical element not found:) There was a problem parsing the table names after the FROM keyword in your SELECT statement. -- Location of error in the SQL statement is: 812 (line: 17 column: 72)

我不想张贴整个查询，因为它是长的（除非被要求），但在底线出现问题：

SELECT TOP 5 [90Days].PRODUCT, [90Days].product2, [90Days].pcustkey, [90Days].[90DayThroughput], TABLETHREE.SumOfLQUAN AS [Current Stock], (ROUND([SumOfLQUAN]/[90DayThroughput],3))*90 AS [DaysRemaining] FROM 
    (
     SELECT TABLEONE.PRODUCT, Sum(TABLEONE.QUANTITY) AS [90DayThroughput], TABLETWO.product2, TABLETWO.pcustkey FROM TABLETWO INNER JOIN TABLEONE ON TABLETWO.PRODCODE = TABLEONE.PRODUCT WHERE 
     (
      (
       (TABLEONE.DATE)>='$date'-90 
      ) 
      AND 
      (
       (TABLEONE.type)='I11' Or (TABLEONE.type)=' 11' 
      ) 
      AND 
      (
       (TABLETWO.POWNER)='$custCode' 
      ) 
     ) 
     GROUP BY TABLEONE.PRODUCT, TABLETWO.product2, TABLETWO.pcustkey ORDER BY Sum(TABLEONE.QUANTITY) DESC

任何帮助将不胜感激。如果需要，我可以提供更多的查询。

也许你应该说'ORDER BY [90DayThroughput]'？不知道... –

如果您使用Codeigniter，我会建议您在查询中使用Active Record Database Pattern。它更简单。 – JobSam

@MikeRobinson这导致了完全相同的问题:(感谢帮助反正 – Jake

答

我认为你缺少某些查询的，如果它是之前
添加顺序连接没有努力TABLETHREE

香港专业教育学院创建了下面的，所以我可以运行查询

TRY 
DROP TABLE #Table1; 
CATCH ADS_SCRIPT_EXCEPTION 
IF __errcode = 7112 THEN 
--Do Nothing 
ELSE 
RAISE; // re-raise the exception 
END IF; 
END TRY; 

TRY 
DROP TABLE #Table2; 
CATCH ADS_SCRIPT_EXCEPTION 
IF __errcode = 7112 THEN 
--Do Nothing 
ELSE 
RAISE; // re-raise the exception 
END IF; 
END TRY; 

Select 'AAA' as Product, 12 as Quantity, 'I11' as Type, curdate() as "Date" 
into #Table1 
from System.iota; 

Select 'AAA' as Product, 'AAA111' as CustKey, '$custCode' as POwner, 'MyPRODCODE' as PRODCODE, 'KEY' as pcustkey 
into #Table2 
from System.iota;

我已注释掉TABLETHREE的东西
您需要在您的群组后通过
“90Days”和iv'e通过ORDER BY [90Days]更改顺序。[90DayThroughput] DESC

SELECT TOP 5 
[90Days].PRODUCT, 
[90Days].product2, 
[90Days].pcustkey, 
[90Days].[90DayThroughput] 
--,#Table3.SumOfLQUAN AS [Current Stock], 
--(ROUND([SumOfLQUAN]/[90DayThroughput],3))*90 AS [DaysRemaining] 
FROM 
    (
     SELECT tb1.PRODUCT, 
     Sum(tb1.QUANTITY) AS [90DayThroughput], 
     tb2.PRODCODE as product2, 
     tb2.pcustkey 
     FROM #Table2 tb2 
     INNER JOIN #Table1 tb1 ON tb2.PRODCODE = tb1.PRODUCT WHERE 
     (
      (
       (tb1.DATE)>=curdate()-90 
      ) 
      AND 
      (
       (tb1.type)='I11' Or (tb1.type)=' 11' 
      ) 
      AND 
      (
       (tb2.POWNER)='$custCode' 
      ) 
     ) 
     GROUP BY tb1.PRODUCT, tb2.PRODCODE, tb2.pcustkey 
    )"90Days" 
ORDER BY [90Days].[90DayThroughput] DESC

ORDER BY导致查询不起作用？

相关推荐