SQL help using Group By

[Ivan Smahin]

On 4/3/2011 7:34 PM, Steve Albin wrote:
> I have the following SQL:
>
> SELECT i.dInvoiceDate, i.nCollected,
>    i.dCollDate, i.cDescription, i.nid, billSum.tot_amt
>     FROM   Invoices i  inner join
>     	(SELECT sum(BillItems.nFixedCost) As tot_amt, j.nId As theId
> 		FROM	invoices j, Customers,BillItems, Lnk_InvTask
> 		where j.oCustPtr = Customers.RecID
> 		and BillItems.RecID = Lnk_InvTask.taskPtr
> 		and Lnk_InvTask.invPtr = j.RecID
> 		and Customers.nId = 14
> 		GROUP BY j.nId
> 		) As billSum  on i.nid = billSum.theId
>    ORDER BY i.dInvoiceDate
>
> I am trying to list my invoices along with a total amount derived from the individual items in the invoice (BillItems).  These are linked to the invoices via the Lnk_InvTask table.
>
> Every time I try to execute this SQL in VStudio, I get the following error:
> 	Kernel error: 0x51500. Identifier "billSum.theId" is not found.
>
> The sub-query works fine on its own.
>
> Any help will be appreciated.
>
>

Currently join with subquery result is not implemented.
But you may redesign your query in many ways.

For example:

SELECT
     i.dInvoiceDate,
     i.nCollected,
     i.dCollDate,
     i.cDescription,
     i.nid,
        (SELECT
         sum(BillItems.nFixedCost) As tot_amt
     FROM
         invoices j,
         Customers,
         BillItems,
         Lnk_InvTask
     where
         j.oCustPtr = Customers.RecID
         and BillItems.RecID = Lnk_InvTask.taskPtr
         and Lnk_InvTask.invPtr = j.RecID
         and Customers.nId = 14
     GROUP BY j.nId
         ) As billSum

FROM   Invoices i  inner join
ORDER BY
     i.dInvoiceDate





-- 
Best regards,
Ivan Smahin
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com