Re: Alias question

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 should be able to ORDER BY 5: Positional notation.

This is a query that may work in Sybase but I expect is a demonstration that you haven't opened an Oracle book or doc. If you think trying to do Sybase in Oracle is a successful strategy I'd suggest you polish up your resume.

Bookmark these:

http://tahiti.oracle.com
http://asktom.oracle.com
http://www.psoug.org (click on Morgan's Library)

And purchase a copy of Tom Kyte's book "Expert one-on-one Oracle"

-- 
Daniel A. Morgan
Relational theory is not something that is simply a nice-to-have.
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)