Clever Geek Handbook

How to write selection inside case case

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

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

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

So the line will 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 this request?

+2


source to share


1 answer


You can do this with case

. I think the following logic is what 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)
+2


source






More articles:


All Articles