RE: SQL Question

Try this:

SELECT
case when fieldvalue = '02' then 'PHYS'
when fieldvalue IN('03','05','06') then 'HOSP'
when fieldvalue IN('00','01','07') then 'ANCL'
when fieldvalue IN('04','10','11','12') then 'ANCL'
Else 'PGRP' end as CodeValue
FROM library/filename


John Arnold
(301) 354-2939


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Young
Sent: Monday, February 04, 2008 12:32 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL Question

Rob,
My issue is that I have already have the field retreived from wherever
its origin may have been (screen, db, parm, etc).
What I was looking for was a shortcut using SQL to just test the value
of that field instead of having to code multiple IF or Select/WHENxx
conditions.
As I indicated earlier, I do not know if this can even be done, but felt
that this was the place to ask the question.

Thanks again,

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: "rob@xxxxxxxxx" <rob@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Sent: Monday, February 4, 2008 11:42:25 AM
Subject: RE: SQL Question

Apparently you can use IN when you do this select checkthis,
case
when checkthis in('A','B') then '1'
else '0'
end as IndicatorChk
from qtemp.young

But you can't use IN, or I was doing it wrong, with this:
select checkthis,
case CheckThis
when in('A','B') then '1'
else '0'
end as IndicatorChk
from qtemp.young

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
02/04/2008 11:34 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
"Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
cc

Subject
RE: SQL Question






Coudn't you just use a CASE statement?

Select key, d1, d2,
case when d3 in ('a', 'b', 'c')
then '1' else '0' end as Ind_Flag

From abcdata

hth,
Eric

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Jeff Young
Sent: Monday, February 04, 2008 10:24 AM
To: midrange-l@xxxxxxxxxxxx; rpg400-l@xxxxxxxxxxxx
Subject: SQL Question


Using SQL, is there any way to test a variable for a value and return an

indicator flag?
What I would like to do is to be able to use the SQL BIF's to test a
variable for a list of values and then return a '1' or '0' (on/off) to
the
program.

Thanks,


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