Alias question

From: dcMan <gregkeesey_at_fortna.com>
Date: Tue, 24 May 2005 21:09:15 GMT
Message-ID: <%JMke.2676$4a.249242@news1.epix.net>

This script came from a Sybase db and now it needs to be converted to Oracle 10g.
I keep getting an error that "totalcount" is an invalid identifier. I found in Oracle's docs
that alias's can only be used for sorting and can not be used within a select for other processing.

How can I get around this aliasing issue? This script is embedded within a Java program, currently.
Thanks in advance.

select iAreaID as pID,

        sPartNum as ID2,
        COUNT(DISTINCT sPartNum) AS partNum,
        COUNT(*) as picks,


        (select count(*)

            from zcarton
            where myValue = 1001
                     and IstatusID IN (1,2)
                     and iPickAreaID = pID
                     and sPartNum = ID2) as totalcount,


        (select count(*)

             from zcarton
             where myValue = 1001
                     and IstatusID IN (3,4,5)
                     and iPickAreaID = pID
                     and sPartNum = ID2) as donecount,
         (CASE totalcount
                WHEN 0 then 'Infinity'
                ELSE TO_CHAR((donecount*100)/totalcount) || '%'
         END ) as myPercent

FROM zCarton
WHERE myValue = 1001
GROUP BY iPickAreaID, sPartNum; Received on Tue May 24 2005 - 16:09:15 CDT