Board index » delphi » Working on Local SQL Calculation for 2 Weeks

Working on Local SQL Calculation for 2 Weeks

I'm using DeskTop Delphi 2 and am trying to run a query that uses a
constant value from another table for a calculation. Below is the
statement.

        Select Sum(Total1 + Total2)/2, Table1.QtyRan *
Table2.PartsPlace,                              Module from Table1, Table2 where RunDate
between
        :FromDate and :ToDate
        Group By Module

Table2 is a list of constant values for the amount of parts used for
each Module.Table1 and Table2 both have the indexed field 'Module'. When
the query runs I want to use the appropriate constant for each Module
returned in the result set to get the product of QtyRan and PartsPlaced.
Where am I going wrong?
                        All advice is greatly appreciated, Thanks

 

Re:Working on Local SQL Calculation for 2 Weeks


Try something like

SELECT (T1.QtyRan * T2.PartsPlace) AS TheProduct
FROM Table1 T1 JOIN Table2 T2
ON (T1.Module = T2.Module)

Bill

--

Bill Todd - TeamB
(TeamB cannot respond to email questions. To contact me
 for any other reason remove nospam from my address.)

Re:Working on Local SQL Calculation for 2 Weeks


Quote
>    Select Sum(Total1 + Total2)/2, Table1.QtyRan *
>Table2.PartsPlace,                          Module from Table1, Table2 where RunDate
>between
>    :FromDate and :ToDate
>    Group By Module

>Table2 is a list of constant values for the amount of parts used for
>each Module.Table1 and Table2 both have the indexed field 'Module'. When
>the query runs I want to use the appropriate constant for each Module
>returned in the result set to get the product of QtyRan and PartsPlaced.
>Where am I going wrong?

I don't see any link on Module? or a specification of where Total1 and Total2
come from.  You also need all regular fields in the Group by when you create
summary fields.

Try something like this

Select Sum(D1.Total1 + D1.Total2)/2, D1.QtyRan *
D2.PartsPlace,D1.Module from Table1 D1, Table2 D2
where D1.module=d2.module and
RunDate between  :FromDate and :ToDate
Group By D1.Module, D1.qtyRan, D2.partsPlace,D1.module
--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Other Threads