-1

In DB2, i need to select rows from a single column based on the value of a host variable.

The db column can contain a 'D' or 'P' only.

The host variable can contain the value 'D' or 'P' or 'B' (all rows).

When the host variable is a 'D', I want to select only rows with a 'D'. When the host variable is a 'P', I want to select only rows with a 'P'. When the host variable is a 'B', I want to select ALL rows.

I am doing this in RPG using embedded SQL.

Thanks in advance

Charles
  • 21,637
  • 1
  • 20
  • 44
cubswin
  • 11
  • 3
  • select * from table where dbColumn = :hostvar. Works for 'D' and 'P', but stuck on how to code if hostvar = 'B'. I prefer not to use dynamic SQL. – cubswin Dec 10 '16 at 17:04
  • Going to try... select * from table where (dbColumn = :hostvar or dbColumn in ('D', 'P')). – cubswin Dec 10 '16 at 17:17

2 Answers2

1

Here you go.

select * from table                 
where dbColumn in (                           
case when :hostvar  ='B' then  'D'                           
     when :hostvar  ='D' then  'D'   
    when :hostvar  ='P' then  'P'                                    
end ,                                              
case when :hostvar  ='B' then  'P'                           
end
)
0

this will work... thanks all

Going to try... select * from table where (dbColumn = :hostvar or dbColumn in ('D', 'P'))

cubswin
  • 11
  • 3
  • 1
    Shouldn't work because `or dbColumn in ('D', 'P')` should always select all rows. Try `or :hostvar = 'B'` instead. – user2338816 Dec 11 '16 at 03:36