SQL CASE Statement for nested If statements

[itp]

Any advice as to how to construct an SQL CASE Statement for
1. nested If statements
2. evaluating more than one variable on an 'if'. (I get an error on the second variable)

If Method = 'P' 
   fromWH = FromWH1  
   Mode = Mode1
Else
  If Origin = 'F'
   fromWH = FromWH2  
   Mode = Mode2
  Else  
   fromWH = FromWH3  
   Mode = Mode3
  EndIf
EndIf 

[B.Hauser]

Re: SQL CASE Statement for nested If statements

Hi,

I'm not quite sure what you try to archieve.

You have a table where Mode and Method are field and you want to join this table to table A only if Method is 'P' and you want to join to table b is method is 'F' and you want to join to a third table if method is neither 'P' nor 'F'.

If so you may try something like this:

select a.*, b.*
   from TableA a join TableB b on a.Mode = b.Mode 
   Where Method = 'P'
Union All
Select a.*, b.*
   from TableA a join TableB b on a.Mode = b.Mode 
   Where Method = 'F'
Union All
Select a.*, b.*
   from TableA a join TableB b on a.Mode = b.Mode 
   Where Method not in ('P', 'F') 

Birgitta

[itp]

Re: SQL CASE Statement for nested If statements

Oh, No not that complicated. I am using just one file and I want to replicate some
If-then-else logic using the SQL Cases construct. Here is some "pseudo-SQL"

Select 
  Division,
  Department,
    CASE WHEN Method = 'P' 
       Then FromWH1, Mode1,  // here I want to return two values when Method = 'P' 
    Else 
       CASE WHEN Method = 'F' 
       Then FromWH2, Mode2, 
    Else 
       FromWH3, Mode3,    
    End
  End
From File1 

[littlepd]

Re: SQL CASE Statement for nested If statements

I'm pretty sure you'll have to make to separate CASE statements for that:

Select
  Division,
  Department,
  CASE Method
    When 'P' Then FromWH1
    When 'F' Then FromWH2
    Else FromWH3
  End AS FromWH,
  CASE Method
    When 'P' Then Mode1
    When 'F' Then Mode2
    Else Mode3
  End AS Mode
From File1

...or something like that.