-2

I am trying to ignore duplicate rows from a CTE but I am not able to do that, it seems like a CTE does not allow to use ROWNUM() variable to WHERE clause as it is showing “Invalid column name 'numrows'” error while trying to do so.

How do I ignore the duplicate rows while using SELECT in a CTE?

SQL Query:

DECLARE @BatchID uniqueidentifier = NEWID();
DECLARE @ClusterID SMALLINT = 1;
DECLARE @BatchSize integer = 20000;
DECLARE @myTableVariable TABLE(
    EventID BIGINT,
    HotelID int,
    BatchStatus varchar(50),
    BatchID uniqueidentifier);

WITH PendingExtResSvcEventsData_Batch AS (
    SELECT TOP (@BatchSize) t.EventID, t.HotelID, t.BatchStatus, t.BatchID,
        ROW_NUMBER() OVER (PARTITION BY t.EventID ORDER BY t.EventID) numrows 
    FROM ExtResSvcPendingMsg t WITH (NOLOCK)
    WHERE t.ClusterID = @ClusterID
      AND numrows = 1
      -- Exclude ExtResSvcEventID and HotelID,
      -- which are partly included in in-progress batch.
      AND NOT EXISTS (
          select 1 from ExtResSvcPendingMsg t2
          where t2.BatchStatus = 'Batched'
            and t2.EventID = t.EventID and t2.HotelID = t.HotelID
          )
    )
UPDATE PendingExtResSvcEventsData_Batch
   SET BatchStatus='Batched',
       BatchID = @BatchID
OUTPUT INSERTED.* INTO @myTableVariable --WHERE numrows = 1

SELECT e.ExtResSvcEventID, e.HotelID, e.ID1, e.ID2, e.ExtResSvcEventType, e.HostID,
       e.StatusCode, e.ChannelID, e.RequestAtTime, e.ProcessTime, e.DateBegin, e.DateEnd,
       e.StatusMsg, em.MsgBodyOut, em.MsgBodyIn, e.ChannelResID 
FROM  ExtResSvcEvent e WITH (NOLOCK)
INNER JOIN @myTableVariable t ON e.ExtResSvcEventID = t.EventID
INNER JOIN ExtResSvcEventXML em WITH (NOLOCK) ON t.EventID = em.ExtResSvcEventID
ORDER BY e.ExtResSvcEventID
7
  • 'It seems like a CTE does not allow to use ROWNUM() variable to where clause'. Nothing allows you to do this, not just a CTE. You seem to have a bit of a CTE obsession. Why? Commented Mar 15, 2018 at 13:12
  • CTE is new for me and I am struggling using it in my query, Can you please advise better approach or suggest changes in my SQL query? Commented Mar 15, 2018 at 13:15
  • Don't use ROW_NUMBER() in WHERE clause in your CTE, but in WHERE clause of UPDATE statement.
    – AdamL
    Commented Mar 15, 2018 at 13:22
  • 1
    Did you try UPDATE .. SET .. WHERE .. OUTPUT ...;? Commented Mar 15, 2018 at 15:46
  • 1
    What's the primary key of ExtResSvcPendingMsg? It would help if you added more details in the question, about what you are trying to do exactly and the tables' definitions. Commented Mar 15, 2018 at 16:00

3 Answers 3

4

I would expect this to work but without table definitions, we can't be sure:

WITH 
PendingExtResSvcEventsData_Batch 
   (...),                           -- your CTE here
upd AS
  ( SELECT EventID, HotelID, BatchStatus, BatchID 
    FROM PendingExtResSvcEventsData_Batch 
    WHERE numrows = 1
  )
UPDATE upd
SET BatchStatus = 'Batched',  
    BatchID = @BatchID
OUTPUT INSERTED.* INTO @myTableVariable ;

A simplified test at: dbfiddle.uk

There are more issues with the query though:

  • What's the reason behind using NOLOCK hint? I suggest you remove it, unless you have a reason for that - and you have read about the consequences and accept the behaviour.
  • You have SELECT TOP (@BatchSize) without a specific ORDER BY. That means that arbitrary rows will be selected to be returned by that subquery.
  • Combined with the WHERE numrows = 1 that you want to apply in the next step, it means that the update may affect fewer rows than the specified @Batchsize, possibly even just one.
0
5

Do not use INSERTED.* here:

