Getting gaps between views from an SQL Table in C# application. Something related to UniqueKeyValue-CodePudding

This is the code I'm executing for generating the View.

' 

namespace xyz
{
    //ToDo: Merge AccountsReceivableUnpostedTransaction, AccountsPayableUnpostedTransaction, RatesUnpostedTransaction & UtilityAccountUnpostedTransaction into 1
    //Find the unique properties attributes and set those.
    [DBViewDefinition(nameof(RatesUnpostedTransaction), SchemaDefinitions.Rates, @"
SELECT ft.ReportingAccountID  UniqueKeyValue,
    ft.ReportingAccountID AccountID,
   ft.ID FinancialTransactionID,
   ft.ReportingAmount InclusiveAmount,
   ft.StatusID StatusID, ROW_NUMBER() Over(Partition BY ft.ID order by ft.ID) as RowNumber
FROM GL.FinancialTransaction AS ft
JOIN [WF].EntityStatusValue AS esv ON esv.ID = ft.StatusID
WHERE ft.ReportingAccountID IS NOT NULL
    AND ft.PostedDate IS NULL
    AND esv.RecordState <> :Closed:
    

UNION ALL

SELECT ft.ID UniqueKeyValue,
    RRP.AccountID,
    FT.ID FinancialTransactionID,
    RRP.Amount InclusiveAmount,
    FT.StatusID,ROW_NUMBER() Over(Partition BY ft.ID order by ft.ID) as RowNumber
FROM RR.RevenueReceipt RR
JOIN GL.FinancialTransaction FT ON FT.ID = RR.ID
JOIN RR.RevenueReceiptPrepayment RRP ON RRP.RevenueReceiptID = RR.ID
JOIN WF.EntityStatusValue AS esv ON esv.ID = FT.StatusID
WHERE ft.PostedDate IS NULL
    AND esv.RecordState <> :Closed:", MigrateOrder = 10, UseRowNumberForUniqueKeyValue = false)]
    [SqlTextReplace("Closed", EntityStatusValue.RecordStateEnum.Closed)] '

So, I'm trying to remove duplicate rows which are generated. I believe its something related to unique key value. Kindly refer to the image for the output and the blue area is duplicate rows which needs to be removed.My error

Database is connected and tables are having duplicate unique keys. The Sql is as shown below in imageSQL QUERY. There isn't any error message but the additional rows are a problem. The desired result would be remove the unwanted rows.

CodePudding user response：

Try changing the "UNION ALL" to just plain "UNION", as "UNION ALL" does not remove duplicate rows of data, while "UNION" does. Reference - https://www.techonthenet.com/sql/union_all.php