I wanted to test ot the effect of an ORDER BY clause so using EDBManager I ran the query

SELECT
_PNO,
COALESCE(_Result,'-') AS _Result,
COALESCE(_Analysis,'-') AS _Analysis,
COUNT(*) AS _Count,
CAST(100* COUNT(*) /  X._Total AS NUMERIC(0,1)) AS _Percentage
FROM CallStats
JOIN
(
 SELECT _Result, COUNT(*) AS _Total
 FROM CallStats
 WHERE
 _fkProjects = :ProjectID
 AND
 _Date BETWEEN :StartDate AND :EndDate
 GROUP BY _PNO,COALESCE(_Result,'-')
) X ON COALESCE(X._Result,'-') = COALESCE(_Result,'-')
WHERE
_fkProjects = :ProjectID
AND
_Date BETWEEN :StartDate AND :EndDate
GROUP BY _PNO,COALESCE(_Result,'-'),COALESCE(_Analysis,'-')

Leaving that one I opened another query window, copied the SQL, added the ORDER BY clause and ran it. Result zero lines. Removed the ORDER BY clause and tried again still nothing. Altered the derived table from X to T and it works.


Roy Lambert

Roy,

<< Leaving that one I opened another query window, copied the SQL, added the
ORDER BY clause and ran it. Result zero lines. Removed the ORDER BY clause
and tried again still nothing. Altered the derived table from X to T and it
works. >>

I'll check it out.

--
Tim Young
Elevate Software
www.elevatesoft.com