Re: SQL Question

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.