Date: 01/05/05 (SQL Server)    Keywords: sql

i feel like a sql goddess today. seriously.

i had to convert a complicated multicondition access iif statement to a working sql statement -sql does not allow iif.  i've been working on this for days, when suddenly, just now, in the midst of exhaustion i discovered the answer..take a look..

before[access]:

IIf([keyms]=1,"-C") & IIf([pmp]=1,"-P") & IIf([regulatory]=1,"-R") & IIf([ipabs]=1,"-I") & IIf([perfobj]=1,"-PO") & " (" & [cdlevel] & ")" AS mstype
FROM milestones;

after [sql]:

CREATE VIEW dbo.milestonesq
AS
SELECT    milestones.*, CASE WHEN mscomplete IS NULL THEN 0 ELSE 1 END AS cmplt, replace(replace(cast(replace(keyms,'1','-C') as varchar)+ cast(replace(pmp,'1','-P') as varchar)+cast(replace(regulatory,'1','-R')as varchar)+cast(replace(ipabs,'1','-I')as varchar)+cast(replace(perfobj,'1','-PO')as varchar)+'('+cast(cdlevel as varchar)+')','0',''),'()','(0)') AS mstype
FROM        milestones

Source: http://www.livejournal.com/community/sqlserver/14438.html