Shyam writes "Hi,I am trying to load a dimension table in an OLAP database for which i am writing a SQL Statement on one of my Datawarehouse table.The question is on the framing of the select statement.Basically the SQL select statement has to do the following things.1. First it should check whether a particular column in the target table has a pre disgnated value(like last 3 characters are '000'), if it has - it takes that column as it's first field Or Else it has to take another column from the table.2. Now it has to check whether the first field is the one that it want's. if it is: It is fine OR Else it takes some other field as it's second field.While i could accomplish fairly easily from Microsft access, i could not do this with Microsft SQL SERVER 2000 or 7.0. The Query in MS ACCESS Looks like this:SELECT IIf(Mid([CustID],6,8)="000",Trim([CustomerName]) & " (" & [CustID] & ")",Trim([BillingDepartmentName]) & " (" & [CustId] & ")") AS N, IIf([N]=Trim([CustomerName]) & " (" & [CustID] & ")",IIf([CMID],[CMID],"Total"),Trim([CustomerName]) & " (" & Left([CustID],5) & ")") AS C1, IIf([C1]="Total","",IIf([C1]=Trim([CustomerName]) & " (" & Left([CustID],5) & ")",IIf([CMID],[CMID],"Total"))) AS C2, IIf([C2]=[CMID],"Total","") AS C3, dbo_Customer.BranchName, dbo_Customer.ActivationDate, dbo_Customer.ARBalance, dbo_Customer.DSO, dbo_Customer.CustIDFROM dbo_Customer;To Be simple:a) What is the substitute for ACCESS 'IIF' statement in SQL SERVER. ('CASE' statement is some what an alternative but not as flexible as IIF)b) How to name a column in the select statemnt and use it's value in next column with in the same select statement. (I know the column can be given an alias name, but how to use it's value again is the problem)Any help on this is very much appreciated.Thanks in advance,Shyam."
Missing 'IIF' command in SQL Server 2000. 
Posted - 2001-09-05 : 23:55:18