locked
SQL Server 2008 -- Custom Paging BUG -- Problem and Solution with Example.... RRS feed

  • Question

  •  

     I found what I feel is a bug with SQL Server 2008 Custom Paging.

     

    Here is the details... 

    ---- We have a transaction detail table that has several records that tie back to a single transaction main table.

    ---- When we use the SQL Server Custom Paging we see duplicates listed for EACH record in the detail talbe.  However, when you run the SQL Statement, exclucing the "BETWEEN ROWNUMBER # and #" no duplicates are displayed and it works as expected.  However, as soon as you add in the BETWEEN statement for the ROWNUMBER it displays all duplicates.

    ---- Also if you replace the BETWEEN with "=" or "<" or " <= and >=" it also displays duplicats.  By using a single ">" it works, but does not allow you the ability to set the upper and lower limits...

     

    The fix we found is as follows.... 

    ---- If we reference a field OTHER THAN THE ROWNUMBER in an "OR" condition it works just fine along with the BETWEEN ROWNUMBER statement.  (We found that an "AND" condition does not work either.)

     

    I know this may sound confusing, so I have copied / pasted the SQL that does not work first, followed by the SQL that does work.  Notice the highlighted statements in each to simplify things....

     

    Thanks,

    Bruce Pullum

     

    (Debbie Jones also helped me work through this and find out what was happening.  Form there we tried many different things until we found one that worked..)

     

    I am hoping that someone from Microsoft will find this and fix the problem.  Also, if Microsoft would like to give Debbie and I a million $$$$  that would be ok as well....   Smile

     

    THIS DOES NOT WORK ---  This is due to the “WHERE RowNumber between 1 and 8

    WITH CustomPaging AS 

    (

          SELECT DISTINCT 'S' AS LnkBtnSelect, acctTransactionMain_T.TMAINiID, acctTransactionMain_T.TMAINlTransactionNumber, CONVERT(varchar(10), acctTransactionMain_T.TMAINdtTransactionDate, 101) AS TransactionDate, CONVERT(varchar(10), acctTransactionMain_T.TMAINdtReferenceDate, 101) AS ReferenceDate, tblLookupCodeTable_T_1.LKUPsCodeText AS InvoiceType, acctTransactionAddress_T.TADDRsSortName, (SELECT SUM(acctTransactionMulti_T.TMULTIdblProjectedAmount) + SUM(acctTransactionMulti_T.TMULTIdblClearingAmount) AS OSAmount FROM acctTransactionDetail_T INNER JOIN acctTransactionMulti_T ON acctTransactionMulti_T.TMULTI_TDETAILiID = acctTransactionDetail_T.TDETAILiID WHERE (acctTransactionDetail_T.TDETAIL_TMAINiID = acctTransactionMain_T.TMAINiID)) AS OSAmount, tblLookupCodeTable_T.LKUPsCodeText AS TransactionStatus, acctTransactionMain_T.TMAINlTransactionNumber AS SortNumber, acctTransactionMain_T.TMAINdtTransactionDate AS SortDate, acctTransactionMain_T.TMAINiID AS SortID, 'C' AS ImageButtonCopyFrom, TMAINiTransactionStatus as SORT_Status  , ROW_NUMBER() over(ORDER BY  TMAINlTransactionNumber, TMAINiTransactionStatus asc, TMAINlTransactionNumber DESC,  acctTransactionMain_T.TMAINdtTransactionDate DESC, TMAINiID DESC) AS RowNumber  FROM acctExpRevAccounts_T RIGHT OUTER JOIN acctTransactionMulti_T AS acctTransactionMulti_T_2 ON acctExpRevAccounts_T.ERACCTiID = acctTransactionMulti_T_2.TMULTI_ERACCTiID  RIGHT OUTER JOIN acctTransactionMain_T LEFT OUTER JOIN acctTransactionDetail_T AS acctTransactionDetail_T_2 ON acctTransactionMain_T.TMAINiID = acctTransactionDetail_T_2.TDETAIL_TMAINiID  LEFT OUTER JOIN acctTransactionAddress_T ON acctTransactionMain_T.TMAIN_TADDRiID = acctTransactionAddress_T.TADDRiID  LEFT OUTER JOIN acctTransactionInvoiceBillCode_T ON acctTransactionDetail_T_2.TDETAIL_TBILLiID = acctTransactionInvoiceBillCode_T.TBILLiID  ON acctTransactionMulti_T_2.TMULTI_TDETAILiID = acctTransactionDetail_T_2.TDETAILiID LEFT OUTER JOIN tblLookupCodeTable_T ON acctTransactionMain_T.TMAINiTransactionStatus = tblLookupCodeTable_T.LKUPiID  LEFT OUTER JOIN tblLookupCodeTable_T AS tblLookupCodeTable_T_1 ON acctTransactionMain_T.TMAINiLookupCodeTable1 = tblLookupCodeTable_T_1.LKUPiID  WHERE (acctTransactionMain_T.TMAINsType = 'INV')  AND (((SELECT SUM(acctTransactionMulti_T.TMULTIdblProjectedAmount) + SUM(acctTransactionMulti_T.TMULTIdblClearingAmount) AS OSAmount FROM acctTransactionDetail_T INNER JOIN acctTransactionMulti_T ON acctTransactionMulti_T.TMULTI_TDETAILiID = acctTransactionDetail_T.TDETAILiID WHERE (acctTransactionDetail_T.TDETAIL_TMAINiID = acctTransactionMain_T.TMAINiID)) <> 0) OR TMAINiTransactionStatus = 300) AND (TADDRsSortName Like '%')

    ) select *  from CustomPaging

    WHERE RowNumber between 1 and 8  

    ORDER BY  TMAINlTransactionNumber

     

    THIS DOES WORK ---  This is due to me adding the “or  TMAINlTransactionNumber = -1

    WITH CustomPaging AS 

    (

          SELECT DISTINCT 'S' AS LnkBtnSelect, acctTransactionMain_T.TMAINiID, acctTransactionMain_T.TMAINlTransactionNumber, CONVERT(varchar(10), acctTransactionMain_T.TMAINdtTransactionDate, 101) AS TransactionDate, CONVERT(varchar(10), acctTransactionMain_T.TMAINdtReferenceDate, 101) AS ReferenceDate, tblLookupCodeTable_T_1.LKUPsCodeText AS InvoiceType, acctTransactionAddress_T.TADDRsSortName, (SELECT SUM(acctTransactionMulti_T.TMULTIdblProjectedAmount) + SUM(acctTransactionMulti_T.TMULTIdblClearingAmount) AS OSAmount FROM acctTransactionDetail_T INNER JOIN acctTransactionMulti_T ON acctTransactionMulti_T.TMULTI_TDETAILiID = acctTransactionDetail_T.TDETAILiID WHERE (acctTransactionDetail_T.TDETAIL_TMAINiID = acctTransactionMain_T.TMAINiID)) AS OSAmount, tblLookupCodeTable_T.LKUPsCodeText AS TransactionStatus, acctTransactionMain_T.TMAINlTransactionNumber AS SortNumber, acctTransactionMain_T.TMAINdtTransactionDate AS SortDate, acctTransactionMain_T.TMAINiID AS SortID, 'C' AS ImageButtonCopyFrom, TMAINiTransactionStatus as SORT_Status  , ROW_NUMBER() over(ORDER BY  TMAINlTransactionNumber, TMAINiTransactionStatus asc, TMAINlTransactionNumber DESC,  acctTransactionMain_T.TMAINdtTransactionDate DESC, TMAINiID DESC) AS RowNumber  FROM acctExpRevAccounts_T RIGHT OUTER JOIN acctTransactionMulti_T AS acctTransactionMulti_T_2 ON acctExpRevAccounts_T.ERACCTiID = acctTransactionMulti_T_2.TMULTI_ERACCTiID  RIGHT OUTER JOIN acctTransactionMain_T LEFT OUTER JOIN acctTransactionDetail_T AS acctTransactionDetail_T_2 ON acctTransactionMain_T.TMAINiID = acctTransactionDetail_T_2.TDETAIL_TMAINiID  LEFT OUTER JOIN acctTransactionAddress_T ON acctTransactionMain_T.TMAIN_TADDRiID = acctTransactionAddress_T.TADDRiID  LEFT OUTER JOIN acctTransactionInvoiceBillCode_T ON acctTransactionDetail_T_2.TDETAIL_TBILLiID = acctTransactionInvoiceBillCode_T.TBILLiID  ON acctTransactionMulti_T_2.TMULTI_TDETAILiID = acctTransactionDetail_T_2.TDETAILiID LEFT OUTER JOIN tblLookupCodeTable_T ON acctTransactionMain_T.TMAINiTransactionStatus = tblLookupCodeTable_T.LKUPiID  LEFT OUTER JOIN tblLookupCodeTable_T AS tblLookupCodeTable_T_1 ON acctTransactionMain_T.TMAINiLookupCodeTable1 = tblLookupCodeTable_T_1.LKUPiID  WHERE (acctTransactionMain_T.TMAINsType = 'INV')  AND (((SELECT SUM(acctTransactionMulti_T.TMULTIdblProjectedAmount) + SUM(acctTransactionMulti_T.TMULTIdblClearingAmount) AS OSAmount FROM acctTransactionDetail_T INNER JOIN acctTransactionMulti_T ON acctTransactionMulti_T.TMULTI_TDETAILiID = acctTransactionDetail_T.TDETAILiID WHERE (acctTransactionDetail_T.TDETAIL_TMAINiID = acctTransactionMain_T.TMAINiID)) <> 0) OR TMAINiTransactionStatus = 300) AND (TADDRsSortName Like '%')

    ) select *  from CustomPaging

    WHERE RowNumber between 1 and 8  

    or  TMAINlTransactionNumber = -1

    ORDER BY  TMAINlTransactionNumber

     

     

    Thursday, September 18, 2008 8:43 PM

Answers

  • Bruce

     

    It would be better to post this message one of the SQL Server forums for a more focused audience.

     

    These forums are focused on supporting Vista.

     

    Go to the following page and scroll down to the SQL Server forums section.

    TechNet Forums:
    http://forums.microsoft.com/TechNet/default.aspx?SiteID=17

     

    Thanks.

     


    If this post helps to resolve your issue, click the Mark as Answer or Helpful button at the top of this message.
    By marking a post as Answered, or Helpful you help others find the answer faster.

    Ronnie Vernon
    Microsoft MVP
    Windows Desktop Experience
    Thursday, September 18, 2008 11:42 PM
    Moderator