CASE Statement

[bdmoran]

I am new to writing case statements in SQL server. What I'm trying to do is if an account has more than 1 customer name ID, I want the phone number to be null otherwise, I want to pull the phone_number.

The case statement is highlighted in RED:

DECLARE @order_ID nvarchar(10),@other_info_2 varchar(100),@inet_event_description varchar(100),@section_name varchar(10),@add_usr varchar(100),@cust_name_id varchar (100),@name nvarchar(100),@num_seats nvarchar(100),@class_name varchar (100),@strSubject varchar(100), @strBody varchar(4000), @type tinyint, @bitmap char (6),@cursor varchar(25),@live_order_id nvarchar(25),@email_addr varchar(25),@order_num nvarchar(50),@order_line_item nvarchar(10), @order_line_item_seq nvarchar(10),@acct_id varchar(10),@event_date nvarchar(25),@event_time nvarchar(25)
DECLARE UnVerifiedCursor CURSOR FOR
SELECT v_returned_inventory.order_id,v_live_inventory.inet_event_description,v_live_inventory.event_date,v_live_inventory.event_time, v_live_inventory.section_name,
v_returned_inventory.add_usr, v_live_inventory.num_seats, v_returned_inventory.acct_id, v_live_inventory.class_name,
AT_trans_for_emailTrigger.email_addr, AT_trans_for_emailTrigger.cust_name_id, premclub.name_first + ' ' + premclub.name_last AS name CASE phone when count(cust_name_id > 1) then @phone = null else @phone = v_cust_phone.phone_number_formatted
FROM v_returned_inventory INNER JOIN
v_live_inventory ON LEFT(v_returned_inventory.event_name, 6) = LEFT(v_live_inventory.event_name, 6) AND
v_returned_inventory.orderNumber = v_live_inventory.other_info_1 INNER JOIN
AT_trans_for_emailTrigger ON v_returned_inventory.order_id = AT_trans_for_emailTrigger.order_id LEFT OUTER JOIN
OPENQUERY(premclub, 'select name_first, name_last, cust_name_id from dba.v_cust_name') AS premclub ON
AT_trans_for_emailTrigger.cust_name_id = premclub.cust_name_id
GROUP BY v_returned_inventory.acct_id, v_returned_inventory.order_id, v_live_inventory.section_name, v_live_inventory.inet_event_description,
v_live_inventory.num_seats, v_returned_inventory.add_usr, v_live_inventory.class_name,v_live_inventory.event_date,v_live_inventory.event_time,
AT_trans_for_emailTrigger.email_addr, AT_trans_for_emailTrigger.cust_name_id, premclub.name_first + ' ' + premclub.name_last

 

[djj55]

you need and END

CASE phone WHEN count(cust_name_id > 1) THEN null ELSE v_cust_phone.phone_number_formatted END

also you cannot use the @phone = within the CASE. You would need to assign this elsewhere.

Did you miss a comma before the CASE?

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[bdmoran]

djj - Thank you for your response. I tried your syntax and it didn't work either. I also added the comma before CASE. I can insert the case statement right in my Select statementm, right? I already have an IF statement as part of my stored procedure. Can I add a case statement like that or is it better to keep it in my select?

 

[Rhys666]

