Tag: result

I’m working with listagg and I have a problem here. It does not give the desired result and I just do not know where the problem is .

The deposit_query might be like this :

sum (dep_balance) >1 and
sum (turnover)    >1 and
DEP_BRANCH_COD     = -1 and
DEPOSIT_GROUP_DESC = -1 and 
DEPOSIT_TYPE_DESC  = -1 and
CURRENCY_DESC      = -1

or it might be like this (without sum):

DEP_BRANCH_COD     = -1 and
DEPOSIT_GROUP_DESC = -1 and
DEPOSIT_TYPE_DESC  = -1 and
CURRENCY_DESC      = -1 

The desired result for first condition is :

DEP_BRANCH_COD     = -1 and 
DEPOSIT_GROUP_DESC = -1 and
DEPOSIT_TYPE_DESC  = -1 and
CURRENCY_DESC      = -1
group by customer_num
having sum (dep_balance) >1 and
       sum (turnover)    >1 and

and for second condition is :

DEP_BRANCH_COD     = -1 and 
DEPOSIT_GROUP_DESC = -1 and
DEPOSIT_TYPE_DESC  = -1 and
CURRENCY_DESC      = -1

What I’ve written so far is the query below and the problem is that the final result has theGroup by customer_num having in the second condition.

declare

   deposit_query varchar(1000) := 'DEP_BRANCH_COD     = -1, 
                                   DEPOSIT_GROUP_DESC = -1, 
                                   DEPOSIT_TYPE_DESC  = -1, 
                                   CURRENCY_DESC      = -1';
   actual_query varchar2(1000);

begin

   select listagg(case
                    when stm not like '%sum(%' then stm end,
                      ' AND ') within group(order by stm) 
                       || 
          listagg(case
                    when stm like '%sum(%' then stm end, 
                      ' AND ') within group(order by stm) || ' Group by customer_num HAVING ' 
  into actual_query
  from (select regexp_substr(deposit_query, '(^and)+', 1, level) STM
        from dual
      connect by regexp_substr(deposit_query, '(^and)+', 1, level) is not null);

dbms_output.put_line(actual_query);

end;

I was wondering if you could tell me how to modify my query to have the desired result.

thanks in advance