Results 1 to 20 of 20

Thread: [RESOLVED] UNION isn't returning what I thought it would (SQL Server)

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Resolved [RESOLVED] UNION isn't returning what I thought it would (SQL Server)

    I run a query and get back four rows. I run another query and I get back four rows. I want to get the eight rows at once, so I unioned them and I got...four rows. Does that make sense?

    I have health care providers in pending tables (tblPendingXXX) and health care providers in non-pending tables (tblXXX). The providers have a fax number which is one of the columns I am selecting. So if I run the query against the pending tables I get four rows; one of them has a unique fax number and three of them have the same fax number. I am also selecting a hard-coded "pending" as one of the columns.

    It just so happens (I am finding out if this is bad data) that the same provider also exists in the non-pending table. So when I select him there I get the same four rows but I am selecting a hard-coded "edit" as one of the columns. (edit and pending will be links on the grid when it is rendered on the web page).

    So far we have four pending rows and four edit rows.

    When I run these queries together with a UNION, I get four rows. Fax aaa and edit, fax bbb and edit, fax aaa and pending, fax bbb and pending. Which I don't quite understand. Why do they return 8 rows total separately, but 4 rows total when unioned?
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: UNION isn't returning what I thought it would (SQL Server)

    UNION is distinct by default

    UNION ALL should give you what you want.

    Actually it's been said that UNION ALL is faster in the fact that it doesn't have to consider a "distinct" pass on the result set.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #3

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: UNION isn't returning what I thought it would (SQL Server)

    So sorry - I've since discovered an important keyword - ALL!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  4. #4

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: UNION isn't returning what I thought it would (SQL Server)

    As Steve also pointed out, before my refresh...
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  5. #5

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: UNION isn't returning what I thought it would (SQL Server)

    Now I realize (now that the data have been explained to me) that I want to do the exact opposite of a UNION. I want to do an ANTI-UNION.

    The pending tables are a subset of the active tables. So every pending provider will also have an active record (or maybe "will" is too optimistic of me - there are three who don't). If he has a pending record it means he's pending. If he doesn't have a pending record it means he's active and editable.

    So I have to think about how this affects my query and whether I return pending/edit. If the search criterion is last name = Smith I have to return "edit" for all the active Smiths but "pending" for all the pending Smiths. I am not sure how to select from the active table only if they're not in the pending table. Or would I just select from the active table then see if they're in the pending table and update the status...
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  6. #6
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: UNION isn't returning what I thought it would (SQL Server)

    You can take the easy way out and put the UNION ALL into a temp table (or sub-query) and SELECT from that...

    Or use:

    Code:
    Select * From Active A
       Where A.Name='Smith'
    Union All
    Select * From Pending P
       Where P.Name='Smith'
          and Not Exists(Select * From Active A1 Where A1.CustId=P.CustId)
    Your WHERE clause can be made "smart" enough to know if a record should be pulled from Pending or not...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  7. #7

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: UNION isn't returning what I thought it would (SQL Server)

    What I decided to do (not that I disregarded your advice but I was 20% into this solution when you posted and I believe they are very similar anyway):

    1. I select into a temporary table all records from tblPendingX that match my criteria. These are action = "pending"
    2. I select all records from tblX that aren't in my temp table that match my criteria, label them action = "edit" and UNION ALL with my temp table.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  8. #8
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: UNION isn't returning what I thought it would (SQL Server)

    Multi-step operations are ok - just put a BEGIN TRAN / COMMIT at the top and bottom of the SPROC to make sure you stay atomic.

    Although I feel a SPROC is atomic in nature naturally - when I do a multi-step operation where concurrency can burn me I always put the BEGIN TRAN / COMMIT in.

    It is safer to do it in a single SELECT / UNION ALL with smart WHERE clause - as a single SELECT is as atomic as you can get.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  9. #9

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: UNION isn't returning what I thought it would (SQL Server)

    Okay, I understand what you are saying, I just thought I was saving time building the temp table rather than executing the same Select again.

    Also, the use of this query is by from the user interface when a user wants to call up records for further processing. So if it crashed midway, they'd be starting over anyway? Or there's a chance they wouldn't know of a crash and would be processing partial data?
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  10. #10
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: UNION isn't returning what I thought it would (SQL Server)

    Sorry - atomic was a misstatement...

    The begin tran/commit is to insure isolation (the third letter of ACID) - which means that no one is sneaking in and "making active" a pending row while you are in the midst of considering it's active/pending status.

    Not to protect against a crash - I would not imagine a sproc doing a simple couple of select's is going to crash.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  11. #11

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: UNION isn't returning what I thought it would (SQL Server)

    Not unless a squirrel is chewing through the power cord at the time, but even given the likelihood of that happening, you can be sure it would be during my stored procedure!

    Anyway, got your point. Thanks.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  12. #12

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: UNION isn't returning what I thought it would (SQL Server)

    Quote Originally Posted by szlamany View Post
    Multi-step operations are ok - just put a BEGIN TRAN / COMMIT at the top and bottom of the SPROC to make sure you stay atomic.
    Can we revisit this again? Can you explain exactly why you recommended it (realizing you had changed "atomic" to "isolation")? I had to take it out, per the advice of the senior s/w egr with whom I am working on this project.

    Often when I'd be closing SSMS at the end of the day, or even one of the windows where I'd been running my sprocs it would tell me I had uncommitted transactions. (I believe that was the verbage). So I just said commit or ignore or whatever the choices were. But then we needed maintenance done on the DB, and the DBA said my machine was still connected, even though everything was closed.

    So my boss emailed me: can you check your search query code for instances where it is locking the DB and not releasing?

    To which I replied: I am doing begin tran/ end tran statements around my selects. It might be because of that – I was getting messages saying the transaction hadn’t completed or something like that, every once in a while. I will remove them. They were put in for “isolation” – perhaps they are unnecessary, but if they are necessary we need to determine if they’re locking the db somehow.

    To which the Sr S/W Egr replied: We don’t need tran in your search queries. That will cause problems if we have them.

    Which has now made my code suspect and it has been targeted for a code review (which of course we should be doing anyway, but we don't, and even though they said we are going to, they will probably decide they don't have time. They also said we are going to do a code review of the consultant's MVC code, perhaps he is suspect simply because he's a consultant, but we are not reviewing the Sr S/W Egr's code, which is unfortunate, because although I'm sure it is 100% perfect and above criticism, it'd be nice for us mistake-makers to learn from it (yes, you do detect bitterness in that statement)).

    So I'd just like to know why you think they are necessary and if you have any comments to make on the scenarios we experienced, and if perhaps I implemented it wrong. Here is the basic skeleton of my code:
    Code:
    ALTER PROCEDURE usp_Search
    	@parameters...
    		
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    BEGIN TRAN was here
    
    SELECT 
    	columns
    	
    	INTO #tblTempPendingRecords
    	
    	FROM pending-tables
    	
    	WHERE criteria
    	
    SELECT 
    	columns
    	
    	INTO #tblTempRecords
    	
    	FROM regular-tables
    	
    	WHERE criteria 
    
    IF @SearchType = 'ALL'
    BEGIN
    	SELECT * FROM #tblTempPendingRecords
    
    	UNION ALL
    	
    	SELECT * FROM #tblTempRecords
    END
    ELSE	-- @SearchType = 'EXACT'
    BEGIN
    	DECLARE @CountOfRecords INT;
    	
    	-- Want to ensure there's only one record in both tables
    	SELECT * INTO #tblAllRecords FROM #tblTempPendingRecords
    	INSERT INTO #tblAllRecords SELECT * FROM #tblTempRecords 
    	SELECT @CountOfRecords = COUNT(ID) FROM #tblAllRecords
    	-- If there is only one record then it's verified and return the ID and the Name
    	IF @CountOfRecords = 1
    		SELECT ID, Name FROM #tblAllRecords
    END
    
    COMMIT TRAN was here
    
    END
    GO
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  13. #13
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: UNION isn't returning what I thought it would (SQL Server)

    Quote Originally Posted by MMock View Post
    What I decided to do (not that I disregarded your advice but I was 20% into this solution when you posted and I believe they are very similar anyway):

    1. I select into a temporary table all records from tblPendingX that match my criteria. These are action = "pending"
    2. I select all records from tblX that aren't in my temp table that match my criteria, label them action = "edit" and UNION ALL with my temp table.
    This type of activity - in my mind - needs to be protected by being in a transaction.

    See this MSDN link

    http://msdn.microsoft.com/en-us/library/ms187926.aspx

    It's a lengthy read - but you will see examples of BEGIN TRAN in places where multi-step "situations" are happening where an "ALL OR NOTHING" requirement is in place. That is ISOLATION - right?

    Regular SELECT statements that are returning row-set data - do NOT need to be in a transaction.

    When you close windows and it tells you that you had "open transactions" - well that means you ignored an ERROR of some sort in an earlier test of the query - and it never reached the COMMIT.

    That is really really bad - for two reasons.

    One - you are locking other people out of whatever resources your transaction is holding.

    Two - you are now getting "bogus" results in your testing - because you have a partially "processed" transaction that you are "working" new queries against.

    That second point - that's the really evil one.

    Whenever I use a BEGIN TRAN/COMMIT or ROLLBACK in a query I get used to clicking the ROLLBACK word (selecting it in the SSMS query window) and EXECUTING it by itself so I can see it say

    "...no corresponding BEGIN TRANSACTION"
    ...
    Attached Images Attached Images  
    Last edited by szlamany; Mar 1st, 2012 at 08:47 AM.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  14. #14
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: [RESOLVED] UNION isn't returning what I thought it would (SQL Server)

    I know I'm coming to this a bit late but I'd have achieved the original goal with a left join and case:-
    Select Active.ID,
    Active.Name,
    Case When Pending.ID is null then 'Active' Else 'Pending' END
    From Active
    Left Join Pending
    on Active.ID = Pending.ID
    Not sure it's any better or worse than the other suggestions but it is an alternative.

    As for the transaction discussion, if your SSMS is holding transactions open at the end of the day it's probably not to do with the transactions you're putting in your sprocs but more likely to be from ad-hoc queries you've run during the day where you've opened a transaction and then forgotton to close it. Or as Sz put it:-
    When you close windows and it tells you that you had "open transactions" - well that means you ignored an ERROR of some sort in an earlier test of the query - and it never reached the COMMIT.
    Whether you need a transaction in the scanario you describe really depends on the business. If for example, a provider become active while your query is running and you miss-report them as pending, does it matter? If it does then you need a transaction.


    edit> And you can never trust squirrels. Although in your example it would have bought the whole server down so your open transactions are unlikely to be the biggest problem you'll have to deal with.

    We used to have a rule that you were allowed to use transactions on the server (as long as they were properly managed) but never to open one from the client. The reasoning was that a server crash means you don't care about a left ofer transaction but a client crash could leave that resource open on the server. I'm not sure that's true but it was an easy enough rule to follow so I never felt the need to find out.
    Last edited by FunkyDexter; Mar 1st, 2012 at 09:07 AM.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  15. #15

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: [RESOLVED] UNION isn't returning what I thought it would (SQL Server)

    Ok - thanks for the explanation. If/when we have the w/t, I will point this out. If we never have the walkthrough, I will informally explain why I thought it was necessary.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  16. #16
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: [RESOLVED] UNION isn't returning what I thought it would (SQL Server)

    I'm right up the street - I'll stop in and set your sr s/w eng straight

    Bottomline...

    There is absolutely no "damage" caused by having a BEGIN TRAN / COMMIT in a SPROC.

    If the queries can fail in production you need to handle TRY CATCH and ROLLBACK's - but that's required and obvious in any programming model.

    Putting them in all SPROCS creates a testing and debugging nightmare (as you have personally experienced with holding transactions open).

    But as FunkyDexter said:

    If for example, a provider become active while your query is running and you miss-report them as pending, does it matter? If it does then you need a transaction.
    If this is a business requirement for you to handle - then you need the BEGIN TRAN / COMMIT

    btw - I did further research and did verify that a SPROC is not an implied transaction - you do not get an ALL-OR-NOTHING experience just because you have a dozen queries in that SPROC container.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  17. #17

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: [RESOLVED] UNION isn't returning what I thought it would (SQL Server)

    Quote Originally Posted by FunkyDexter View Post
    I know I'm coming to this a bit late but I'd have achieved the original goal with a left join and case:-

    Code:
    Select Active.ID, 
    Active.Name,
    Case When Pending.ID is null then 'Active' Else 'Pending' END
    From Active
    Left Join Pending
    on Active.ID = Pending.ID
    Not sure it's any better or worse than the other suggestions but it is an alternative.
    So you are suggesting I select all my columns from the active table, but set the action (Active or Pending) based on if there is a correspoinding row in the pending table.

    I think I might give that a try. I am just wondering if it's possible to have different data in the pending table and the active table, and if it matters. For example, the first one I happened to pick to test with that has a row in both has a phone and fax in the actve table but those columns are null in the pending table. So I'd be displaying the record as "pending" but with data from the active table. Ideally, you'd think they'd be the same, but that is the problem with replicating data and why we don't do it, right?
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  18. #18
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: [RESOLVED] UNION isn't returning what I thought it would (SQL Server)

    So I'd be displaying the record as "pending" but with data from the active table. Ideally, you'd think they'd be the same, but that is the problem with replicating data and why we don't do it, right?
    Right! (although replicated is probably the wrong word if we're being pedantic). There are damn good reasons why databases are normalised and this is one of them. Really pending vs activeshould be handled as a flag on the provider table but I imagine that's outside of your control to change.

    Actually, this is one of the places where a join aproach has an edge over the union because you use an isunll to pick up whatever info is available. e.g.
    select isnull(Active.PhoneNumber, Pending.PhoneNumber) from blah blah blah Of course, you'll jave to decide whether you want to trust the active or the pending info more and pick that one up first.


    edit>
    I'm right up the street - I'll stop in and set your sr s/w eng straight
    I'd have to travel half way round the world but I'm still willing to turn up if I get to watch Steve explain transactions to a "senior" engineer using words of one syllable and, optionally, a bat.
    Last edited by FunkyDexter; Mar 1st, 2012 at 10:29 AM.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  19. #19

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: [RESOLVED] UNION isn't returning what I thought it would (SQL Server)

    If you knew this guy, any travel would be worth it to put him or see him put in his place. He's my boss's golden boy, and I'm not sure I get why.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  20. #20

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: [RESOLVED] UNION isn't returning what I thought it would (SQL Server)

    Okay, I've rewritten some stuff and I'm really happy with it (and believe me, it takes a lot to make me happy as far as my code is concerned). Thanks to you both, I am going to shine in our walkthrough! I've really learned a lot from the threads I've posted in this forum ove the past couple of weeks. Thanks again and if I haven't rated you too recently in the past, I will do so now.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width