SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


union with constant values


union with constant values

Author
Message
lanky_doodle
lanky_doodle
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1735 Visits: 169
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
ZZartin
ZZartin
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26112 Visits: 17450
lanky_doodle - Wednesday, February 28, 2018 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

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'

drew.allen
drew.allen
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65685 Visits: 17414
lanky_doodle - Wednesday, February 28, 2018 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

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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
lanky_doodle
lanky_doodle
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1735 Visits: 169
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]

from tbl_Users u join

tbl_User_Favourites uf on uf.Person_ID = u.[User_ID] and uf.[User_ID] = @UserID 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

select coalesce(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 by coalesce(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.

drew.allen
drew.allen
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65685 Visits: 17414
lanky_doodle - Wednesday, February 28, 2018 10:13 AM
But 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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)

Group: General Forum Members
Points: 164043 Visits: 22803
Is it possible that you have a group that is called everyone? The numbers are different on your results.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)

Group: General Forum Members
Points: 164043 Visits: 22803
You should change your UNION to UNION ALL


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
sgmunson
sgmunson
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96071 Visits: 7204
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)
Smile Smile Smile
Health & Nutrition
Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)SSC Guru (164K reputation)

Group: General Forum Members
Points: 164043 Visits: 22803
sgmunson - Wednesday, February 28, 2018 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.

Actually, a string with the value 'false' will be converted to 0 when casted to bit.



Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
lanky_doodle
lanky_doodle
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1735 Visits: 169
sgmunson - Wednesday, February 28, 2018 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.

This is in a sproc, and this variable is a parameter.

ALTER procedure [dbo].[genSelPeopleGroups]

@UserID uniqueidentifier,

@Grouping bit

as

...

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search