February 28, 2018 at 8:16 am
Hello,
I'm doing this:
...
union
select 'EVY', 'Everyone', ...
from ...
where ... and @Grouping = 'false'
That @Grouping = false still returns the row if the value is true. I've worked out because I'm using constant values - 'EVY', 'Everyone' - instead of column values. If I change to column values it works as expected.
How can I use constant values this way?
Thanks
February 28, 2018 at 8:53 am
lanky_doodle - Wednesday, February 28, 2018 8:16 AMHello,I'm doing this:
...
union
select 'EVY', 'Everyone', ...
from ...
where ... and @Grouping = 'false'That @Grouping = false still returns the row if the value is true. I've worked out because I'm using constant values - 'EVY', 'Everyone' - instead of column values. If I change to column values it works as expected.
How can I use constant values this way?
Thanks
What exactly are you trying to do, way too much of that code is redacted to be useful. But in general UNION does an implicit DISTINCT on the entire result set so if you have a bunch of selects in the union that all return the same constant result set if if any of them return anything you'll get a single row back with that constant result.
SELECT * FROM (VALUES ('Hello'), ('Hello')) TESTY(COL_ONE)
UNION
SELECT 'Hello'
February 28, 2018 at 9:17 am
lanky_doodle - Wednesday, February 28, 2018 8:16 AMHello,I'm doing this:
...
union
select 'EVY', 'Everyone', ...
from ...
where ... and @Grouping = 'false'That @Grouping = false still returns the row if the value is true. I've worked out because I'm using constant values - 'EVY', 'Everyone' - instead of column values. If I change to column values it works as expected.
How can I use constant values this way?
Thanks
The only way that 'EVY', 'Everyone' will return is if the WHERE clause evaluates to TRUE, so the value of @Grouping must be 'false'.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 28, 2018 at 10:13 am
The data returned is presented in a web app. I want to return a lit of departments that are bound to a .NET repeater control, which itself has a nested repeater control for people in each group. The pairing is done based on the DEP_Code value (this exists for each user). The first SELECT is a list of favourite people for each person.
That means the presentation in the web app is grouped by Department. I want the ability to turn of grouping, so it would be just a list of people. I still want Favourites to be there whether grouping is on or off.
So:
select'FAV' DEP_Code, 'Favourites' DEP_Name, cast(1 as bit) Favourite, 1 [Rank],
count(a.EMP_ClockNumber) Total,
count(case when a.ACT_Status = 'In' then a.EMP_ClockNumber end) [In],
count(case when a.ACT_Status = 'Out' then a.EMP_ClockNumber end) [Out],
count(case when a.ACT_Status = 'Break' then a.EMP_ClockNumber end) [Break],
count(case when a.ACT_Status = 'Absent' then a.EMP_ClockNumber end) [Absent]
fromtbl_Users u join
tbl_User_Favourites uf on uf.Person_ID = u.[User_ID] and uf.[User_ID] = @user-id left join
[(local)\ISYS].Intelligent.dbo.Employees e on e.EMP_Number collate database_default = u.User_Number left join
[(local)\ISYS].Intelligent.dbo.vwStaffDayActivity1 a on a.EMP_ClockNumber = e.EMP_ClockNumber
where(getdate() >= e.EMP_Join_Date) and
(
(e.EMP_Left_Date is null) or
(
getdate() <=
case when e.EMP_Proposal_Date <= e.EMP_Left_Date then e.EMP_Proposal_Date
else e.EMP_Left_Date
end
)
)
union
select'EVY', 'Everyone', cast(0 as bit), 2,
count(a.EMP_ClockNumber) Total,
count(case when a.ACT_Status = 'In' then a.EMP_ClockNumber end),
count(case when a.ACT_Status = 'Out' then a.EMP_ClockNumber end),
count(case when a.ACT_Status = 'Break' then a.EMP_ClockNumber end),
count(case when a.ACT_Status = 'Absent' then a.EMP_ClockNumber end)
from[(local)\ISYS].Intelligent.dbo.Employees e left join
[(local)\ISYS].Intelligent.dbo.vwStaffDayActivity1 a on a.EMP_ClockNumber = e.EMP_ClockNumber
where(getdate() >= e.EMP_Join_Date) and
(
(e.EMP_Left_Date is null) or
(
getdate() <=
case when e.EMP_Proposal_Date <= e.EMP_Left_Date then e.EMP_Proposal_Date
else e.EMP_Left_Date
end
)
) and @Grouping = 'false'
union
selectcoalesce(s.SDP_Code, d.DEP_Code), coalesce(s.SDP_Name, d.DEP_Name), cast(0 as bit),
cast(replace(coalesce(s.SDP_Email, d.DEP_Email), '@', '') as int),
count(a.EMP_ClockNumber),
count(case when a.ACT_Status = 'In' then a.EMP_ClockNumber end),
count(case when a.ACT_Status = 'Out' then a.EMP_ClockNumber end),
count(case when a.ACT_Status = 'Break' then a.EMP_ClockNumber end),
count(case when a.ACT_Status = 'Absent' then a.EMP_ClockNumber end)
from[(local)\ISYS].Intelligent.dbo.Employees e left join
[(local)\ISYS].Intelligent.dbo.Departments d on d.DEP_Code = e.EMP_DEP_Code left join
[(local)\ISYS].Intelligent.dbo.[Sub Departments] s on s.SDP_Code = e.EMP_SUB_DEP_Code left join
[(local)\ISYS].Intelligent.dbo.vwStaffDayActivity1 a on a.EMP_ClockNumber = e.EMP_ClockNumber
where(getdate() >= e.EMP_Join_Date) and
(
(e.EMP_Left_Date is null) or
(
getdate() <=
case when e.EMP_Proposal_Date <= e.EMP_Left_Date then e.EMP_Proposal_Date
else e.EMP_Left_Date
end
)
) and @Grouping = 'true'
group bycoalesce(s.SDP_Code, d.DEP_Code), coalesce(s.SDP_Name, d.DEP_Name),
cast(replace(coalesce(s.SDP_Email, d.DEP_Email), '@', '') as int)
order by[Rank];
When grouping is false, this is the dataset which is spot on:
DEP_Code DEP_Name Favourite Rank Total In Out Break Absent
FAV Favourites 1 1 5 1 2 0 2
EVY Everyone 0 2 73 13 49 0 11
But when grouping is true, the Everyone select is still there, because I am using literal values.
DEP_Code DEP_Name Favourite Rank Total In Out Break Absent
FAV Favourites 1 1 5 1 2 0 2
EVY Everyone 0 2 0 0 0 0 0
PART Partners 0 2 11 5 3 0 3
ATTO Attorneys 0 3 7 1 6 0 0
TRAIN Trainees 0 4 14 5 4 0 5
CON Consultants 0 5 1 0 1 0 0
LON PAs Group 1 0 6 6 0 6 0 0
SEV PAs Group 2 0 7 11 1 10 0 0
ACHR Accounts & HR 0 8 11 0 11 0 0
IT IT 0 9 2 0 0 0 2
REC Records 0 10 10 1 8 0 1
I want favourites plus everyone or favourites plus groups.
February 28, 2018 at 10:31 am
lanky_doodle - Wednesday, February 28, 2018 10:13 AMBut when grouping is true, the Everyone select is still there, because I am using literal values.
The SELECT clause is evaluated AFTER the WHERE clause, so using literal values in the SELECT clause cannot possibly have any effect on the number of records returned. Specifically, it cannot cause records to return in place of an empty set. You need to look elsewhere to determine why those values are showing up in your results. I would start by running each of your UNIONed sets separately.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 28, 2018 at 10:55 am
Is it possible that you have a group that is called everyone? The numbers are different on your results.
February 28, 2018 at 10:58 am
You should change your UNION to UNION ALL
February 28, 2018 at 2:37 pm
I noticed that your query does not identify the data type of the @Grouping variable. You are testing it for equality with a character string, so if it's a bit data type, you aren't going to get the results you are expecting, as you can't test a bit for 'true' or for 'false', as SQL Server does not recognize those strings as boolean values. You have to actually test for either 1 or 0 for a bit data type, where 1 is generally true, and 0 is generally false. On the other hand, if it has a varchar(5) (or longer) data type, and you know that the web app supplies character strings that are either the word true or the word false, you would be okay, but if that app returns a boolean value, it's the problem.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 28, 2018 at 3:18 pm
sgmunson - Wednesday, February 28, 2018 2:37 PMI noticed that your query does not identify the data type of the @Grouping variable. You are testing it for equality with a character string, so if it's a bit data type, you aren't going to get the results you are expecting, as you can't test a bit for 'true' or for 'false', as SQL Server does not recognize those strings as boolean values. You have to actually test for either 1 or 0 for a bit data type, where 1 is generally true, and 0 is generally false. On the other hand, if it has a varchar(5) (or longer) data type, and you know that the web app supplies character strings that are either the word true or the word false, you would be okay, but if that app returns a boolean value, it's the problem.
Actually, a string with the value 'false' will be converted to 0 when casted to bit.
March 1, 2018 at 1:35 am
sgmunson - Wednesday, February 28, 2018 2:37 PMI noticed that your query does not identify the data type of the @Grouping variable. You are testing it for equality with a character string, so if it's a bit data type, you aren't going to get the results you are expecting, as you can't test a bit for 'true' or for 'false', as SQL Server does not recognize those strings as boolean values. You have to actually test for either 1 or 0 for a bit data type, where 1 is generally true, and 0 is generally false. On the other hand, if it has a varchar(5) (or longer) data type, and you know that the web app supplies character strings that are either the word true or the word false, you would be okay, but if that app returns a boolean value, it's the problem.
This is in a sproc, and this variable is a parameter.
ALTER procedure [dbo].[genSelPeopleGroups]
@user-id uniqueidentifier,
@Grouping bit
as
...
March 1, 2018 at 1:44 am
What I can't understand is if I change the 2nd select to column names:
From:
select 'EVY', 'Everyone', cast(0 as bit), 2,
...
To:
select e.EMP_Forename, e.EMP_Surname, cast(0 as bit), 2,
...
This is the resultset when grouping = true (or 1), which is right:
DEP_Code DEP_Name Favourite Rank Total In Out Break Absent
FAV Favourites 1 1 5 2 3 0 0
PART Partners 0 2 11 5 6 0 0
ATTO Attorneys 0 3 7 2 5 0 0
TRAIN Trainees 0 4 14 6 4 0 4
CON Consultants 0 5 1 0 1 0 0
LON PAs Group 1 0 6 6 4 2 0 0
SEV PAs Group 2 0 7 11 3 8 0 0
ACHR Accounts & HR 0 8 11 5 6 0 0
IT IT 0 9 2 2 0 0 0
REC Records 0 10 10 0 10 0 0
It's just when I use 'EVY', 'Everyone' in the select that it returns the redundant row.
March 1, 2018 at 3:42 am
There is no group called Everyone.
If I strip out some of the query to just this, and add 1=2 in the where clause, it still returns a row:
select'EVY', 'Everyone', cast(0 as bit), 2,
count(a.EMP_ClockNumber) Total,
count(case when a.ACT_Status = 'In' then a.EMP_ClockNumber end),
count(case when a.ACT_Status = 'Out' then a.EMP_ClockNumber end),
count(case when a.ACT_Status = 'Break' then a.EMP_ClockNumber end),
count(case when a.ACT_Status = 'Absent' then a.EMP_ClockNumber end)
from[(local)\ISYS].Intelligent.dbo.Employees e left join
[(local)\ISYS].Intelligent.dbo.vwStaffDayActivity1 a on a.EMP_ClockNumber = e.EMP_ClockNumber
where1=2
(No column name) (No column name) (No column name) (No column name) Total (No column name) (No column name) (No column name) (No column name)
EVY Everyone 0 2 0 0 0 0 0
March 1, 2018 at 5:36 am
It's also the aggregate functions, well COUNT at least. If I strip those out it returns nothing if grouping is true, even with using literal values.
So it seems:
-literal values with aggregates causes a row to be returned, even with an impossible evaluator (1=2)
-column names with aggregates is ok
-literal values without aggregates is ok
And not just in this UNION case.
select'EVY', 'Everyone', count(1) Total
fromtbl_Users
where1=2
Results in:
(No column name) (No column name) Total
EVY Everyone 0
selectForename, count(1) Total
fromtbl_Users
where1=2
group byForename
Results in nothing.
Solution: Use HAVING instead:
select'EVY', 'Everyone', count(1) Total
fromtbl_Users
having 1=2
Results in nothing.
March 1, 2018 at 9:05 am
lanky_doodle - Thursday, March 1, 2018 1:35 AMsgmunson - Wednesday, February 28, 2018 2:37 PMI noticed that your query does not identify the data type of the @Grouping variable. You are testing it for equality with a character string, so if it's a bit data type, you aren't going to get the results you are expecting, as you can't test a bit for 'true' or for 'false', as SQL Server does not recognize those strings as boolean values. You have to actually test for either 1 or 0 for a bit data type, where 1 is generally true, and 0 is generally false. On the other hand, if it has a varchar(5) (or longer) data type, and you know that the web app supplies character strings that are either the word true or the word false, you would be okay, but if that app returns a boolean value, it's the problem.This is in a sproc, and this variable is a parameter.
ALTER procedure [dbo].[genSelPeopleGroups]
@user-id uniqueidentifier,
@Grouping bit
as
...
I have to say I never anticipated the following results:SELECT CAST('true' AS bit) AS TRUE, CAST('false' AS bit) AS FALSETRUE FALSE
1 0
However, those are explicit CAST operations, so I tried reproducing the @Grouping variable, declared as a bit = 1 and as a bit = 0, and both worked as you expected. Then I tried not setting a value for @Grouping, and the query then failed to return any rows at all. So therefore, I SIT corrected...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply