How to tweak WHERE clause to select all records without Premium = 0 when passing NULL as a parameter in SQL statementHow to tweak WHERE clause to select all records when passing NULL as a parameter in SQL statementSelect columns from result set of stored procedureSQL update from one Table to another based on a ID matchSQL: Dynamic Where Clause using IN statement dying on records with NULL valuesT SQL SELECT statement with complex CASE in WHERE clauseSQL Server R2: select statement with where clauseSelect Statement - selecting first group of results where all entries are NOT NullSQL Cast works in Select but not in Where clauseSelect all data including null if the SQL variable is nullPassing a parameter into a an Openquery select statementHow to tweak WHERE clause to select all records when passing NULL as a parameter in SQL statement
How to show the equivalence between the regularized regression and their constraint formulas using KKT
How could indestructible materials be used in power generation?
Today is the Center
Emailing HOD to enhance faculty application
Forgetting the musical notes while performing in concert
UK: Is there precedent for the governments e-petition site changing the direction of a government decision?
Where does SFDX store details about scratch orgs?
How do I write bicross product symbols in latex?
How can I prevent hyper evolved versions of regular creatures from wiping out their cousins?
Could gravitational lensing be used to protect a spaceship from a laser?
Why is it a bad idea to hire a hitman to eliminate most corrupt politicians?
Does a druid starting with a bow start with no arrows?
In Romance of the Three Kingdoms why do people still use bamboo sticks when papers are already invented?
1960's book about a plague that kills all white people
A reference to a well-known characterization of scattered compact spaces
How much of data wrangling is a data scientist's job?
90's TV series where a boy goes to another dimension through portal near power lines
Western buddy movie with a supernatural twist where a woman turns into an eagle at the end
Why do I get two different answers for this counting problem?
What does it mean to describe someone as a butt steak?
AES: Why is it a good practice to use only the first 16bytes of a hash for encryption?
Fully-Firstable Anagram Sets
How to draw the figure with four pentagons?
Twin primes whose sum is a cube
How to tweak WHERE clause to select all records without Premium = 0 when passing NULL as a parameter in SQL statement
How to tweak WHERE clause to select all records when passing NULL as a parameter in SQL statementSelect columns from result set of stored procedureSQL update from one Table to another based on a ID matchSQL: Dynamic Where Clause using IN statement dying on records with NULL valuesT SQL SELECT statement with complex CASE in WHERE clauseSQL Server R2: select statement with where clauseSelect Statement - selecting first group of results where all entries are NOT NullSQL Cast works in Select but not in Where clauseSelect all data including null if the SQL variable is nullPassing a parameter into a an Openquery select statementHow to tweak WHERE clause to select all records when passing NULL as a parameter in SQL statement
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
The result should return:
- If
@reasonID = 1I need to select only policies that havereasonID = 211 - If
@reasonID = 2I need to select only policies that havereasonID <> 211includingreasonID IS NULL - If
@reasonID = NULLI need to select all policies includingNULLandPremium <> 0
The below example works for @reasonID = 1 and @reasonID = 2:
@reasonID = 1
@reasonID = 2
But how can I tweak the WHERE clause to select all rows when @reasonID = NULL ? Because it returns policies that have Premium = 0, which I do not need.
@reasonID = NULL
declare @TempTable1 table (ControlNo int, PolicyNumber varchar(50), Premium money)
insert into @TempTable1
values (1, 'Pol1', 100), (2, 'Pol2', 0), (3, 'Pol3', 50), (4, 'Pol4', 0),
(5, 'Pol5', 70), (6, 'Pol6', 0), (7, 'Pol7', 30)
declare @TempTable2 table (ControlNo int, PolicyNumber varchar(50), reasonID int)
insert into @TempTable2
values (1, 'Pol1', 5), (2, 'Pol2', NULL), (3, 'Pol3', 211),
(4, 'Pol4', 8), (5, 'Pol5', 211), (6, 'Pol6', NULL),
(7, 'Pol7', 3)
--select * from @TempTable1
--select * from @TempTable2
--Here I input @reasonID parameter
declare @reasonID int = NULL
select
T2.ControlNo, T2.PolicyNumber, T1.Premium, T2.reasonID
from
@TempTable1 T1
inner join
@TempTable2 T2 on t1.ControlNo = T2.ControlNo
where
T1.Premium <> 0
and (case when reasonID = 211 then 1 else 2 end = @reasonID) --works for @reasonID = 1 or @reasonID = 2
or (@reasonID IS NULL) --does not work
But should be like that:
Is any way to modify WHERE clause to achieve desirable result without using HAVING clause or GROUP BY?
sql-server tsql sql-server-2012 edited Mar 8 at 5:29
marc_s
584k13011241270
asked Mar 8 at 0:01
OlegOleg
1,45221953
add a comment |
The result should return:
- If
@reasonID = 1I need to select only policies that havereasonID = 211 - If
@reasonID = 2I need to select only policies that havereasonID <> 211includingreasonID IS NULL - If
@reasonID = NULLI need to select all policies includingNULLandPremium <> 0
The below example works for @reasonID = 1 and @reasonID = 2:
@reasonID = 1
@reasonID = 2
But how can I tweak the WHERE clause to select all rows when @reasonID = NULL ? Because it returns policies that have Premium = 0, which I do not need.
@reasonID = NULL
declare @TempTable1 table (ControlNo int, PolicyNumber varchar(50), Premium money)
insert into @TempTable1
values (1, 'Pol1', 100), (2, 'Pol2', 0), (3, 'Pol3', 50), (4, 'Pol4', 0),
(5, 'Pol5', 70), (6, 'Pol6', 0), (7, 'Pol7', 30)
declare @TempTable2 table (ControlNo int, PolicyNumber varchar(50), reasonID int)
insert into @TempTable2
values (1, 'Pol1', 5), (2, 'Pol2', NULL), (3, 'Pol3', 211),
(4, 'Pol4', 8), (5, 'Pol5', 211), (6, 'Pol6', NULL),
(7, 'Pol7', 3)
--select * from @TempTable1
--select * from @TempTable2
--Here I input @reasonID parameter
declare @reasonID int = NULL
select
T2.ControlNo, T2.PolicyNumber, T1.Premium, T2.reasonID
from
@TempTable1 T1
inner join
@TempTable2 T2 on t1.ControlNo = T2.ControlNo
where
T1.Premium <> 0
and (case when reasonID = 211 then 1 else 2 end = @reasonID) --works for @reasonID = 1 or @reasonID = 2
or (@reasonID IS NULL) --does not work
But should be like that:
Is any way to modify WHERE clause to achieve desirable result without using HAVING clause or GROUP BY?
sql-server tsql sql-server-2012 edited Mar 8 at 5:29
marc_s
584k13011241270
asked Mar 8 at 0:01
OlegOleg
1,45221953
The functioncoalesceis your friend here. Also consider thecase whenexpression.
– Ben
Mar 8 at 0:14
add a comment |
The result should return:
- If
@reasonID = 1I need to select only policies that havereasonID = 211 - If
@reasonID = 2I need to select only policies that havereasonID <> 211includingreasonID IS NULL - If
@reasonID = NULLI need to select all policies includingNULLandPremium <> 0
The below example works for @reasonID = 1 and @reasonID = 2:
@reasonID = 1
@reasonID = 2
But how can I tweak the WHERE clause to select all rows when @reasonID = NULL ? Because it returns policies that have Premium = 0, which I do not need.
@reasonID = NULL
declare @TempTable1 table (ControlNo int, PolicyNumber varchar(50), Premium money)
insert into @TempTable1
values (1, 'Pol1', 100), (2, 'Pol2', 0), (3, 'Pol3', 50), (4, 'Pol4', 0),
(5, 'Pol5', 70), (6, 'Pol6', 0), (7, 'Pol7', 30)
declare @TempTable2 table (ControlNo int, PolicyNumber varchar(50), reasonID int)
insert into @TempTable2
values (1, 'Pol1', 5), (2, 'Pol2', NULL), (3, 'Pol3', 211),
(4, 'Pol4', 8), (5, 'Pol5', 211), (6, 'Pol6', NULL),
(7, 'Pol7', 3)
--select * from @TempTable1
--select * from @TempTable2
--Here I input @reasonID parameter
declare @reasonID int = NULL
select
T2.ControlNo, T2.PolicyNumber, T1.Premium, T2.reasonID
from
@TempTable1 T1
inner join
@TempTable2 T2 on t1.ControlNo = T2.ControlNo
where
T1.Premium <> 0
and (case when reasonID = 211 then 1 else 2 end = @reasonID) --works for @reasonID = 1 or @reasonID = 2
or (@reasonID IS NULL) --does not work
But should be like that:
Is any way to modify WHERE clause to achieve desirable result without using HAVING clause or GROUP BY?
sql-server tsql sql-server-2012 edited Mar 8 at 5:29
marc_s
584k13011241270
asked Mar 8 at 0:01
OlegOleg
1,45221953
The result should return:
- If
@reasonID = 1I need to select only policies that havereasonID = 211 - If
@reasonID = 2I need to select only policies that havereasonID <> 211includingreasonID IS NULL - If
@reasonID = NULLI need to select all policies includingNULLandPremium <> 0
The below example works for @reasonID = 1 and @reasonID = 2:
@reasonID = 1
@reasonID = 2
But how can I tweak the WHERE clause to select all rows when @reasonID = NULL ? Because it returns policies that have Premium = 0, which I do not need.
@reasonID = NULL
declare @TempTable1 table (ControlNo int, PolicyNumber varchar(50), Premium money)
insert into @TempTable1
values (1, 'Pol1', 100), (2, 'Pol2', 0), (3, 'Pol3', 50), (4, 'Pol4', 0),
(5, 'Pol5', 70), (6, 'Pol6', 0), (7, 'Pol7', 30)
declare @TempTable2 table (ControlNo int, PolicyNumber varchar(50), reasonID int)
insert into @TempTable2
values (1, 'Pol1', 5), (2, 'Pol2', NULL), (3, 'Pol3', 211),
(4, 'Pol4', 8), (5, 'Pol5', 211), (6, 'Pol6', NULL),
(7, 'Pol7', 3)
--select * from @TempTable1
--select * from @TempTable2
--Here I input @reasonID parameter
declare @reasonID int = NULL
select
T2.ControlNo, T2.PolicyNumber, T1.Premium, T2.reasonID
from
@TempTable1 T1
inner join
@TempTable2 T2 on t1.ControlNo = T2.ControlNo
where
T1.Premium <> 0
and (case when reasonID = 211 then 1 else 2 end = @reasonID) --works for @reasonID = 1 or @reasonID = 2
or (@reasonID IS NULL) --does not work
But should be like that:
Is any way to modify WHERE clause to achieve desirable result without using HAVING clause or GROUP BY?
sql-server tsql sql-server-2012 sql-server tsql sql-server-2012 edited Mar 8 at 5:29
marc_s
584k13011241270
asked Mar 8 at 0:01
OlegOleg
1,45221953
edited Mar 8 at 5:29
marc_s
584k13011241270
asked Mar 8 at 0:01
OlegOleg
1,45221953
edited Mar 8 at 5:29
marc_s
584k13011241270
edited Mar 8 at 5:29
marc_s
584k13011241270
edited Mar 8 at 5:29
marc_s
584k13011241270
584k13011241270
asked Mar 8 at 0:01
OlegOleg
1,45221953
asked Mar 8 at 0:01
OlegOleg
1,45221953
asked Mar 8 at 0:01
OlegOleg
1,45221953
1,45221953
The functioncoalesceis your friend here. Also consider thecase whenexpression.
– Ben
Mar 8 at 0:14
add a comment |
The functioncoalesceis your friend here. Also consider thecase whenexpression.
– Ben
Mar 8 at 0:14
The function
coalesce is your friend here. Also consider the case when expression.– Ben
Mar 8 at 0:14
The function
coalesce is your friend here. Also consider the case when expression.– Ben
Mar 8 at 0:14
add a comment |
2 Answers
2
active
oldest
votes
I think you may have missed to add one parenthesis. Also, I modified your case statement in where condition because your else condition will take consideration of null values as well. Since you have or condtion for nulls it does not matter right now. I assumed you may not want null condition in your case statement so I changed it.I did not see Policy 8 in your input, so it does not generate the output of 8. rest is same.
declare @TempTable1 table (ControlNo int,PolicyNumber varchar(50), Premium money)
insert into @TempTable1 values (1,'Pol1', 100),
(2,'Pol2', 0),
(3,'Pol3', 50),
(4,'Pol4', 0),
(5,'Pol5', 70),
(6,'Pol6', 0),
(7, 'Pol7',30)
declare @TempTable2 table (ControlNo int,PolicyNumber varchar(50), reasonID int)
insert into @TempTable2 values (1,'Pol1', 5),
(2,'Pol2', NULL),
(3,'Pol3', 211),
(4,'Pol4', 8),
(5,'Pol5', 211),
(6,'Pol6', NULL),
(7,'Pol7',3)
--select * from @TempTable1
--select * from @TempTable2
--Here I input @reasonID parameter
declare @reasonID int = NULL
select T2.ControlNo,T2.PolicyNumber, T1.Premium, T2.reasonID
from @TempTable1 T1
inner join @TempTable2 T2 on t1.ControlNo = T2.ControlNo
where T1.Premium <> 0
and ((case when reasonID = 211 then 1
when isnull(reasonID,'') not in (211,'') then 2 end = @reasonID) --works for @reasonID = 1 or @reasonID = 2
OR (@reasonID IS NULL)) --does not work (added parentheses)
Output: Now it does not bring premium <> 0
ControlNo PolicyNumber Premium reasonID
1 Pol1 100.00 5
3 Pol3 50.00 211
5 Pol5 70.00 211
7 Pol7 30.00 3
answered Mar 8 at 0:19
AviAvi
809314
Thanks, but I am trying also bring rows that do have NULL values for reasonID if parameter = 2. So it works for@reasonID= 1and@reasonID= NULL, but not for@reasonID= 2
– Oleg
Mar 8 at 0:30
I added ORreasonIDIS NULL and it works.
– Oleg
Mar 8 at 0:35
@Oleg you want without condition (@reasonID IS NULL) ?
– Avi
Mar 8 at 0:43
When@reasonID= NULL I want it to select any` reasonID` (including NULL) butPremiumshould be <> 0
– Oleg
Mar 8 at 0:46
Badically this one works as well:AND ( (CASE WHEN tblQuotes.QuoteStatusReasonID = 211 THEN 1 WHEN QuoteStatusReasonID <> 211 OR QuoteStatusReasonID IS NULL THEN 2 END = @isExonorated) OR (@isExonorated IS NULL) )
– Oleg
Mar 8 at 0:53
add a comment |
I believe you need something like this:
select *
from @TempTable1 t1
join @TempTable2 t2 on t1.ControlNo = t2.ControlNo
where t1.Premium <> 0
and (
(@reasonID is null)
or
(@reasonID = 1 and t2.reasonID = 211)
or
(@reasonID = 2 and (t2.reasonID <> 211 or t2.reasonID is null))
)
Data:
declare @TempTable1 table (ControlNo int,PolicyNumber varchar(50), Premium money)
insert into @TempTable1 values (1,'Pol1', 100),
(2,'Pol2', 0),
(3,'Pol3', 50),
(4,'Pol4', 0),
(5,'Pol5', 70),
(6,'Pol6', 0),
(7, 'Pol7',30),
(8, 'Pol8',10)
declare @TempTable2 table (ControlNo int,PolicyNumber varchar(50), reasonID int)
insert into @TempTable2 values (1,'Pol1', 5),
(2,'Pol2', NULL),
(3,'Pol3', 211),
(4,'Pol4', 8),
(5,'Pol5', 211),
(6,'Pol6', NULL),
(7,'Pol7',3),
(8,'Pol8',null)
For @reasonID = null:
ControlNo PolicyNumber Premium ControlNo PolicyNumber reasonID
----------- ------------ --------------------- ----------- ------------ -----------
1 Pol1 100.00 1 Pol1 5
3 Pol3 50.00 3 Pol3 211
5 Pol5 70.00 5 Pol5 211
7 Pol7 30.00 7 Pol7 3
8 Pol8 10.00 8 Pol8 NULL
For @reasonID = 1:
ControlNo PolicyNumber Premium ControlNo PolicyNumber reasonID
----------- ------------ --------------------- ----------- ------------ -----------
3 Pol3 50.00 3 Pol3 211
5 Pol5 70.00 5 Pol5 211
For @reasonID = 2:
ControlNo PolicyNumber Premium ControlNo PolicyNumber reasonID
----------- ------------ --------------------- ----------- ------------ -----------
1 Pol1 100.00 1 Pol1 5
7 Pol7 30.00 7 Pol7 3
8 Pol8 10.00 8 Pol8 NULL
answered Mar 8 at 1:42
Kirill PolishchukKirill Polishchuk
45k899107
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55054756%2fhow-to-tweak-where-clause-to-select-all-records-without-premium-0-when-passing%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
I think you may have missed to add one parenthesis. Also, I modified your case statement in where condition because your else condition will take consideration of null values as well. Since you have or condtion for nulls it does not matter right now. I assumed you may not want null condition in your case statement so I changed it.I did not see Policy 8 in your input, so it does not generate the output of 8. rest is same.
declare @TempTable1 table (ControlNo int,PolicyNumber varchar(50), Premium money)
insert into @TempTable1 values (1,'Pol1', 100),
(2,'Pol2', 0),
(3,'Pol3', 50),
(4,'Pol4', 0),
(5,'Pol5', 70),
(6,'Pol6', 0),
(7, 'Pol7',30)
declare @TempTable2 table (ControlNo int,PolicyNumber varchar(50), reasonID int)
insert into @TempTable2 values (1,'Pol1', 5),
(2,'Pol2', NULL),
(3,'Pol3', 211),
(4,'Pol4', 8),
(5,'Pol5', 211),
(6,'Pol6', NULL),
(7,'Pol7',3)
--select * from @TempTable1
--select * from @TempTable2
--Here I input @reasonID parameter
declare @reasonID int = NULL
select T2.ControlNo,T2.PolicyNumber, T1.Premium, T2.reasonID
from @TempTable1 T1
inner join @TempTable2 T2 on t1.ControlNo = T2.ControlNo
where T1.Premium <> 0
and ((case when reasonID = 211 then 1
when isnull(reasonID,'') not in (211,'') then 2 end = @reasonID) --works for @reasonID = 1 or @reasonID = 2
OR (@reasonID IS NULL)) --does not work (added parentheses)
Output: Now it does not bring premium <> 0
ControlNo PolicyNumber Premium reasonID
1 Pol1 100.00 5
3 Pol3 50.00 211
5 Pol5 70.00 211
7 Pol7 30.00 3
answered Mar 8 at 0:19
AviAvi
809314
Thanks, but I am trying also bring rows that do have NULL values for reasonID if parameter = 2. So it works for@reasonID= 1and@reasonID= NULL, but not for@reasonID= 2
– Oleg
Mar 8 at 0:30
I added ORreasonIDIS NULL and it works.
– Oleg
Mar 8 at 0:35
@Oleg you want without condition (@reasonID IS NULL) ?
– Avi
Mar 8 at 0:43
When@reasonID= NULL I want it to select any` reasonID` (including NULL) butPremiumshould be <> 0
– Oleg
Mar 8 at 0:46
Badically this one works as well:AND ( (CASE WHEN tblQuotes.QuoteStatusReasonID = 211 THEN 1 WHEN QuoteStatusReasonID <> 211 OR QuoteStatusReasonID IS NULL THEN 2 END = @isExonorated) OR (@isExonorated IS NULL) )
– Oleg
Mar 8 at 0:53
add a comment |
I think you may have missed to add one parenthesis. Also, I modified your case statement in where condition because your else condition will take consideration of null values as well. Since you have or condtion for nulls it does not matter right now. I assumed you may not want null condition in your case statement so I changed it.I did not see Policy 8 in your input, so it does not generate the output of 8. rest is same.
declare @TempTable1 table (ControlNo int,PolicyNumber varchar(50), Premium money)
insert into @TempTable1 values (1,'Pol1', 100),
(2,'Pol2', 0),
(3,'Pol3', 50),
(4,'Pol4', 0),
(5,'Pol5', 70),
(6,'Pol6', 0),
(7, 'Pol7',30)
declare @TempTable2 table (ControlNo int,PolicyNumber varchar(50), reasonID int)
insert into @TempTable2 values (1,'Pol1', 5),
(2,'Pol2', NULL),
(3,'Pol3', 211),
(4,'Pol4', 8),
(5,'Pol5', 211),
(6,'Pol6', NULL),
(7,'Pol7',3)
--select * from @TempTable1
--select * from @TempTable2
--Here I input @reasonID parameter
declare @reasonID int = NULL
select T2.ControlNo,T2.PolicyNumber, T1.Premium, T2.reasonID
from @TempTable1 T1
inner join @TempTable2 T2 on t1.ControlNo = T2.ControlNo
where T1.Premium <> 0
and ((case when reasonID = 211 then 1
when isnull(reasonID,'') not in (211,'') then 2 end = @reasonID) --works for @reasonID = 1 or @reasonID = 2
OR (@reasonID IS NULL)) --does not work (added parentheses)
Output: Now it does not bring premium <> 0
ControlNo PolicyNumber Premium reasonID
1 Pol1 100.00 5
3 Pol3 50.00 211
5 Pol5 70.00 211
7 Pol7 30.00 3
answered Mar 8 at 0:19
AviAvi
809314
Thanks, but I am trying also bring rows that do have NULL values for reasonID if parameter = 2. So it works for@reasonID= 1and@reasonID= NULL, but not for@reasonID= 2
– Oleg
Mar 8 at 0:30
I added ORreasonIDIS NULL and it works.
– Oleg
Mar 8 at 0:35
@Oleg you want without condition (@reasonID IS NULL) ?
– Avi
Mar 8 at 0:43
When@reasonID= NULL I want it to select any` reasonID` (including NULL) butPremiumshould be <> 0
– Oleg
Mar 8 at 0:46
Badically this one works as well:AND ( (CASE WHEN tblQuotes.QuoteStatusReasonID = 211 THEN 1 WHEN QuoteStatusReasonID <> 211 OR QuoteStatusReasonID IS NULL THEN 2 END = @isExonorated) OR (@isExonorated IS NULL) )
– Oleg
Mar 8 at 0:53
add a comment |
I think you may have missed to add one parenthesis. Also, I modified your case statement in where condition because your else condition will take consideration of null values as well. Since you have or condtion for nulls it does not matter right now. I assumed you may not want null condition in your case statement so I changed it.I did not see Policy 8 in your input, so it does not generate the output of 8. rest is same.
declare @TempTable1 table (ControlNo int,PolicyNumber varchar(50), Premium money)
insert into @TempTable1 values (1,'Pol1', 100),
(2,'Pol2', 0),
(3,'Pol3', 50),
(4,'Pol4', 0),
(5,'Pol5', 70),
(6,'Pol6', 0),
(7, 'Pol7',30)
declare @TempTable2 table (ControlNo int,PolicyNumber varchar(50), reasonID int)
insert into @TempTable2 values (1,'Pol1', 5),
(2,'Pol2', NULL),
(3,'Pol3', 211),
(4,'Pol4', 8),
(5,'Pol5', 211),
(6,'Pol6', NULL),
(7,'Pol7',3)
--select * from @TempTable1
--select * from @TempTable2
--Here I input @reasonID parameter
declare @reasonID int = NULL
select T2.ControlNo,T2.PolicyNumber, T1.Premium, T2.reasonID
from @TempTable1 T1
inner join @TempTable2 T2 on t1.ControlNo = T2.ControlNo
where T1.Premium <> 0
and ((case when reasonID = 211 then 1
when isnull(reasonID,'') not in (211,'') then 2 end = @reasonID) --works for @reasonID = 1 or @reasonID = 2
OR (@reasonID IS NULL)) --does not work (added parentheses)
Output: Now it does not bring premium <> 0
ControlNo PolicyNumber Premium reasonID
1 Pol1 100.00 5
3 Pol3 50.00 211
5 Pol5 70.00 211
7 Pol7 30.00 3
answered Mar 8 at 0:19
AviAvi
809314
I think you may have missed to add one parenthesis. Also, I modified your case statement in where condition because your else condition will take consideration of null values as well. Since you have or condtion for nulls it does not matter right now. I assumed you may not want null condition in your case statement so I changed it.I did not see Policy 8 in your input, so it does not generate the output of 8. rest is same.
declare @TempTable1 table (ControlNo int,PolicyNumber varchar(50), Premium money)
insert into @TempTable1 values (1,'Pol1', 100),
(2,'Pol2', 0),
(3,'Pol3', 50),
(4,'Pol4', 0),
(5,'Pol5', 70),
(6,'Pol6', 0),
(7, 'Pol7',30)
declare @TempTable2 table (ControlNo int,PolicyNumber varchar(50), reasonID int)
insert into @TempTable2 values (1,'Pol1', 5),
(2,'Pol2', NULL),
(3,'Pol3', 211),
(4,'Pol4', 8),
(5,'Pol5', 211),
(6,'Pol6', NULL),
(7,'Pol7',3)
--select * from @TempTable1
--select * from @TempTable2
--Here I input @reasonID parameter
declare @reasonID int = NULL
select T2.ControlNo,T2.PolicyNumber, T1.Premium, T2.reasonID
from @TempTable1 T1
inner join @TempTable2 T2 on t1.ControlNo = T2.ControlNo
where T1.Premium <> 0
and ((case when reasonID = 211 then 1
when isnull(reasonID,'') not in (211,'') then 2 end = @reasonID) --works for @reasonID = 1 or @reasonID = 2
OR (@reasonID IS NULL)) --does not work (added parentheses)
Output: Now it does not bring premium <> 0
ControlNo PolicyNumber Premium reasonID
1 Pol1 100.00 5
3 Pol3 50.00 211
5 Pol5 70.00 211
7 Pol7 30.00 3
answered Mar 8 at 0:19
AviAvi
809314
answered Mar 8 at 0:19
AviAvi
809314
answered Mar 8 at 0:19
AviAvi
809314
answered Mar 8 at 0:19
AviAvi
809314
809314
Thanks, but I am trying also bring rows that do have NULL values for reasonID if parameter = 2. So it works for@reasonID= 1and@reasonID= NULL, but not for@reasonID= 2
– Oleg
Mar 8 at 0:30
I added ORreasonIDIS NULL and it works.
– Oleg
Mar 8 at 0:35
@Oleg you want without condition (@reasonID IS NULL) ?
– Avi
Mar 8 at 0:43
When@reasonID= NULL I want it to select any` reasonID` (including NULL) butPremiumshould be <> 0
– Oleg
Mar 8 at 0:46
Badically this one works as well:AND ( (CASE WHEN tblQuotes.QuoteStatusReasonID = 211 THEN 1 WHEN QuoteStatusReasonID <> 211 OR QuoteStatusReasonID IS NULL THEN 2 END = @isExonorated) OR (@isExonorated IS NULL) )
– Oleg
Mar 8 at 0:53
add a comment |
Thanks, but I am trying also bring rows that do have NULL values for reasonID if parameter = 2. So it works for@reasonID= 1and@reasonID= NULL, but not for@reasonID= 2
– Oleg
Mar 8 at 0:30
I added ORreasonIDIS NULL and it works.
– Oleg
Mar 8 at 0:35
@Oleg you want without condition (@reasonID IS NULL) ?
– Avi
Mar 8 at 0:43
When@reasonID= NULL I want it to select any` reasonID` (including NULL) butPremiumshould be <> 0
– Oleg
Mar 8 at 0:46
Badically this one works as well:AND ( (CASE WHEN tblQuotes.QuoteStatusReasonID = 211 THEN 1 WHEN QuoteStatusReasonID <> 211 OR QuoteStatusReasonID IS NULL THEN 2 END = @isExonorated) OR (@isExonorated IS NULL) )
– Oleg
Mar 8 at 0:53
Thanks, but I am trying also bring rows that do have NULL values for reasonID if parameter = 2. So it works for
@reasonID= 1 and @reasonID = NULL, but not for @reasonID = 2– Oleg
Mar 8 at 0:30
Thanks, but I am trying also bring rows that do have NULL values for reasonID if parameter = 2. So it works for
@reasonID= 1 and @reasonID = NULL, but not for @reasonID = 2– Oleg
Mar 8 at 0:30
I added OR
reasonID IS NULL and it works.– Oleg
Mar 8 at 0:35
I added OR
reasonID IS NULL and it works.– Oleg
Mar 8 at 0:35
@Oleg you want without condition (@reasonID IS NULL) ?
– Avi
Mar 8 at 0:43
@Oleg you want without condition (@reasonID IS NULL) ?
– Avi
Mar 8 at 0:43
When
@reasonID = NULL I want it to select any` reasonID` (including NULL) but Premium should be <> 0– Oleg
Mar 8 at 0:46
When
@reasonID = NULL I want it to select any` reasonID` (including NULL) but Premium should be <> 0– Oleg
Mar 8 at 0:46
Badically this one works as well:
AND ( (CASE WHEN tblQuotes.QuoteStatusReasonID = 211 THEN 1 WHEN QuoteStatusReasonID <> 211 OR QuoteStatusReasonID IS NULL THEN 2 END = @isExonorated) OR (@isExonorated IS NULL) ) – Oleg
Mar 8 at 0:53
Badically this one works as well:
AND ( (CASE WHEN tblQuotes.QuoteStatusReasonID = 211 THEN 1 WHEN QuoteStatusReasonID <> 211 OR QuoteStatusReasonID IS NULL THEN 2 END = @isExonorated) OR (@isExonorated IS NULL) ) – Oleg
Mar 8 at 0:53
add a comment |
I believe you need something like this:
select *
from @TempTable1 t1
join @TempTable2 t2 on t1.ControlNo = t2.ControlNo
where t1.Premium <> 0
and (
(@reasonID is null)
or
(@reasonID = 1 and t2.reasonID = 211)
or
(@reasonID = 2 and (t2.reasonID <> 211 or t2.reasonID is null))
)
Data:
declare @TempTable1 table (ControlNo int,PolicyNumber varchar(50), Premium money)
insert into @TempTable1 values (1,'Pol1', 100),
(2,'Pol2', 0),
(3,'Pol3', 50),
(4,'Pol4', 0),
(5,'Pol5', 70),
(6,'Pol6', 0),
(7, 'Pol7',30),
(8, 'Pol8',10)
declare @TempTable2 table (ControlNo int,PolicyNumber varchar(50), reasonID int)
insert into @TempTable2 values (1,'Pol1', 5),
(2,'Pol2', NULL),
(3,'Pol3', 211),
(4,'Pol4', 8),
(5,'Pol5', 211),
(6,'Pol6', NULL),
(7,'Pol7',3),
(8,'Pol8',null)
For @reasonID = null:
ControlNo PolicyNumber Premium ControlNo PolicyNumber reasonID
----------- ------------ --------------------- ----------- ------------ -----------
1 Pol1 100.00 1 Pol1 5
3 Pol3 50.00 3 Pol3 211
5 Pol5 70.00 5 Pol5 211
7 Pol7 30.00 7 Pol7 3
8 Pol8 10.00 8 Pol8 NULL
For @reasonID = 1:
ControlNo PolicyNumber Premium ControlNo PolicyNumber reasonID
----------- ------------ --------------------- ----------- ------------ -----------
3 Pol3 50.00 3 Pol3 211
5 Pol5 70.00 5 Pol5 211
For @reasonID = 2:
ControlNo PolicyNumber Premium ControlNo PolicyNumber reasonID
----------- ------------ --------------------- ----------- ------------ -----------
1 Pol1 100.00 1 Pol1 5
7 Pol7 30.00 7 Pol7 3
8 Pol8 10.00 8 Pol8 NULL
answered Mar 8 at 1:42
Kirill PolishchukKirill Polishchuk
45k899107
add a comment |
I believe you need something like this:
select *
from @TempTable1 t1
join @TempTable2 t2 on t1.ControlNo = t2.ControlNo
where t1.Premium <> 0
and (
(@reasonID is null)
or
(@reasonID = 1 and t2.reasonID = 211)
or
(@reasonID = 2 and (t2.reasonID <> 211 or t2.reasonID is null))
)
Data:
declare @TempTable1 table (ControlNo int,PolicyNumber varchar(50), Premium money)
insert into @TempTable1 values (1,'Pol1', 100),
(2,'Pol2', 0),
(3,'Pol3', 50),
(4,'Pol4', 0),
(5,'Pol5', 70),
(6,'Pol6', 0),
(7, 'Pol7',30),
(8, 'Pol8',10)
declare @TempTable2 table (ControlNo int,PolicyNumber varchar(50), reasonID int)
insert into @TempTable2 values (1,'Pol1', 5),
(2,'Pol2', NULL),
(3,'Pol3', 211),
(4,'Pol4', 8),
(5,'Pol5', 211),
(6,'Pol6', NULL),
(7,'Pol7',3),
(8,'Pol8',null)
For @reasonID = null:
ControlNo PolicyNumber Premium ControlNo PolicyNumber reasonID
----------- ------------ --------------------- ----------- ------------ -----------
1 Pol1 100.00 1 Pol1 5
3 Pol3 50.00 3 Pol3 211
5 Pol5 70.00 5 Pol5 211
7 Pol7 30.00 7 Pol7 3
8 Pol8 10.00 8 Pol8 NULL
For @reasonID = 1:
ControlNo PolicyNumber Premium ControlNo PolicyNumber reasonID
----------- ------------ --------------------- ----------- ------------ -----------
3 Pol3 50.00 3 Pol3 211
5 Pol5 70.00 5 Pol5 211
For @reasonID = 2:
ControlNo PolicyNumber Premium ControlNo PolicyNumber reasonID
----------- ------------ --------------------- ----------- ------------ -----------
1 Pol1 100.00 1 Pol1 5
7 Pol7 30.00 7 Pol7 3
8 Pol8 10.00 8 Pol8 NULL
answered Mar 8 at 1:42
Kirill PolishchukKirill Polishchuk
45k899107
add a comment |
I believe you need something like this:
select *
from @TempTable1 t1
join @TempTable2 t2 on t1.ControlNo = t2.ControlNo
where t1.Premium <> 0
and (
(@reasonID is null)
or
(@reasonID = 1 and t2.reasonID = 211)
or
(@reasonID = 2 and (t2.reasonID <> 211 or t2.reasonID is null))
)
Data:
declare @TempTable1 table (ControlNo int,PolicyNumber varchar(50), Premium money)
insert into @TempTable1 values (1,'Pol1', 100),
(2,'Pol2', 0),
(3,'Pol3', 50),
(4,'Pol4', 0),
(5,'Pol5', 70),
(6,'Pol6', 0),
(7, 'Pol7',30),
(8, 'Pol8',10)
declare @TempTable2 table (ControlNo int,PolicyNumber varchar(50), reasonID int)
insert into @TempTable2 values (1,'Pol1', 5),
(2,'Pol2', NULL),
(3,'Pol3', 211),
(4,'Pol4', 8),
(5,'Pol5', 211),
(6,'Pol6', NULL),
(7,'Pol7',3),
(8,'Pol8',null)
For @reasonID = null:
ControlNo PolicyNumber Premium ControlNo PolicyNumber reasonID
----------- ------------ --------------------- ----------- ------------ -----------
1 Pol1 100.00 1 Pol1 5
3 Pol3 50.00 3 Pol3 211
5 Pol5 70.00 5 Pol5 211
7 Pol7 30.00 7 Pol7 3
8 Pol8 10.00 8 Pol8 NULL
For @reasonID = 1:
ControlNo PolicyNumber Premium ControlNo PolicyNumber reasonID
----------- ------------ --------------------- ----------- ------------ -----------
3 Pol3 50.00 3 Pol3 211
5 Pol5 70.00 5 Pol5 211
For @reasonID = 2:
ControlNo PolicyNumber Premium ControlNo PolicyNumber reasonID
----------- ------------ --------------------- ----------- ------------ -----------
1 Pol1 100.00 1 Pol1 5
7 Pol7 30.00 7 Pol7 3
8 Pol8 10.00 8 Pol8 NULL
answered Mar 8 at 1:42
Kirill PolishchukKirill Polishchuk
45k899107
I believe you need something like this:
select *
from @TempTable1 t1
join @TempTable2 t2 on t1.ControlNo = t2.ControlNo
where t1.Premium <> 0
and (
(@reasonID is null)
or
(@reasonID = 1 and t2.reasonID = 211)
or
(@reasonID = 2 and (t2.reasonID <> 211 or t2.reasonID is null))
)
Data:
declare @TempTable1 table (ControlNo int,PolicyNumber varchar(50), Premium money)
insert into @TempTable1 values (1,'Pol1', 100),
(2,'Pol2', 0),
(3,'Pol3', 50),
(4,'Pol4', 0),
(5,'Pol5', 70),
(6,'Pol6', 0),
(7, 'Pol7',30),
(8, 'Pol8',10)
declare @TempTable2 table (ControlNo int,PolicyNumber varchar(50), reasonID int)
insert into @TempTable2 values (1,'Pol1', 5),
(2,'Pol2', NULL),
(3,'Pol3', 211),
(4,'Pol4', 8),
(5,'Pol5', 211),
(6,'Pol6', NULL),
(7,'Pol7',3),
(8,'Pol8',null)
For @reasonID = null:
ControlNo PolicyNumber Premium ControlNo PolicyNumber reasonID
----------- ------------ --------------------- ----------- ------------ -----------
1 Pol1 100.00 1 Pol1 5
3 Pol3 50.00 3 Pol3 211
5 Pol5 70.00 5 Pol5 211
7 Pol7 30.00 7 Pol7 3
8 Pol8 10.00 8 Pol8 NULL
For @reasonID = 1:
ControlNo PolicyNumber Premium ControlNo PolicyNumber reasonID
----------- ------------ --------------------- ----------- ------------ -----------
3 Pol3 50.00 3 Pol3 211
5 Pol5 70.00 5 Pol5 211
For @reasonID = 2:
ControlNo PolicyNumber Premium ControlNo PolicyNumber reasonID
----------- ------------ --------------------- ----------- ------------ -----------
1 Pol1 100.00 1 Pol1 5
7 Pol7 30.00 7 Pol7 3
8 Pol8 10.00 8 Pol8 NULL
answered Mar 8 at 1:42
Kirill PolishchukKirill Polishchuk
45k899107
edited Mar 8 at 1:49
answered Mar 8 at 1:42
Kirill PolishchukKirill Polishchuk
45k899107
answered Mar 8 at 1:42
Kirill PolishchukKirill Polishchuk
45k899107
answered Mar 8 at 1:42
Kirill PolishchukKirill Polishchuk
45k899107
45k899107
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55054756%2fhow-to-tweak-where-clause-to-select-all-records-without-premium-0-when-passing%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
The function
coalesceis your friend here. Also consider thecase whenexpression.– Ben
Mar 8 at 0:14