on 01-26-2013 3:43 PM
Hi,
I have a table in HANA,in which I have 7 columns: OrderID, PostingDate, Shift, Material, Unit, Quantity, Amount.
Now for a combination of 1 orderid(001), 1 postingdate (d1) and 1 shift(s1) there can be multiple rows in my table. My requirement is to sort the data of this table in ascending order based on OrderID, PostingDate and Shift and then show a new column say Flag with value as 'Y' only for the 1st row of each group. For example if my data is like:
OrderID | PostingDate | Shift | Material | Unit | Quantity | Amount |
---|---|---|---|---|---|---|
001 | D1 | S1 | M1 | KG | 1005 | 2001 |
001 | D1 | S1 | M2 | KG | 3005 | 4001 |
001 | D2 | S2 | M2 | KG | 5005 | 6002 |
002 | D3 | S2 | M3 | CSE | 7005 | 8003 |
002 | D3 | S2 | M4 | KG | 9005 | 10005 |
002 | D4 | S3 | M5 | KG | 11005 | 12005 |
002 | D4 | S3 | M6 | KG | 14000 | 15000 |
I am trying to write a SQL which should return the below mentioned data:
OrderID | PostingDate | Shift | Material | Unit | Quantity | Amount | Flag |
---|---|---|---|---|---|---|---|
001 | D1 | S1 | M1 | KG | 1005 | 2001 | Y |
001 | D1 | S1 | M2 | KG | 3005 | 4001 | |
001 | D2 | S2 | M2 | KG | 5005 | 6002 | Y |
002 | D3 | S2 | M3 | CSE | 7005 | 8003 | Y |
002 | D3 | S2 | M4 | KG | 9005 | 10005 | |
002 | D4 | S3 | M5 | KG | 11005 | 12005 | Y |
002 | D4 | S3 | M6 | KG | 14000 | 15000 |
Here you can see that the new column "Flag" is initialized only for the 1st row of each group based on OrderID, PostingDate and Shift.
Can you please help me with this requirement?
Regards,
Piyush
Hi Piyush,
This can be achieved through a combination of a window partition and a case statement. The following statement will provide you with what you need:
CASE WHEN ROW_NUMBER() over (partition by "ORDERID", "POSTINGDATE", "SHIFT"
order by "ORDERID", "POSTINGDATE", "SHIFT") =1 THEN 'Y'
ELSE '' END AS FLAG
For a full working example, copy and paste the following code:
CREATE COLUMN TABLE "TABLE3298883" ("ORDERID" VARCHAR(3),
"POSTINGDATE" VARCHAR(2),
"SHIFT" VARCHAR(2),
"MATERIAL" VARCHAR(2),
"UNIT" VARCHAR(3),
"QUANTITY" INTEGER CS_INT,
"AMOUNT" INTEGER CS_INT);
INSERT INTO "TABLE3298883"
VALUES('001','D1','S1','M1','KG',1005,2001);
INSERT INTO "TABLE3298883"
VALUES('001','D1','S1','M2','KG',3005,4001);
INSERT INTO "TABLE3298883"
VALUES('001','D2','S2','M2','KG',5005,6002);
INSERT INTO "TABLE3298883"
VALUES('002','D3','S2','M3','CSE',7005,8003);
INSERT INTO "TABLE3298883"
VALUES('002','D3','S2','M4','KG',9005,10005);
INSERT INTO "TABLE3298883"
VALUES('002','D4','S3','M5','KG',11005,12005);
INSERT INTO "TABLE3298883"
VALUES('002','D4','S3','M6','KG',14000,15000);
SELECT "ORDERID",
"POSTINGDATE",
"SHIFT",
"MATERIAL",
"UNIT",
"QUANTITY",
"AMOUNT",
CASE WHEN ROW_NUMBER() over (partition by "ORDERID", "POSTINGDATE", "SHIFT"
order by "ORDERID", "POSTINGDATE", "SHIFT") =1 THEN 'Y'
ELSE '' END AS FLAG
FROM "TABLE3298883"
ORDER BY 1,2,3
This gives the following result:
For anybody interested in Window Partitions, the SQL Reference can be found here:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Gary,
Thanks for you reply. I tried to run, exactly the same SQL provided by you but it gives me Syntax Error near 'over'. If i comment the logic written for Flag, then it works fine.
Do i need to make any change in the setting of my Studio for Windows Partition functions to work? Currently i am running HANA Studio Rev. 41.
Regards,
Piyush
User | Count |
---|---|
89 | |
10 | |
10 | |
9 | |
6 | |
6 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.