Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2003
    Posts
    6

    Unanswered: Strange Query Behaviour in MSDE

    Hi,

    I am running into a problem with a query that's driving me crazy so I would appreciate any information. I am using MSDE 2000 from an ASP.NET application to read the DB.

    Basically I have two tables of transaction info (one for successful transactions, one for failed ones) and what I want is to get the 10 newest overall transactions for a customer, regardless of which table it's from. Here is the query I am trying to use:

    SELECT DISTINCT TOP 10 CustomerName, CreditCardNum, Amount, InvoiceNum, TransEnterTime, 'Success' AS Status, NULL AS OrigTransTime FROM SuccessTransactionTbl WHERE MerchantNum='111111222222'
    UNION SELECT DISTINCT TOP 10 CustomerName, CreditCardNum, Amount, InvoiceNum, TransEnterTime, 'FAILED' AS Status, NULL AS OrigTransTime FROM FailedTransactionTbl WHERE MerchantNum='111111222222'
    ORDER BY TransEnterTime

    Of course this can return up to 20 transactions, but that's not the problem.

    Also, the MerchantNum + TransEnterTime combination is a unique key on both tables so there should be no duplicates.

    What I am seeing is that for a certain customer (specified by MerchantNum), who has no successful transactions but has a bunch of failed ones, the query returns 10 failed transactions, but they are not the 10 newest ones. It's as if it is taking 10 failed transactions by some other criteria and then sorting them by time. I thought the "ORDER BY" clause used with TOP was supposed to get the top X after the sort.

    Even more bizarre is that if I remove the "DISTINCT" keyword from the query, the correct records are returned. Like I said the TransEnterTime is unique per customer so there really should be no duplicates (and there aren't any in the data) so distinct is not needed (I have it in because I intend to extend the query to do a join, but even this stripped down one fails).

    I also get the correct records for this customer if I remove the whole "UNION" part with the success table (but this of course is not acceptable for other customers)

    Lastly I get the correct records if I remove the "TOP 10" from the Failed query and then programatically just look at the first 10 records in the result, but I'd rather not do this for performance reasons.

    My questions are:
    1) Why am I not getting the 10 newest failed transactions? Is there some precedence problem here with UNION, and/or ORDER BY that is screwing things up?
    2) Why the heck should DISTINCT affect the results if there are no duplicates?
    3) Is there some better query I can use to get the transactions I want?

    Thanks for any assistance!

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Re: Strange Query Behaviour in MSDE

    RE:

    SELECT DISTINCT TOP 10 CustomerName, CreditCardNum, ...

    ... ORDER BY TransEnterTime


    the query returns 10 failed transactions, but they are not the 10 newest ones. It's as if it is taking 10 failed transactions by some other criteria and then sorting them by time.
    Question I

    ORDER BY TransEnterTime = ORDER BY TransEnterTime Asc, Are you expecting ORDER BY TransEnterTime Desc ?
    Last edited by DBA; 01-23-03 at 05:23.

  3. #3
    Join Date
    Jan 2003
    Posts
    6
    Oops, sorry I am actually trying to get the 10 OLDEST ones first. The problem is I am not getting all 10 oldest ones, I get 10 transactions but they are not the correct ones.

    Basically I want to page through the transactions which is why I want to get 10 of them at a time. The query I posted would be for the first page, later I would give another constraint to get the next 10, etc. But this first query isn't giving me the right transactions which is really confusing me!

    Sorry about that...

  4. #4
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Send create table for SuccessTransactionTbl

  5. #5
    Join Date
    Jan 2003
    Posts
    6
    Sure:

    CREATE TABLE SuccessTransactionTbl(MerchantNum char(12) NOT NULL,TransEnterTime datetime NOT NULL,TransSeqNum char(4),TransCode char(2),CustomerName varchar(30) NOT NULL,CreditCardNum varchar(16) NOT NULL,CreditCardExpiry char(4) NOT NULL,CustomerAddress varchar(30) NOT NULL,CustomerZip varchar(9) NOT NULL,Amount varchar(12) NOT NULL,ReceivedACI char(1) NOT NULL,AuthSourceCode char(1) NOT NULL,AuthRespCode char(2) NOT NULL,ApprovalCode char(6) NOT NULL,TransDate char(6) NOT NULL,TransTime char(6) NOT NULL,AuthRespText char(16) NOT NULL,AVSResultCode char(1) NOT NULL,RetrievalRefNum char(12) NOT NULL,TransID char(15),ValidationCode char(4),CVV2 varchar(4),CVV2Response char(1),InvoiceNum varchar(25),SettFailureCount smallint NOT NULL)
    CREATE UNIQUE INDEX idxOne ON SuccessTransactionTbl (MerchantNum, TransEnterTime)

    Here is failed:

    CREATE TABLE FailedTransactionTbl(MerchantNum char(12) NOT NULL,TransEnterTime datetime NOT NULL,TransSeqNum char(4),TransCode char(2),CustomerName varchar(30),CreditCardNum varchar(16),CreditCardExpiry char(4),CustomerAddress varchar(30),CustomerZip varchar(9),Amount varchar(12),ReceivedACI char(1),AuthSourceCode char(1),AuthRespCode char(2),ApprovalCode char(6),TransDate char(6),TransTime char(6),AuthRespText char(16),AVSResultCode char(1),RetrievalRefNum char(12),TransID char(15),ValidationCode char(4),CVV2 varchar(4),CVV2Response char(1),InvoiceNum varchar(25),FailureType varchar(10),FailureMessage varchar(100))
    CREATE UNIQUE INDEX idxOne ON FailedTransactionTbl (MerchantNum, TransEnterTime)

    For this particular customer, I have no data in the success table, and entries with the following "TransEnterTime" in the FailedTbl:

    TransEnterTime
    1/17/2003 10:28:08 PM
    1/17/2003 10:36:28 PM
    1/18/2003 4:02:19 AM
    1/18/2003 4:10:38 AM
    1/18/2003 4:15:37 AM
    1/18/2003 4:16:16 AM
    1/18/2003 4:20:25 AM
    1/18/2003 4:31:04 AM
    1/18/2003 4:39:23 AM
    1/18/2003 4:39:49 AM
    1/18/2003 4:42:26 AM
    1/18/2003 4:43:05 AM
    1/21/2003 3:23:10 AM

    So I would expect the first 10 of these to be returned by my query, instead I get these:

    1/17/2003 10:28:08 PM
    1/17/2003 10:36:28 PM
    1/18/2003 4:02:19 AM
    1/18/2003 4:10:38 AM
    1/18/2003 4:20:25 AM
    1/18/2003 4:31:04 AM
    1/18/2003 4:39:23 AM
    1/18/2003 4:42:26 AM
    1/18/2003 4:43:05 AM
    1/21/2003 3:23:10 AM

    So these three that I would expect are missing:
    1/18/2003 4:15:37 AM
    1/18/2003 4:16:16 AM
    1/18/2003 4:39:49 AM

    I don't understand why it isn't working? It really seems to be behaving strangely for this and I can't figure out what I'm missing.

  6. #6
    Join Date
    Jan 2003
    Posts
    6
    Does anyone have any insight on why this query isn't behaving as expected? Am I at least correct in thinking that the query as posted SHOULD return the records that I expect (ie the 10 oldest transactions), or is there something wrong with it?

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Have you tried something like this:

    Select top 10 (fields)
    from (select fields
    from success
    union
    select fields
    from failed) a
    order by date


    This should isolate the union away from the order by clause. Hope this helps.
    ~Matt

  8. #8
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    select top 10 * from tbl
    union
    select top 10 * from tbl2
    order by x

    will get the 20 recs then order them - so you can get any recs from the 2 tables in the result.

    try this
    create table #a (i int)
    insert #a select 2
    insert #a select 1
    select top 1 i from #a
    union
    select top 1 i from #a
    order by i

    if you want the top 10 to work on each table before the union you have to specify it

    select * from
    (select top 10 * from tbl order by x) as a
    union
    select * from
    (select top 10 * from tbl2 order by x) as a
    order by x

  9. #9
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    UNION is slower than simple UNION ALL and concatenate only new rows (no problem in this case).

    If you want 10+10, use nigelrivett's query.

    If only 10 then

    select top 10 *
    from
    (
    select * from tbl
    union all
    select * from tbl2
    ) a
    order by x

    or simply

    set rowcount 10
    select * from tbl
    union all
    select * from tbl2
    order by x
    set rowcount 0


    I am no sure how distinct top/order works directly in union. Execution plan differs if there is index.

  10. #10
    Join Date
    Jan 2003
    Posts
    6
    Thanks guys, the suggestions work fine. I guess the key is to nest the select with unions to separate it from the "order by" clause which makes sense. This has been very helpful!

Posting Permissions

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