如何编写一个 select inside case 语句 [英] How to write a select inside case statement
本文介绍了如何编写一个 select inside case 语句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个存储过程,它在 select 语句中包含一个 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
在行 case when Invoice_DeliveryType <>'USPS' then '' else 'Y' 结尾为 Invoice_eDeliver
我需要检查有效的电子邮件地址(如果电子邮件有效,则显示 Y,否则显示 N).
I need to check for a valid email address (if email is valid, display Y, else display N).
所以这行会写成:
if Invoice_DeliveryType <>'USPS' then '' else (If ISNULL(Select emailaddr from dbo.Client Where Client_ID = SUBSTRING(Invoice_ID, 1, 6)), 'Y', 'N')
如何写出这个查询?
推荐答案
您可以使用 case
来做到这一点.我认为以下是您想要的逻辑:
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)
这篇关于如何编写一个 select inside case 语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文