incorporating if statement inside select

[DOGWATCH]

i couldn't find a good example of this in my texts.

I need to incorporate a if statement into my query. We add another field to invoices_staging_r100 called shiptype.

If the company name = PHYSICIANS SALES then shiptype=STOCK
ELSEIF the first 5 character of company<>first 5 characters of shipcompany THEN shiptype=DROP ELSE shiptype=STOCK.

Any ideas how to write this logic in my query?


select
if(left(company,5)<>left("ship_company",5),
'DROP',
if(company='PHYSICIANS SALES',
if(Index("ship_company",'SALES')>0,'STOCK','DROP'),'STOCK')) as shiptype, *
from invoices_staging_r100

 

[bborissov]

[COLOR=blue]select[/color]  [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] company=[COLOR=red]'PHYSICIANS SALES'[/color]
                  [COLOR=blue]THEN[/color] [COLOR=red]'STOCK'[/color]
             [COLOR=blue]WHEN[/color] [COLOR=#FF00FF]left[/color](company,5) <> [COLOR=#FF00FF]left[/color](ship_company,5)
                  [COLOR=blue]THEN[/color] [COLOR=red]'DROP'[/color]
        [COLOR=blue]ELSE[/color] [COLOR=red]'STOCK'[/color] [COLOR=blue]END[/color] [COLOR=blue]AS[/color] shiptype,
        *         
[COLOR=blue]from[/color] invoices_staging_r100

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP