CODEWITHSUNDEEP
sqlt-sqlteradatarow-number
user2040021
user2040021

Reputation: 309

Condition evaluation and applying row numbers in a table

I need guidance to resolve following issue. I am trying to give row number based on condition. Here is my condition:

ROW_NUMBER() OVER (
    PARTITION BY CLAIM_KEY, EXPOSURE_KEY, RESERVELINE_ID, TRANSACTIONSUBTYPE_DESC 
    ORDER BY TRANSACTION_CREATE_TS
)

Here is my table after applying above condition. (I just included one example here to simplify things.)

enter image description here

And here is what I want to achieve, but so far no results.

enter image description here

Upvotes: 1

Views: 80

Answers (2)

Rob Paller
Rob Paller

Reputation: 7786

I think you will need to consider using RESET WHEN as part of your ROW_NUMBER() window aggregate. RESET WHEN can include another window aggregate to allow you to look back at the previous row in the scope of the existing partition of ROW_NUMBER to check the condition of either the amount or close time stamp being NOT NULL.

The use of RESET WHEN is explained in Chapter 22 of SQL Functions, Operators, Expressions, and Predicates manual for Teradata 15.10. The chapter title is Ordered Analytical/Windows Aggregate Functions - The Window Feature.

Hope this helps set you in the right direction.

Upvotes: 2

ravioli
ravioli

Reputation: 3823

Your query seems to be working as expected. Looking at your expected result set image, the rows where you manually added "1, 2, 3, 4" in blue text belong to the same partition as the first three rows:

CLAIM_KEY = 165,529
EXPOSURE_KEY = 158,038
RESERVELINE_ID = 101,692
TRANSACTIONSUBTYPE_DESC = 'Reserve'

That's why the ROW_NUMBER() continues to increment with 4,5,6,7. Try adding ORDER BY CLAIM_KEY, EXPOSURE_KEY, RESERVELINE_ID, TRANSACTIONSUBTYPE_DESC to the end of your full query, so you can see more clearly how the rows are being partitioned together and why the result set is the way it is.

Upvotes: 0

Related Questions