August 6, 2015 at 9:15 am
I have two fields (SEC1 and SEC2 in EMP TBL) need to be updated to Y if they are null with the following exceptions:
1. If DEPT in 101, 104 do not set either of these fields
2. If DEPT in 102, 103 do not set SEC1 but do set SEC2
What I have trouble with is wrapping if around the case statement, I wanted to say if field is null and the rest of exceptions is true then go through the cases to update accordingly. When I wrap the if begin I get error.
I am using select here so I send list of records first to user before doing update, in the select I used sec1 and sec2_Update to show the values when update happens.
use tempdb
go
create table EMP_INFO
(
SEC1 NVARCHAR(5),
SEC2 NVARCHAR(5),
DEPT NVARCHAR(5)
);
GO
insert into EMP_INFO values
('Y', 'N', 101),
('N', NULL, 102),
(NULL, NULL, 101),
(NULL, 'Y', 102),
(NULL, 'N', 103),
(NULL, NULL, 101),
('Y', NULL, 102),
('N', NULL, 104),
(NULL, NULL, 105),
('Y', 'N', 101),
('N', NULL, 102),
(NULL, NULL, 101),
(NULL, 'Y', 102),
(NULL, 'N', 103),
(NULL, NULL, 101),
('Y', NULL, 102),
('N', NULL, 104),
(NULL, NULL, 105),
(NULL, 'N', 105),
(NULL, NULL, 101),
(NULL, 'N', 105),
(NULL, NULL, 101),
('N', NULL, 102),
(NULL, NULL, 101),
(NULL, NULL, 101),
(NULL, NULL, 106),
(NULL, NULL, 107),
(NULL, NULL, 108);
** QUERY **
select SEC1,
case
when DEPT in ('101', '104') AND SEC1 is NULL THEN SEC1
when DEPT IN ('102','103') AND SEC1 is NULL THEN 'Y'
else SEC1,
END as SEC1_UPDATE,
SEC2,
case
when DEPT in ('101', '104') AND SEC2 is NULL THEN SEC2
when DEPT IN ('102','103') AND SEC2 is NULL THEN 'Y'
ELSE SEC2 END as SEC2_UPDATE
from EMP_INFO
August 6, 2015 at 10:17 am
If I understand the problem correctly this should work.
select *
, case when DEPT in (102, 103) then SEC1 else isnull(SEC1, 'Y') end as NewSEC1
, ISNULL(SEC2, 'Y') as NewSEC2
from EMP_INFO
where DEPT not in (101, 104)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 6, 2015 at 10:59 am
Sean, wow so simple! I like to code but sometimes I not thinking logic 🙂 Great, it worked 🙂
THANKS.
August 6, 2015 at 12:08 pm
lsalih (8/6/2015)
Sean, wow so simple! I like to code but sometimes I not thinking logic 🙂 Great, it worked 🙂THANKS.
Great. Glad that worked for and thanks for letting me know. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 6, 2015 at 12:13 pm
I was putting else case SEC1 is null then 'Y'
For whatever reason it was given me error, so I thought I should put something like if SEC1 is null begin then put the cases inside it, but I was getting errors either way. I already made the update, and THANK YOU again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply