Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2001
    Posts
    175

    Unanswered: criteria for UNION query

    I have a UNION query (SELECT * FROM uniquepos UNION select * FROM uniqueneg which joins the queries below.

    I would like to put in a criteria in the UNION query that would remove records where the TransAmt+TaxAmt=ReceiptAmt OR can the criteria be put in the queries below

    UNIQUEPOS
    SELECT Pos.osamt, Pos.ClientNo, Pos.FirstName, Pos.LastName, Pos.Address1, Pos.Address2, Pos.City, Pos.Country, Pos.TransDate, Pos.InvoiceNo, Pos.PolicyNo, Pos.TransAmt, Pos.TaxAmt, Pos.ReceiptAmt, Pos.Currency
    FROM Pos LEFT JOIN Neg ON (Pos.osamt = abs(Neg.osamt)) AND (Pos.ClientNo = Neg.ClientNo)
    WHERE (((Neg.osamt) Is Null));

    UNIQUENEG
    SELECT Neg.osamt, Neg.ClientNo, Neg.FirstName, Neg.LastName, Neg.Address1, Neg.Address2, Neg.City, Neg.Country, Neg.TransDate, Neg.InvoiceNo, Neg.PolicyNo, Neg.TransAmt, Neg.TaxAmt, Neg.ReceiptAmt, Neg.Currency
    FROM Neg LEFT JOIN Pos ON Neg.osamt = Pos.osamt * -1
    WHERE (((Pos.osamt) Is Null));

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you should probably put that condition into both WHERE clauses

    i'm a bit concerned about your second query -- it seems to be matching rows based on negative and positive amounts with total disregard for whichever clients' records are being matched
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2001
    Posts
    175
    Is this the correct syntax:

    WHERE (((Neg.osamt) Is Null)) and TransAmt+TaxAmt=ReceiptAmt;

    I tried that was not getting the results I wanted. No rows were being returned

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you wanted to remove rows where TransAmt+TaxAmt=ReceiptAmt then you would code the WHERE condition as

    ... and TransAmt+TaxAmt <> ReceiptAmt

    or

    ... and not (TransAmt+TaxAmt=ReceiptAmt)

    what about my other point?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2001
    Posts
    175
    Ok - that worked and I also needed to use the function 'abs'. Everything is fine now.

    Sorry about not responding to the client issue but I purposely omitted it from the post but I did have it in. See Below.

    UNIQUENEG
    SELECT Neg.osamt, Neg.ClientNo, Neg.FirstName, Neg.LastName, Neg.Address1, Neg.Address2, Neg.City, Neg.Country, Neg.TransDate, Neg.InvoiceNo, Neg.PolicyNo, Neg.TransAmt, Neg.TaxAmt, Neg.ReceiptAmt, Neg.Currency
    FROM Neg LEFT JOIN Pos ON (Pos.ClientNo = Neg.ClientNo) AND (Neg.osamt = Pos.osamt * -1)
    WHERE (((Pos.osamt) Is Null)) and Neg.TransAmt+Neg.TaxAmt <> Neg.ReceiptAmt;

    UNIQUEPOS
    SELECT Pos.osamt, Pos.ClientNo, Pos.FirstName, Pos.LastName, Pos.Address1, Pos.Address2, Pos.City, Pos.Country, Pos.TransDate, Pos.InvoiceNo, Pos.PolicyNo, Pos.TransAmt, Pos.TaxAmt, Pos.ReceiptAmt, Pos.Currency
    FROM Pos LEFT JOIN Neg ON (Pos.ClientNo = Neg.ClientNo) AND (Pos.osamt = abs(Neg.osamt))
    WHERE (((Neg.osamt) Is Null)) and Pos.TransAmt+Pos.TaxAmt <>Pos.ReceiptAmt;

    Thank you very much for your assistance.

Posting Permissions

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