How do you write an IF ELSE inside a SELECT statement in MSSQL 2008?

Question

Here is what I am trying to do:

SELECT iif(d.ItemType = 'INVOICE', 0, iif(d.ItemType = 'PACKING', 0, 1)) AS MissingDocuments FROM dbo.DocumentDetails AS D

Unfortunately realized this is not compatible with MSSQL2008. So tried writing an IF ELSE but that also is not working.

SELECT  IF d.ItemType = 'INVOICE'
   0
ELSE 
   BEGIN
      d.ItemType = 'PACKING'
      0
   ELSE
      1
   END  AS MissingDocuments
FROM  dbo.DocumentDetails AS D

can you please tell me what am I doing wrong here ?

use [`CASE`](https://msdn.microsoft.com/en-us/library/ms181765.aspx). `CASE WHEN d.ItemType IN ('INVOICE','PACKING') THEN 0 ELSE 1 END` — ughai, May 19 '15 at 05:39
you're missing `if` statement in your `else` block. There is no corresponding `if` for your nested `else` — Sachin, May 19 '15 at 05:41
possible duplicate of http://stackoverflow.com/questions/4622/sql-case-statement-syntax — ughai, May 19 '15 at 05:42
@ughai You should have posted an answer...those points could have been yours! — Tim Biegeleisen, May 19 '15 at 05:47
I agree with @stuartLC's answer (as far as duplicating the logic you've put forth in your question), but I wonder if this is really what you want. Are you trying to mark any document type that isn't Invoice/Packing as Missing, or are you trying to check an order to see if it has at least one of each of those document types associated with it? — APH, May 19 '15 at 05:47
@TimBiegeleisen - As long as OP gets an answer and the question gets closed, it's all good. :) — ughai, May 19 '15 at 05:52

score 7 · Accepted Answer · answered May 19 '15 at 05:41

7

Use CASE ... WHEN. The most concise logic seems to be:

SELECT 
  CASE WHEN d.ItemType IN ('INVOICE', 'PACKING') THEN 0 ELSE 1 END
     AS MissingDocuments 
FROM dbo.DocumentDetails AS d

i.e. the Document is missing if it isn't 'Invoice' or 'Packing'

answered May 19 '15 at 05:41

StuartLC

104,537
17
209
285

score 2 · Answer 2 · answered May 19 '15 at 05:41

2

I think You Looking For Case When Try This..

SELECT 
 case when d.ItemType = 'INVOICE' or d.ItemType = 'PACKING'
 then  0
 ELSE
  1
 END  AS MissingDocuments
FROM  dbo.DocumentDetails AS D

answered May 19 '15 at 05:41

Dhaval

2,341
1
13
16

How do you write an IF ELSE inside a SELECT statement in MSSQL 2008?

2 Answers2