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

[Nick Jensen]

On 9/27/05, Geraint Jones  wrote:
> I can think of nowhere else in NZ where I can pose this sort of question -
> so here goes
...

OK, I actually have never used Oracle, but I'll give it a go based on
SQL Server's version of SQL.

Translating all that into something readable with a bunch of
find/replace all's and formatting it in my own slightly convoluted way
to avoid extra email line breaks, I get:

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.

Can Oracle do nested SELECT statements? I would try something like:

...
	tblCustomer.Region,
	(SELECT
		SUM(DeliveredQuantity)
	 FROM
		SALES_HISTORY
	 WHERE
		YEAR(Sale_Date) = 2004 AND
		Customer = tblCustomer.Customer)
FROM
...

This might work better using table aliases instead of the whole table name.