Re: SQL Question

[Jeff Young <cooljeff913@xxxxxxxxx>]

Thanks, but the actual problem was that I was specifing 'end case' to end the case statement, but it *should* have been just 'end'.
The 'end case' caused the field created to be named 'case' which confused me to no end. lol
I changed the statement to use the correct field name and then used that in my order by.


 
Jeff Young 
Sr. Programmer Analyst
IBM -e(logo) server Certified Systems Exper - iSeries Technical Solutions V5R2 
IBM  Certified Specialist- e(logo) server i5Series Technical Solutions Designer V5R3
IBM  Certified Specialist- e(logo)server i5Series Technical Solutions Implementer V5R3 


  
 





----- Original Message ----
From: "Michael_Schutte@xxxxxxxxxxxx" <Michael_Schutte@xxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Sent: Thursday, March 13, 2008 12:19:40 PM
Subject: Re: SQL Question

Or...

select substr(oncitm,5,10) cstitm, onitem,okpric    price,
case when okpgrp = '40004' then '1'
    when okpgrp = '40001' then '2' end case as New_Field
from
vcoitem join vinitem
Order By 4

but you want to use the name you just created, you'll have to use jim's
method.


Michael Schutte
Admin Professional
Bob Evans Farms, Inc.





midrange-l-bounces@xxxxxxxxxxxx wrote on 03/13/2008 12:15:56 PM:

> With t1 as(
> select substr(oncitm,5,10) cstitm, onitem,okpric    price,
> case when okpgrp = '40004' then '1'
>    when okpgrp = '40001' then '2' end as Field_Name
>  from
> vcoitem join vinitem )
>
>  select * from t1 order by Field_name
>
> On Thu, Mar 13, 2008 at 10:12 AM, Jeff Young <cooljeff913@xxxxxxxxx>
wrote:
> > Given the following SQL Statement -
> > select substr(oncitm,5,10) cstitm, onitem,okpric    price,
> > case when okpgrp = '40004' then '1'
> >    when okpgrp = '40001' then '2' end case
> >  from
> > vcoitem join vinitem
> >
> > How can I specify the result of the CASE clause in the order by clause.
> > Also, how can I assign a name to the result of the CASE clause
> >
> > TIA,
> >
> > Jeff Young
> > Sr. Programmer Analyst
> > IBM -e(logo) server Certified Systems Exper - iSeries Technical
Solutions
> > V5R2
> > IBM  Certified Specialist- e(logo) server i5Series Technical Solutions
> > Designer V5R3
> > IBM  Certified Specialist- e(logo)server i5Series Technical Solutions
> > Implementer V5R3
> >
> >
> >
> >
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> > list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.