oracle sql - how to partition by row_number

Question

I have this select statement where I'd like to be able to partition by row_number() which I just defined in previous line.

but I am getting "ORA-30483: window functions are not allowed here".

How can I partition by row_number?
If this is not possible, how can I rewrite this "partition by" statement?

SELECT DISTINCT
       ID "ID",
       career "Career",
       ROW_NUMBER ()
       OVER (PARTITION BY ID, Career
             ORDER BY ID, Career)
           "Row",
       (SUM (some numbers + some numbers)
        OVER (
            PARTITION BY ID,
                         (ROW_NUMBER ()
                          OVER (PARTITION BY ID, Career
                                ORDER BY ID, Career))))
           "All_Units"
FROM   TABLE       a .....

Here is the sample. What I like to do is have "All_Units" column partition by ID, Row (Row is row_number() over (partition by ID,Career order by ID,Career). But I'm getting "window functions are not allowed here" error. So for 02233445, I would like All_Units to be partition by ID and Row, which returns 251 for Row 1 is the answer I want.

ID          Career     Row  All_Units 
01122334    GR         1    #    
01122334    GR         2    #
02233445    GR         1    251   
02233445    GR         2    100
02233445    LAW        1    251
04323333    GR         1    # 
04323333    GR         2    # 

Answer 1

You'll need to use sub-queries to reference the result of one window function inside another. Something like this (untested):

SELECT DISTINCT
       a.emplid AS "Student ID",
       a.acad_career AS "Acad Career",
       a_row_number AS "Row",
       (SUM (
              (SELECT COALESCE (SUM (i.unt_passd_prgrss), 0)
               FROM   ps_stf_gr_term_vw i
               WHERE  a.emplid = i.emplid AND a.acad_career = i.acad_career)
            + (SELECT COALESCE (SUM (j.unt_trnsfr), 0)
               FROM   ps_stf_gr_trnsfr_v j
               WHERE  a.emplid = j.emplid AND a.acad_career = j.acad_career))
        OVER (PARTITION BY a.emplid, a_row_number))
           AS "All Units"
FROM   (SELECT a.*,
               ROW_NUMBER ()
               OVER (PARTITION BY a.emplid, a.acad_career
                     ORDER BY a.emplid, a.acad_career)
                   AS a_row_number
        FROM   table_a) a

However, if I'm understanding the purpose of your query correctly, this is a bit of a roundabout way of getting to the desired result. I generally find that the presence of a sub-query in the column list is a suggestion that the query has gone in a bad direction.

If the goal is simply to have every row in table alongside a row number and the count of passed and transferred units, I think this query would be a better design (though it is also untested).

SELECT a.emplid AS "Student ID",
       a.acad_career AS "Acad Career",
       ROW_NUMBER ()
       OVER (PARTITION BY a.emplid, a.acad_career
             ORDER BY a.emplid, a.acad_career)
           AS "Row",
       COALESCE (unt_passd_prgrss, 0) + COALESCE (unt_trnsfr, 0))
           AS "All Units"
FROM   table_a a
       LEFT JOIN
       (SELECT emplid,
               acad_career,
               COALESCE (SUM (unt_passd_prgrss), 0) AS unt_passd_prgrss
        FROM   ps_stf_gr_term_vw) i
           ON a.emplid = i.emplid AND a.acad_career = i.acad_career
       LEFT JOIN
       (SELECT emplid,
               acad_career,
               COALESCE (SUM (unt_trnsfr), 0) AS unt_trnsfr
        FROM   ps_stf_gr_trnsfr_v) j
           ON a.emplid = j.emplid AND a.acad_career = j.acad_career

However, while these solutions should be syntactically correct, I don't believe they will produce your desired result. I don't have enough information to produce that.