Re: SQL brain fade: CASE statement

[rob@xxxxxxxxx]

select * from reeve
....+....1...
FACTOR  VALUE
  1       A
  1       B
  1       C
  2       A
  2       B
  2       X

SELECT * FROM REEVE
WHERE
      (FACTOR = '1' and VALUE = 'A')
   OR (FACTOR = '2' and VALUE in('A', 'B'))
....+....1...
FACTOR  VALUE
  1       A
  2       A
  2       B

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


|-----------------------------+-------------------------------------------|
|   "Reeve" <news@xxxxxxxxxx> |                                           |
|   Sent by:                  |                                           |
|   midrange-l-bounces@midrang|                                         To|
|   e.com                     |                                    "'Midra|
|                             |                                    nge    |
|   07/20/2004 04:20 PM       |                                    Systems|
|                             |                                    Technic|
|         Please respond to   |                                    al     |
|         Midrange Systems    |                                    Discuss|
|       Technical Discussion  |                                    ion'"  |
|      <midrange-l@xxxxxxxxxxx|                                    <midran|
|                m>           |                                    ge-l@mi|
|                             |                                    drange.|
|                             |                                    com>   |
|                             |                                         cc|
|                             |                                           |
|                             |                                    Subject|
|                             |                                    SQL    |
|                             |                                    brain  |
|                             |                                    fade:  |
|                             |                                    CASE   |
|                             |                                    stateme|
|                             |                                    nt     |
|                             |                                           |
|                             |                                           |
|                             |                                           |
|                             |                                           |
|                             |                                           |
|                             |                                           |
|-----------------------------+-------------------------------------------|






I have a complex SELECT statement and I'm having trouble figuring out a way
to add some new logic using CASE.  FACTOR and VALUE are column names; '1',
'2', 'A', and 'B' are host variables.  In code that doesn't work, here's
what I want to do:



select * from file

where

{other selection conditions)

and

case when FACTOR = '1' then VALUE = 'A'

when FACTOR = '2' then VALUE in('A','B')

else null

end

and

(more selection conditions)



In English:

If FACTOR is '1', select all records where VALUE is 'A'.

If FACTOR is '2', select all records where VALUE is 'A' or 'B'



I think I have three choices: figure out how to do to this with a CASE,
write a stored procedure to do the logic, or extend the SELECT statement
like this:



select * from file

where



{other selection conditions)

and

FACTOR = '1' and VALUE = 'A'

and

(more selection conditions)



OR



{other selection conditions, same as above)

and

FACTOR = '2' and VALUE = 'B'

and

(more selection conditions, same as above)





Is this a case of "you can't get there from here", or have I made a goofy
error, or have I failed to design the solution properly?



Thanks,

Reeve



--
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.