CODEWITHSUNDEEP
sqlhana
Justin Kelley
Justin Kelley

Reputation: 107

Assign group numbers with window-function ROW_NUMBER in HANA SQL

I am having to type lots of text up here to allow me to post this.

Please ignore this text and see my question below.

I have the following code with a partition:

SELECT rownum,"SRNUM" ,
"SRAnalyzeddate",
"Account",
"U_USD_TOTAL_POTENTIAL_NNACV",
"U_USD_TOTAL_UNDEPLOYED_BACKLOG",
"U_USD_TOTAL_UNDER_SUBSCRIBED", 
"DV_U_BUSINESS_UNIT",
"CUSTOMER_SINCE",
"ContractEnd",
"Segment",
"Industry",
"Territory",    
"Vertical",
"FIELD_GEO",
"FIELD_MAJOR_AREA",
"FIELD_AREA"
 FROM 
 (
select SR."NUMBER" AS "SRNUM" ,SR."U_ANALYZED_DATE" AS "SRAnalyzeddate",SR."DV_SALES_ACCOUNT" AS "Account","U_USD_TOTAL_POTENTIAL_NNACV", 
"U_USD_TOTAL_UNDEPLOYED_BACKLOG","U_USD_TOTAL_UNDER_SUBSCRIBED", "DV_U_BUSINESS_UNIT","CUSTOMER_SINCE",
acc."U_NEW_SEGMENT" AS "Segment",
acc."DV_INDUSTRY" As "Industry",
acc."DV_U_ACCOUNT_TERRITORY" As "Territory",    
acc."DV_U_VERTICAL" As "Vertical",
ter."DV_U_GEO" AS "FIELD_GEO",
ter."DV_U_MAJOR_AREA" AS "FIELD_MAJOR_AREA",
ter."DV_U_AREA" AS "FIELD_AREA",
 MAX("END_DATE") AS "ContractEnd",
 ROW_NUMBER() OVER (PARTITION BY SR."NUMBER" ORDER BY SR."DV_SALES_ACCOUNT" DESC) AS rownum
 from  "SURF_RT"."SALES_ACCOUNT" acc
left join "SURF_RT"."SALES_REQUEST" SR  on  acc."NAME" = SR."DV_SALES_ACCOUNT"
left join "SURF_RT"."SALES_CONTRACT" con on con."DV_ACCOUNT"=SR."DV_SALES_ACCOUNT"
left join "SURF_RT"."U_SALES_TERRITORY" ter on acc."DV_U_ACCOUNT_TERRITORY" = ter."U_NAME"
JOIN "SURF_RT"."U_SALES_REQUEST_ALERT_LINES" line on sr."NUMBER"=line."DV_U_SALES_REQUEST"
JOIN "SURF_RT"."SALES_PRODUCT" prod on line."U_PRODUCT_CODE"=prod."U_PRODUCT_CODE"
WHERE 
"DV_SALES_CATEGORY"='Compliance' AND SR."DV_STATE"  NOT IN ('Closed Canceled')
AND YEAR("U_ANALYZED_DATE") = '2020' AND MONTH("U_ANALYZED_DATE") IN ('1','2','3','4')
AND acc."DV_TYPE" NOT IN ('Suspect','Prospect','Inactive','Inactive-Former Customer','Vendor')
GROUP BY SR."NUMBER" ,
SR."U_ANALYZED_DATE",
SR."DV_SALES_ACCOUNT",
"U_USD_TOTAL_POTENTIAL_NNACV",
"U_USD_TOTAL_UNDEPLOYED_BACKLOG",
"U_USD_TOTAL_UNDER_SUBSCRIBED",
"DV_U_BUSINESS_UNIT",
"CUSTOMER_SINCE",
acc."U_NEW_SEGMENT",
acc."DV_INDUSTRY",
acc."DV_U_ACCOUNT_TERRITORY",   
acc."DV_U_VERTICAL",
ter."DV_U_GEO" ,
ter."DV_U_MAJOR_AREA" ,
ter."DV_U_AREA"
ORDER BY SR."DV_SALES_ACCOUNT","SRNUM",
"SRAnalyzeddate"
)

I would like each SRNUM for a single DV_SALES_ACCOUNT to have a different row number like this:

SRNUM1 = rownum1
SRNUM1 = rownum1
SRNUM1 = rownum1
SRNUM2 = rownum2
SRNUM2 = rownum2
SRNUM2 = rownum2
SRNUM3 = rownum3
SRNUM3 = rownum3
SRNUM3 = rownum3

Instead I am getting this:

SRNUM1 = rownum1
SRNUM1 = rownum2
SRNUM1 = rownum3
SRNUM2 = rownum1
SRNUM2 = rownum2
SRNUM2 = rownum3
SRNUM3 = rownum1
SRNUM3 = rownum2
SRNUM3 = rownum3

How can I fix this? Thanks!

I am typing a placeholder right here, because for some reason they think my question is "too much code".

Upvotes: 0

Views: 452

Answers (1)

Lars Br.
Lars Br.

Reputation: 10396

The verbal description of what you want to achieve is different from your expected output. In fact, the output you currently get is what you described.

Looking back at your expected output

SRNUM1 = rownum1
SRNUM1 = rownum1
SRNUM1 = rownum1
SRNUM2 = rownum2
SRNUM2 = rownum2
SRNUM2 = rownum2
SRNUM3 = rownum3
SRNUM3 = rownum3
SRNUM3 = rownum3

it seems that you want to assign each SRNUM with its own number (per account).

With that insight, it's easy to go back to your SQL and implement that:

[...]
ROW_NUMBER() OVER 
 (PARTITION BY 
           SR."DV_SALES_ACCOUNT", SR."NUMBER" 
  ) AS "ACCOUNT_SALES_REQUEST_NUMBER"
[...]

You want to be careful with the choice of alias names. ROWNUM is a term that is already used in SQL and mixes database technology concerns with your application domain concern.

As an example, I used "ACCOUNT_SALES_REQUEST_NUMBER" here, which is rather long to type, but at least it says on the tin what it is.

Upvotes: 1

Related Questions