[wellylug] [OT] PHP - Oracle - Query Help Desperatly Needed ;-)

Brent Wood pcreso at pcreso.com
Tue Sep 27 13:04:14 NZST 2005



My 02c...


> 
> SELECT
> 	tblRegion.Description,
> 	SUM(tblSales.DeliveredQuantity),
> 	SUM(tblSales.SalesValue),
> 	(FLOOR(SUM(tblSales.SalesValue)) -
> 	 FLOOR(SUM(tblSales.Cost))),
> 	FLOOR(((FLOOR(SUM(tblSales.SalesValue)) -
>                 FLOOR(SUM(tblSales.Cost))) /
>                FLOOR(SUM(tblSales.SalesValue))) * 100),
> 	tblCustomer.Region
> FROM
> 	tblSalesOrder
> 	INNER JOIN tblSales ON
> 		tblSalesOrder.SalesOrderNumber =
> 		tblSales.SalesOrderNumber
> 	INNER JOIN TTCCOM100099 ON
> 		tblSalesOrder.Customer =
> 		TTCCOM100099.Customer
> 	INNER JOIN tblCustomer ON
> 		tblCustomer.Customer =
> 		TTCCOM100099.Customer
> 	INNER JOIN tblRegion ON
> 		tblCustomer.Region =
> 		tblRegion.Region
> WHERE
> 	tblSalesOrder.SalesRep = '". $rep ."'
> GROUP BY
> 	tblRegion.Description, tblCustomer.Region
> ORDER BY
> 	FLOOR(SUM(tblSales.SalesValue)) DESC
> 
> So what is TTCCOM100099? Seems unnecessary anyway, you can just join
> tblSalesOrder (TCISLI240099) directly to tblCustomer (TTCCOM110099),
> unless the join is just to exclude lines that don't have a customer in
> the mystery table.


in which case a subquery may be more efficient than an inner join, eg:
...
where tblCustomer.Customer in (select distinct Customer from TTCCOM100099)
  and ....


also, the nested floors simply gives more room for rounding errors to
accumulate, so I'd suggest just retaining the outer FLOOR and just have the
SUM's in the inner part, unless some accounting arcana requires the extras.


Brent




More information about the wellylug mailing list