Re: Alias question

From: <fitzjarrell_at_cox.net>
Date: 24 May 2005 18:54:28 -0700
Message-ID: <1116986068.446139.47450@f14g2000cwb.googlegroups.com>

dcMan wrote:
> 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;

You COULD try this:

select s.pID, s.ID2, s.partNum, s.picks, s.totalcount, s.donecount,

          (CASE s.totalcount
                 WHEN 0 then 'Infinity'
                 ELSE TO_CHAR((s.donecount*100)/s.totalcount) || '%'
          END ) as myPercent

from
(select z.iAreaID as pID,

         z.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 = z.iAreaID
                      and sPartNum = z.sPartNum) as totalcount,
         (select count(*)
              from zcarton
              where myValue = 1001
                      and IstatusID IN (3,4,5)
                      and iPickAreaID = z.iAreaID
                      and sPartNum = z.sPartNum) as donecount

 FROM zCarton z
 WHERE z.myValue = 1001
 GROUP BY z.iPickAreaID, z.sPartNum) s;

And, just possibly, the above query will work. I haven't tested it so I can't guarantee the results.

David Fitzjarrell Received on Tue May 24 2005 - 20:54:28 CDT