You have to assign to the variable outside the case statement, the count function only takes one parameter, (you've for '> 1' inside the parameters you're sending to the function, and as previously stated, you're missing and END and a comma. It's probably also worth prefixing the 'cust_name_id field' within the COUNT function with its source table name.

,@phone = CASE phone WHEN COUNT(TABLE_NAME.cust_name_id) > 1 then NULL ELSE v_cust_phone.phone_number_formatted END

Rhys

"Technological progress is like an axe in the hands of a pathological criminal"
"Two things are infinite: the universe and human stupidity; and I'm not sure about the the universe"
Albert Einstein

 

[bdmoran]

Alright now I'm having an issue with @strBody populating the sent email. My SP sends the email with the subject but the body of the email is blank. It is strange to me because this was working perfectly before. My co-worker change some joins in my Select statement but I don't think should affect my SP. Any ideas?

DECLARE @order_ID nvarchar(10),@inet_event_description varchar(100),@section_name varchar(10),@add_usr varchar(100),@cust_name_id varchar (100),@name nvarchar(100),@num_seats nvarchar(100),@class_name varchar (100),@strSubject varchar(100), @strBody nvarchar(4000), @type tinyint, @bitmap char (6),@cursor varchar(25),@live_order_id nvarchar(25),@email_addr varchar(25),@order_num nvarchar(50),@order_line_item nvarchar(10), @order_line_item_seq nvarchar(10),@acct_id varchar(10),@event_date nvarchar(25),@event_time nvarchar(25),@phone nvarchar(25)
DECLARE UnVerifiedCursor CURSOR FOR
SELECT v_live_inventory.live_order_id,v_live_inventory.inet_event_description, v_live_inventory.section_name,
v_returned_inventory.add_usr, v_live_inventory.num_seats, v_returned_inventory.acct_id, v_returned_inventory.class_name,
AT_trans_for_emailTrigger.email_addr, AT_trans_for_emailTrigger.cust_name_id, premclub.name_first + ' ' + premclub.name_last AS name
FROM v_returned_inventory INNER JOIN
v_live_inventory ON LEFT(v_returned_inventory.event_name, 6) = LEFT(v_live_inventory.event_name, 6) AND
v_returned_inventory.orderNumber = v_live_inventory.other_info_1 INNER JOIN
AT_trans_for_emailTrigger ON v_returned_inventory.order_id = AT_trans_for_emailTrigger.order_id LEFT OUTER JOIN
OPENQUERY(premclub, 'select name_first, name_last, cust_name_id from dba.v_cust_name') AS premclub ON
AT_trans_for_emailTrigger.cust_name_id = premclub.cust_name_id
GROUP BY v_returned_inventory.acct_id, v_live_inventory.live_order_id, v_live_inventory.section_name, v_live_inventory.inet_event_description,
v_returned_inventory.other_info_3, v_live_inventory.num_seats, v_returned_inventory.add_usr, v_returned_inventory.class_name,
AT_trans_for_emailTrigger.email_addr, AT_trans_for_emailTrigger.cust_name_id, premclub.name_first + ' ' + premclub.name_last


--open cursor
OPEN UnVerifiedCursor
FETCH NEXT FROM UnVerifiedCursor INTO @order_id,@inet_event_description,@section_name,@add_usr,@num_seats,@acct_id,@class_name,@email_addr,@cust_name_id,@name
WHILE @@FETCH_STATUS = 0
BEGIN
-- Send e-mail for View
IF @section_name = 'gdview' or @section_name = 'exview'
BEGIN
SET @strBody = 'Rental Notification

Name:' + SPACE(1) + @name + SPACE(1) + '
Email address:' + SPACE(1) + @email_addr + SPACE(1) +'
Sales Agent:' + SPACE(1) + @add_usr + SPACE(1) +'


'+'Additional Information:' +'
--------------------------------------------------------------------------------

Event:' + SPACE(1) + @inet_event_description + SPACE(1) + '
Event Date:' + SPACE(1) + CONVERT(VARCHAR,@event_date,107) + SPACE(1) + SPACE(1) + right(CONVERT( varchar,@event_time, 100),7) + SPACE(1) + '
Description:' + SPACE(1) + @section_name + SPACE(1) + '
Quantity:' + SPACE(1) + @num_seats + SPACE(1) + '
--------------------------------------------------------------------------------'

SET @strSubject = 'ORDER NOTIFICATION: '+ @inet_event_description + SPACE(1)+' - '+ @section_name
-- EXEC sp_send_cdontsmail @strFrom, @email_addr, @strSubject, @strBody
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Orders_at_TDGarden_profile',
@recipients = 'test@test.com;',
@body = @strBody ,
@subject = @strSubject
END
--Send e-mail for Suite/Patron Lounge Rentals
ELSE
BEGIN
SET @strBody = 'Rental Notification

Name:' + SPACE(1) + @name + SPACE(1) + '
Email address:' + SPACE(1) + @email_addr + SPACE(1) +'
Sales Agent:' + SPACE(1) + @add_usr + SPACE(1) +'


'+'Additional Information:' +'
--------------------------------------------------------------------------------

Event:' + SPACE(1) + @inet_event_description + SPACE(1) + '
Event Date:' + SPACE(1) + CONVERT(VARCHAR,@event_date,107) + SPACE(1) + SPACE(1) + right(CONVERT( varchar,@event_time, 100),7) + SPACE(1) + '
Description:' + SPACE(1) + @section_name + SPACE(1) + '
Quantity:' + SPACE(1) + @num_seats + SPACE(1) + '
--------------------------------------------------------------------------------'

SET @strSubject = 'ORDER NOTIFICATION: '+ @inet_event_description + SPACE(1)+' - '+ @section_name

-- EXEC sp_send_cdontsmail @strFrom, @email_addr, @strSubject, @strBody
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Orders_at_TDGarden_profile',
@recipients = 'bdmoran@dncboston.com;',
@body = @strBody ,
@subject = @strSubject
END
FETCH NEXT FROM UnVerifiedCursor INTO @order_id,@inet_event_description,@section_name,@add_usr,@num_seats,@acct_id,@class_name,@email_addr,@cust_name_id,@name
END
-- close cursor
CLOSE UnVerifiedCursor
DEALLOCATE UnVerifiedCursor













-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================

 

[bdmoran]

Hi,

You can disregard my last post. I ended up re-coding my stored procedure and fixing what was wrong. However, I am looking to create a subquery that contains a CASE statement but keep getting errors:

SELECT v_returned_inventory.order_id,v_live_inventory.inet_event_description,v_live_inventory.event_time,v_live_inventory.event_date, v_returned_inventory.section_name,
v_returned_inventory.add_usr, v_live_inventory.num_seats, v_returned_inventory.acct_id, v_live_inventory.class_name,
AT_trans_for_emailTrigger.email_addr, AT_trans_for_emailTrigger.cust_name_id, premclub.name_first + ' ' + premclub.name_last AS name
FROM v_returned_inventory INNER JOIN
v_live_inventory ON LEFT(v_returned_inventory.event_name, 6) = LEFT(v_live_inventory.event_name, 6) AND
v_returned_inventory.orderNumber = v_live_inventory.other_info_1 INNER JOIN
AT_trans_for_emailTrigger ON v_returned_inventory.order_id = AT_trans_for_emailTrigger.order_id LEFT OUTER JOIN
OPENQUERY(premclub, 'select name_first, name_last, cust_name_id from dba.v_cust_name') AS premclub ON
AT_trans_for_emailTrigger.cust_name_id = premclub.cust_name_id (Select * from v_cust_phone CASE WHEN COUNT(v_cust_phone.cust_name_id) > 1 then NULL ELSE v_cust_phone.phone_number_formatted END)
GROUP BY v_returned_inventory.acct_id, v_returned_inventory.order_id, v_returned_inventory.section_name, v_live_inventory.inet_event_description,
v_returned_inventory.other_info_3, v_live_inventory.num_seats, v_returned_inventory.add_usr, v_live_inventory.class_name,
AT_trans_for_emailTrigger.email_addr, AT_trans_for_emailTrigger.cust_name_id, premclub.name_first + ' ' + premclub.name_last,v_live_inventory.event_time,v_live_inventory.event_date

Please let me know if you can help. Thanks!

 

[SQLScholar]

Sorry.... what are you actually trying to achieve with that subquery?

Is it trying to join to that subquery? If so you need to define the join. Subquery joins look like this

INNER JOIN
(Select field1, field2, field3 from tablename where field1 = 'xyx') TABLEALIAS on TABLEALIAS.field1 = mainquery.field1



----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[bdmoran]

Sorry for the confusion. What I am trying to do is create a subquery/case statement within that Select statement.

So, I need to pull phone numbers but if one account has more than one ID, I want the phone field to be null.

so being somehting like this CASE WHEN (cust > 1) phone_num = NULL ELSE phone_num

 

[SQLScholar]

The subquery (in the format i set above mind you) will be something like this

(Select * from v_cust_phone
WHERE v_cust_phone.cust_name_id not in (select v_cust_phone.cust_name_id from v_cust_phone group by v_cust_phone having count(v_cust_phone)>1))

If you join this with a left join - purely by the fact its a left join, if there are more then one then they would not be returned in the above query and will be null.

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[bdmoran]

I am a bit confused. I did an outer left join and keep receiving errors:

SELECT v_returned_inventory.order_id,v_live_inventory.inet_event_description,v_live_inventory.event_time,v_cust_phone.phone_number_formatted,v_live_inventory.event_date, v_returned_inventory.section_name,
v_returned_inventory.add_usr, v_live_inventory.num_seats, v_returned_inventory.acct_id, v_live_inventory.class_name,
AT_trans_for_emailTrigger.email_addr, AT_trans_for_emailTrigger.cust_name_id, premclub.name_first + ' ' + premclub.name_last AS name
FROM v_returned_inventory INNER JOIN
v_live_inventory ON LEFT(v_returned_inventory.event_name, 6) = LEFT(v_live_inventory.event_name, 6) AND
v_returned_inventory.orderNumber = v_live_inventory.other_info_1 INNER JOIN
AT_trans_for_emailTrigger ON v_returned_inventory.order_id = AT_trans_for_emailTrigger.order_id LEFT OUTER JOIN v_cust_phone on v_cust_phone.acct_id = v_returned_inventory.acct_id LEFT OUTER JOIN
OPENQUERY(premclub, 'select name_first, name_last, cust_name_id from dba.v_cust_name') AS premclub ON
AT_trans_for_emailTrigger.cust_name_id = premclub.cust_name_id NOT IN(select v_cust_phone.cust_name_id from v_cust_phone group by v_cust_phone having count(v_cust_name_id)>1))