SQL & PL/SQL

Hallo everybody!

I have a table with history of changes. The table has the following structure:

key_value - is an object

row_id - is object's attribute

dt - date of changing

new_value - value of attribute after changing

The task is to find values of attributes, actual for determined date, and to aggregate them into one text field for each key_value.

In given example I am expecting the following:

:dt = 20.05.2015

------------------------------------------------

922131       20;73

1238040     45

1281570 

I try the following select:

with a
as
(select 1245506 as key_value, 'AAA7fnAAEAAKCrMABr' as row_id, to_date('15.07.2015','dd.mm.yyyy') as dt, 2 as new_value from dual union all
select 922131, 'AAA7fnAAMAAH6IEACI', to_date('19.05.2015','dd.mm.yyyy'), 19 from dual union all
select 922131, 'AAA7fnAAMAAH6IEACI', to_date('20.05.2015','dd.mm.yyyy'), 73 from dual union all
select 1238040, 'AAA7fnAAEAAKCrMABq', to_date('20.05.2015','dd.mm.yyyy'), 45 from dual union all
select 922131, 'AAA7fnAAEAAKCrMABo', to_date('18.05.2015','dd.mm.yyyy'), null from dual union all
select 1281570, 'AAA7fnAAEAAKCrMABx', to_date('11.05.2015','dd.mm.yyyy'), null from dual union all
select 922131, 'AAA7fnAAMAAH6IEACb', to_date('19.05.2015','dd.mm.yyyy'), 20 from dual union all
select 1238040, 'AAA7fnAAEAAKCrMABq', to_date('21.05.2015','dd.mm.yyyy'), 12 from dual)

SELECT
            key_value, wm_concat(MAX (new_value)
               KEEP (DENSE_RANK LAST ORDER BY a.dt)
               OVER (PARTITION BY row_id) )
    FROM a
   WHERE  a.dt <= :dt

But it gives an error: ORA-00978 nested group function without GROUP BY

When I try:

with a
as
(select 1245506 as key_value, 'AAA7fnAAEAAKCrMABr' as row_id, to_date('15.07.2015','dd.mm.yyyy') as dt, 2 as new_value from dual union all
select 922131, 'AAA7fnAAMAAH6IEACI', to_date('19.05.2015','dd.mm.yyyy'), 19 from dual union all
select 922131, 'AAA7fnAAMAAH6IEACI', to_date('20.05.2015','dd.mm.yyyy'), 73 from dual union all
select 1238040, 'AAA7fnAAEAAKCrMABq', to_date('20.05.2015','dd.mm.yyyy'), 45 from dual union all
select 922131, 'AAA7fnAAEAAKCrMABo', to_date('18.05.2015','dd.mm.yyyy'), null from dual union all
select 1281570, 'AAA7fnAAEAAKCrMABx', to_date('11.05.2015','dd.mm.yyyy'), null from dual union all
select 922131, 'AAA7fnAAMAAH6IEACb', to_date('19.05.2015','dd.mm.yyyy'), 20 from dual union all
select 1238040, 'AAA7fnAAEAAKCrMABq', to_date('21.05.2015','dd.mm.yyyy'), 12 from dual)

SELECT
            key_value, wm_concat(MAX (new_value)
               KEEP (DENSE_RANK LAST ORDER BY a.dt)
               OVER (PARTITION BY row_id) )
    FROM a
   WHERE a.dt <= :dt
GROUP BY key_value

It gives: ORA-00979 not a GROUP BY expression (for MAX (new_value))

How can I solve the task?

We have Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi, so I can't use LISTAGG. Actually, I use user-defined functions,  wm_concat was given just as an example. I receive the same errors with any other aggregate function (e.g. COLLECT).

Сообщение отредактировано: Toshish