Oracle SQL: Sum Over Partition returns error in Select

Question

I have a larger Select statement which works fine until I include the following which returns the below error.
Using just the Sum part works too but I only need this for the first row of each AID which is why I used the Row Number here.

Can someone tell me what I am doing wrong here ?
Is the problem maybe that Sum and Partition are referring to two different tables which I am joining in the Select ?

My SQL (part of Select):

, CASE 
    WHEN ROW_NUMBER() OVER (PARTITION BY dp.SHIPPER_ID ORDER BY di.AID) = 1 THEN (SUM(di.SHIPPED_UNITS) OVER (PARTITION BY dp.SHIPPER_ID))
    ELSE '' 
END AS Gesamtmenge

Error message:

ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

Many thanks in advance for any help,
Mike

@MiloBellano: Thanks so much - that works perfect ! If you post it as an answer I will accept this. :) — Mike, Jun 20 '17 at 07:22
Happy to help! :) I have posted the answer if you want to accept it. Thanks mate! — MiloBellano, Jun 20 '17 at 07:26

score 1 · Answer 1 · answered Jun 20 '17 at 07:22

1

Error says all. You are using '' which is essentially a char and not int.

, CASE 
    WHEN ROW_NUMBER() OVER (PARTITION BY dp.SHIPPER_ID ORDER BY di.AID) = 1 THEN (SUM(di.SHIPPED_UNITS) OVER (PARTITION BY dp.SHIPPER_ID))
    ELSE NULL 
END AS Gesamtmenge

answered Jun 20 '17 at 07:22

Prabhat G

2,974
1
22
31

Thanks a lot ! It works. Milo just posted this as well. – Mike Jun 20 '17 at 07:23
Good. I missed by 2 minutes :) – Prabhat G Jun 20 '17 at 07:24

score 1 · Accepted Answer · answered Jun 20 '17 at 07:25

1

As original column is integer, you can't pass a char so

, CASE 
  WHEN ROW_NUMBER() OVER (PARTITION BY dp.SHIPPER_ID ORDER BY di.AID) = 1 THEN 
 (SUM(di.SHIPPED_UNITS) OVER (PARTITION BY dp.SHIPPER_ID))
 ELSE NULL
END AS Gesamtmenge

answered Jun 20 '17 at 07:25

MiloBellano

396
1
5
15

score 0 · Answer 3 · answered Jun 20 '17 at 07:24

0

Exactly as error message points out you can not mix different datatypes in CASE. For example this is wrong

select case when 1=1 then 2 else '' end from dual

Use null or 0 in else or convert then expression to string

answered Jun 20 '17 at 07:24

Serg

22,285
5
21
48

Oracle SQL: Sum Over Partition returns error in Select

3 Answers3