Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2012
    Posts
    1

    Unanswered: Union returns duplicates

    This SQL is meant to show the changes that will be made, when removing a selected user's email address from a batch.
    However, when executed, each row is duplicated, and in the duplication, the semi-colon or comma isn't removed.
    For example, if I wanted to remove user "sam@mail.com"

    The table results displayed would be:

    Row 1:
    BatchID: 50
    ParamName:EmailTo
    ParamValue: jack@mail.com;sam@mail.com;frank@mail.com
    NewParamValue: jack@mail.com;frank@mail.com

    Row 2:
    BatchID: 50
    ParamName:EmailTo
    ParamValue: jack@mail.com;sam@mail.com;john@mail.com
    NewParamValue: jack@mail.com;;frank@mail.com

    Ideally, it should only display each row once, and not have the semicolon error.
    It seems to be a union error, because when I comment out the First and second union statements, it runs fine.



    -- Delete email address from a.Batch

    IF(@EmailAddress IS NOT NULL)
    BEGIN
    IF(LEN(@EmailAddress) > 0)
    BEGIN
    IF(@ShowOnly = 1)
    BEGIN
    SELECT DISTINCT BatchID, Name ParamName, Value ParamValue, NewParamValue
    FROM (
    SELECT *, REPLACE(VALUE, @EmailAddress + ',', '') NewParamValue
    FROM a.Batch
    WHERE VALUE LIKE '%' + @EmailAddress + ',' + '%'
    UNION
    SELECT *, REPLACE(VALUE, @EmailAddress + ';', '') NewParamValue
    FROM a.Batch
    WHERE VALUE LIKE '%' + @EmailAddress + ';' + '%'
    UNION
    SELECT *, REPLACE(VALUE, @EmailAddress, '') NewParamValue
    FROM a.Batch
    WHERE VALUE LIKE '%' + @EmailAddress + '%'
    ) emails
    END


    Any help is much appreciated

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Duplicates are being returned because the rows are not really duplicates. There is some difference between them.
    The culprit is likely the "SELECT *", which is not good practice. Enumerate only the columns you need.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    VALUE?

    This is a forum for SQL Server, not DB2
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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