View problem

[wadewilson1]

I'm trying to create a new column derived from another column and I am having trouble doing it.

I have column 1 that can be 1,2, or null, if it is 1 I want it to display A, if 2 then B if null, then display column 3. I tried the following code, but it doesn't work at (not surprising)

SELECT DISTINCTROW PEOPLESOFT_ID, Department, CategoryText, ProductID, TotalRawScore, TotalRawGoal IIf([productid] = '1', (IIf([department] <> 'Lost Stolen', '', [department]), IIf([productid] = '2', IIf(([department] = 'Lost Stolen' OR [Department] = 'Fraud Detection'), [department], ''), [department]))) AS DepartmentEx
FROM dbo.VW_QASCORES_EXCLUDE

 

[SQLSister]

What database are you using? IIF is not a function that Microsoft SQL server uses. Look up the Case function in Books ONline instead.

 

[wadewilson1]

I'm using MSSQL Server 2000, when I try to create the view, it tells that the CASE SQL construct is not supported. Any suggestions?

 

[tlbroadbent]

Use Query Analyzer to create the view rather than Enterprise Manager. You can then use CASE.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm

 

[SQLBill]

IIF is a Microsoft Access function. CASE works in SQL Server 2000, I use it quite often.

How about posting your code with the CASE statement? Maybe there's something wrong with the way you put it together.

-SQLBill

 

[wadewilson1]

Here is what I have in the Query Analyzer:
SELECT PEOPLESOFT_ID, Department, [Date], TotalRawScore, TotalRawGoal, ProductID,
DEPARTMENTEx = CASE WHEN (ProductID = &quot;1&quot; AND DEPARTMENT = &quot;LOST STOLEN&quot THEN &quot;LOST STOLEN&quot;
WHEN (ProductID = &quot;2&quot; AND (DEPARTMENT = &quot;LOST STOLEN&quot; OR DEPARTMENT = &quot;FRAUD DETECTION 1&quot THEN &quot;LSRDET&quot;
ELSE &quot;UNIVFRAUD&quot;
END
FROM dbo.VW_QASCORES_EXCLUDE

 

[SQLBill]

From your code, it appears you are using PEOPLESOFT. Are you sure it's using MS SQL Server? I know our PEOPLESOFT programs have changed to ORACLE.

-SQLBill

 

[wadewilson1]

The PEOPLESOFT_ID is just a column in the table, it resides on a SQL Server 2000 box.

 

[wadewilson1]

Thanks everyone for you help, I finally got the view created. It appears that on the 2nd WHEN, I forgot the closing parenthesee.

I also ad to change the double quotes back to single quotes.

Thanks again