How to write a select inside case statement

Question

I have a stored procedure that contains a case statement inside a select statement.

select Invoice_ID, 'Unknown' as Invoice_Status, 
case when Invoice_Printed is null then '' else 'Y' end as Invoice_Printed, 
case when Invoice_DeliveryDate is null then '' else 'Y' end as Invoice_Delivered, 
case when Invoice_DeliveryType <> 'USPS' then '' else 'Y' end as Invoice_eDeliver, 
Invoice_ContactLName+', '+Invoice_ContactFName as ContactName, 
from dbo.Invoice
left outer join dbo.fnInvoiceCurrentStatus() on Invoice_ID=CUST_InvoiceID 
where CUST_StatusID= 7 
order by Inv_Created

At line case when Invoice_DeliveryType <> 'USPS' then '' else 'Y' end as Invoice_eDeliver

I need to check for a valid email address (if email is valid, display Y, else display N).

So the line would read:

if Invoice_DeliveryType <> 'USPS' then '' else ( If ISNULL(Select emailaddr from dbo.Client Where Client_ID = SUBSTRING(Invoice_ID, 1, 6)), 'Y', 'N')

How can I write out this query?

Gordon Linoff · Accepted Answer · 2014-09-10T15:59:53.500

2

You can do this with a case. I think the following is the logic you want:

(case when Invoice_DeliveryType <> 'USPS' then ''
      when exists (Select 1
                   from dbo.Client c
                   Where c.Client_ID = SUBSTRING(i.Invoice_ID, 1, 6) and
                         c.emailaddr is not null
                  )
      then 'Y'
      else 'N'
 end)

edited Sep 10 '14 at 15:59

answered Sep 10 '14 at 14:28

Gordon Linoff

1,242,037
58
646
786

I am getting an error "The conversion of the nvarchar value '20111028995999' overflowed an int column." EmailAddr is actually a GUID value – user3929962 Sep 10 '14 at 14:44
@user3929962 . . . I don't think `emailaddr` is needed for the query, assuming that the purpose of `isnull()` is to determine if any row is returned, rather than a `NULL` value in the email field. That error doesn't seem related to this code. – Gordon Linoff Sep 10 '14 at 14:56
I updated my query with the changes you made, but I still get the same error. The original query works perfectly. – user3929962 Sep 10 '14 at 15:01
@user3929962 . . . Nothing in this snippet could be converting a string with 14-character string to an integer. Perhaps a 6-character one, depending on the `where` clause, but not a 14-character one. – Gordon Linoff Sep 10 '14 at 15:02
added "AND emailaddr is not null" and the query works now. Thanks for your help – user3929962 Sep 10 '14 at 15:17

How to write a select inside case statement

1 Answers1

Linked