OUTPUT INSERTED.* INTO @myTableVariable

Instead, list the columns explicitly:

OUTPUT INSERTED.EventID,
       INSERTED.HotelID,
       INSERTED.BatchStatus,
       INSERTED.BatchID
INTO @myTableVariable

The * means all the columns of the target dataset. The target dataset in your case is a CTE which, apart from normal columns, returns a calculated column. The INSERTED.* syntax includes the calculated column too but referencing a calculated column is not allowed in that context. Listing all the required columns explicitly resolves the issue.

And you can use the numrows filter on that UPDATE (but not inside the CTE). The numrows reference will be valid everywhere apart from the OUTPUT clause and on the left side of an assignment in the SET clause.

Here is a demo, which shamelessly borrows ypercubeᵀᴹ's setup as well as his UPDATE statement.

-1

Don't use nolock here

PARTITION BY t.EventID ORDER BY t.EventID is going to be an arbitrary order. Are you sure that is what you want.

top without and order by is arbitrary. You are partially batching but then you exclude partial batches. This seems wrong to me.

You can just use numrows in the final select.

DECLARE @BatchID uniqueidentifier = NEWID();
DECLARE @ClusterID SMALLINT = 1;
DECLARE @BatchSize integer = 20000;
DECLARE @myTableVariable TABLE(EventID BIGINT,HotelID int, BatchStatus varchar(50),BatchID uniqueidentifier);

WITH PendingExtResSvcEventsData_Batch AS
(  SELECT TOP (@BatchSize) t.EventID, t.HotelID, t.BatchStatus, t.BatchID
       ,  ROW_NUMBER() OVER (PARTITION BY t.EventID ORDER BY t.EventID) numrows 
     FROM ExtResSvcPendingMsg t  
    WHERE t.ClusterID = @ClusterID 
      AND numrows = 1 
      AND NOT EXISTS ( select 1 
                         from ExtResSvcPendingMsg t2 
                        where t2.BatchStatus = 'Batched' 
                          and t2.EventID = t.EventID 
                          and t2.HotelID = t.HotelID       
                      )
)        
UPDATE PendingExtResSvcEventsData_Batch 
   SET BatchStatus = 'Batched',  
       BatchID = @BatchID
OUTPUT INSERTED.* INTO @myTableVariable --WHERE numrows = 1

SELECT e.ExtResSvcEventID, e.HotelID, e.ID1, e.ID2, e.ExtResSvcEventType, e.HostID, e.StatusCode, e.ChannelID, e.RequestAtTime, e.ProcessTime, e.DateBegin, e.DateEnd, e.StatusMsg, em.MsgBodyOut, em.MsgBodyIn, e.ChannelResID 
  FROM ExtResSvcEvent e WITH (NOLOCK) 
 INNER JOIN @myTableVariable t 
    ON e.ExtResSvcEventID = t.EventID    
   AND t.numrows = 1 
 INNER JOIN ExtResSvcEventXML em with (nolock) 
    on t.EventID = em.ExtResSvcEventID            
 ORDER BY e.ExtResSvcEventID

Why output all rows. It looks like you are only using t.EventID.

4
  • thanks for the suggestions I have made those changes and Got 2 errors while testing this query ... Msg 404, Level 16, State 1, Line 6 The column reference "inserted.numrows" is not allowed because it refers to a base table that is not being modified in this statement. Msg 213, Level 16, State 1, Line 6 Column name or number of supplied values does not match table definition. Commented Mar 15, 2018 at 13:58
  • 1
    Then you should have had those errors all along as I changed nothing there. This is just confusing and appears you have not thought this through. Sorry I could not have been of more assistance.
    – paparazzo
    Commented Mar 15, 2018 at 14:12
  • The Select inside the CTE statement having rownum column which you advised to use in final select clause but the numrows is not inserting in @myTableVariable. Its showing error i.e. The column reference "inserted.numrows" is not allowed because it refers to a base table that is not being modified in this statement. OUTPUT Inserted.* INTO @myTableVariable not allowing rownum to insert to @myTableVariable hope this helps to clarify the issue. Commented Mar 15, 2018 at 14:19
  • 1
    Not me advising that. That is and was in YOUR query. You should have been getting those errors all along. This is just a sloppy query that you did not test. I chose not to spend any more time on it.
    – paparazzo
    Commented Mar 15, 2018 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.