How to write a select inside case statement
27,353
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)
Comments
-
user3929962 almost 2 years
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?
-
user3929962 over 9 yearsI am getting an error "The conversion of the nvarchar value '20111028995999' overflowed an int column." EmailAddr is actually a GUID value
-
Gordon Linoff over 9 years@user3929962 . . . I don't think
emailaddr
is needed for the query, assuming that the purpose ofisnull()
is to determine if any row is returned, rather than aNULL
value in the email field. That error doesn't seem related to this code. -
user3929962 over 9 yearsI updated my query with the changes you made, but I still get the same error. The original query works perfectly.
-
Gordon Linoff over 9 years@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. -
user3929962 over 9 yearsadded "AND emailaddr is not null" and the query works now. Thanks for your help