David,
Thank you, your solution was perfect, and exactly what I was looking for.
Thanks again for taking the time to help me out.
<fitzjarrell_at_cox.net> wrote in message
news:1116986068.446139.47450_at_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 Wed May 25 2005 - 09:02:14 CDT
