union with constant values

  • 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

  • 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'

  • 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

  • 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.

  • 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

  • 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
  • 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
  • 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)

  • 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
  • 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]

    @user-id uniqueidentifier,

    @Grouping bit

    as

    ...

  • 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.

  • 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

  • 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.

  • lanky_doodle - Thursday, March 1, 2018 1:35 AM

    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]

    @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 FALSE
    TRUE    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