RE: SQL Question

["John Arnold \(MFS\)" <jarnold@xxxxxxxxxxxxxxxxxxxxxxxxxx>]

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