none
Need help on error: You tried to execute a query that does not include the specified expression

    Question

  • I have the following query below that I'm getting an error on. The error says, "You tried to execute a query that does not include the specified expression 'OrderNumber' as part of an aggregate function". I'm sure this is probably something really simple related to the nested Select statement but I've been digging into nested Select statements and have yet to find the answer. I've also looked up this error and read a ton of articles, but I've yet to find anything directly related to what I'm trying to do here:( 

    SELECT Transactions.Description, Transactions.OrderNumber, Val(Mid([Description],InStr([Description],"#")+1)) AS [Original Order Number], (Select OrderDate from Orders AS S1 where [Original Order Number]=Orders.OrderNumber) AS [Original Order Date]
    FROM Transactions, Orders
    WHERE (((Transactions.CarryForward)=-1) AND ((Transactions.Description) Like "*Credit xfer from*"))
    GROUP BY Transactions.Description, Transactions.OrderNumber, [Original Order Date];
    

    Friday, March 04, 2011 4:17 PM

Answers

  • As you are not aggregating any data you don't need a GROUP BY clause, so you can delete it, or change it to an ORDER BY Clause if that's how you want the returned rows sorted.  If using the query as a basis for a report, however, don't sort the query, but use the report's internal sorting and grouping mechanism.

    Also I suspect you'll  need to repeat the expression to correlate the subquery with the outer query.  So this gives us:

    SELECT Transactions.Description, Transactions.OrderNumber, Val(Mid([Description],InStr([Description],"#")+1))
    AS [Original Order Number],
        (SELECT OrderDate
         FROM Orders AS S1
         WHERE S1.OrderNumber =
         Val(Mid([Description],InStr([Description],"#")+1))) AS [Original Order Date]
    FROM Transactions, Orders
    WHERE Transactions.CarryForward = TRUE
    AND Transactions.Description Like "*Credit xfer from*";

    However there is no explicit join between the tables in the outer query, either by means of a JOIN clause or by means of a join criterion in the WHERE clause, so the Cartesian product of the two tables, subject to the restrictions on the CarryForward and Description columns, will be returned.  I'd guess you might really want to use the correlation criterion in the subquery a the join criterion in the outer query, in which case the subquery should be unnecessary:

    SELECT Transactions.Description, Transactions.OrderNumber, Val(Mid(Transactions.Description,InStr(Transactions.Description,"#")+1))
    AS [Original Order Number], Orders.OrderDate AS [Original Order Date]
    FROM Transactions INNER JOIN Orders
    ON Orders.OrderNumber =  Val(Mid(Transactions.Description,InStr(Transactions.Description,"#")+1))) WHERE Transactions.CarryForward = TRUE
    AND Transactions.Description Like "*Credit xfer from*";

    On the other hand my guess might be wrong.  The fact that you have an OrderNumber column in Transaction, presumably referencing the key of Orders, but at the same time are engineering a join on a substring of the Description column makes it difficult to understand the model being employed here.


    Ken Sheridan, Stafford, England
    • Marked as answer by Bessie Zhao Friday, March 11, 2011 10:20 AM
    Friday, March 04, 2011 5:21 PM