skip to Main Content

In the query I shared below, I use 3 recursive loops to cumulatively retrieve debit, credit and budget values. Can I do this using 1 recursion instead of 3 recursions?
Also, can it be done in this format without changing the format of the query? Briefly, what I want to do in the query is to cumulatively reflect the debit, credit and budget (debit-credit) of the sub-account codes to a parent account for the account codes in accounting, and thus reflect the cumulative debit, credit and budget values ​​to the main account at the top.

select hkodu.id,
       hkodu.hesap_kodu           as hesapKodu,
       hkodu.hesap_adi            as hesapAdi,
       hkodu.parent_hesap_kodu_id as parentId,
       btc.butce_yili             as butceYili,
       hkodu.active               as active,
       hkodu.owner_birim_id       as birimId,
       (WITH recursive tree_hk AS (SELECT h.id, h.hesap_kodu, h.hesap_adi, h.parent_hesap_kodu_id, b.borc, b.alacak
                                   FROM hesap_kodu h
                                            JOIN butce b ON h.id = b.hesap_kodu_id
                                   where h.id = hkodu.id
                                     and h.active = true

                                   UNION ALL

                                   SELECT hk.id,
                                          hk.hesap_kodu,
                                          hk.hesap_adi,
                                          hk.parent_hesap_kodu_id,
                                          bc.borc,
                                          bc.alacak
                                   FROM hesap_kodu hk
                                            JOIN butce bc ON hk.id = bc.hesap_kodu_id
                                            JOIN tree_hk tr ON tr.id = hk.parent_hesap_kodu_id
                                   where hk.active = true)

        select sum(borc - alacak)
        from tree_hk)             as butce,

       (WITH recursive tree_hk AS (SELECT h.id, h.hesap_kodu, h.hesap_adi, h.parent_hesap_kodu_id, b.borc, b.alacak
                                   FROM hesap_kodu h
                                            JOIN butce b ON h.id = b.hesap_kodu_id
                                   where h.id = hkodu.id
                                     and h.active = true

                                   UNION ALL

                                   SELECT hk.id,
                                          hk.hesap_kodu,
                                          hk.hesap_adi,
                                          hk.parent_hesap_kodu_id,
                                          bc.borc,
                                          bc.alacak
                                   FROM hesap_kodu hk
                                            JOIN butce bc ON hk.id = bc.hesap_kodu_id
                                            JOIN tree_hk tr ON tr.id = hk.parent_hesap_kodu_id
                                   where hk.active = true)

        select sum(alacak)
        from tree_hk)             as alacak,
       (WITH recursive tree_hk AS (SELECT h.id, h.hesap_kodu, h.hesap_adi, h.parent_hesap_kodu_id, b.borc, b.alacak
                                   FROM hesap_kodu h
                                            JOIN butce b ON h.id = b.hesap_kodu_id
                                   where h.id = hkodu.id
                                     and h.active = true

                                   UNION ALL

                                   SELECT hk.id,
                                          hk.hesap_kodu,
                                          hk.hesap_adi,
                                          hk.parent_hesap_kodu_id,
                                          bc.borc,
                                          bc.alacak
                                   FROM hesap_kodu hk
                                            JOIN butce bc ON hk.id = bc.hesap_kodu_id
                                            JOIN tree_hk tr ON tr.id = hk.parent_hesap_kodu_id
                                   where hk.active = true)

        select sum(borc)
        from tree_hk)             as borc

from hesap_kodu hkodu
         left join butce btc on btc.hesap_kodu_id = hkodu.id;

2

Answers


  1. Sure, you need only to call it once and than canrefer to it as often as you need

    WITH recursive tree_hk AS (
    SELECT h.id, h.hesap_kodu, h.hesap_adi, h.parent_hesap_kodu_id, b.borc, b.alacak
       FROM hesap_kodu h
                JOIN butce b ON h.id = b.hesap_kodu_id
       where h.active = true
    
       UNION ALL
    
       SELECT hk.id,
              hk.hesap_kodu,
              hk.hesap_adi,
              hk.parent_hesap_kodu_id,
              bc.borc,
              bc.alacak
       FROM hesap_kodu hk
                JOIN butce bc ON hk.id = bc.hesap_kodu_id
                JOIN tree_hk tr ON tr.id = hk.parent_hesap_kodu_id
       where hk.active = true)
    select hkodu.id,
           hkodu.hesap_kodu           as hesapKodu,
           hkodu.hesap_adi            as hesapAdi,
           hkodu.parent_hesap_kodu_id as parentId,
           btc.butce_yili             as butceYili,
           hkodu.active               as active,
           hkodu.owner_birim_id       as birimId,
           (select sum(borc - alacak)
            from tree_hk
     WHERE id = hkodu.id)             as butce,
    
           (
    
            select sum(alacak)
            from tree_hk
     WHERE id = hkodu.id)             as alacak,
           (select sum(borc)
            from tree_hk
     WHERE id = hkodu.id)             as borc
    
    from hesap_kodu hkodu
             left join butce btc on btc.hesap_kodu_id = hkodu.id;
    
    Login or Signup to reply.
  2. Without some sample data and expected result it is hard to tell, but maybe you could get it without recursions. If it is possible to make correct partition by and order by in Sum() Over() analytic function with windowing clause – then it would look something like below. Think that in postgres windowing clause (Rows Between/Range) could be ommited as it defaults to all preceding till current row.
    Once again, not sure, but might be worth to try – it’s up to you.

    select  hkodu.id,
            hkodu.hesap_kodu           as hesapKodu,
            hkodu.hesap_adi            as hesapAdi,
            hkodu.parent_hesap_kodu_id as parentId,
            btc.butce_yili             as butceYili,
            hkodu.active               as active,
            hkodu.owner_birim_id       as birimId,
        --
            Sum(Coalesce(btc.borc, 0)) 
                    Over(Partition By hkodu.hesap_kodu, hkodu.hesap_adi, hkodu.parent_hesap_kodu_id
                         Order By hkodu.id
                         Rows Between Unbounded Preceding And Current Row) 
            - Sum(Coalesce(btc.alacak, 0) 
                    Over(Partition By hkodu.hesap_kodu, hkodu.hesap_adi, hkodu.parent_hesap_kodu_id
                         Order By hkodu.id
                         Rows Between Unbounded Preceding And Current Row) as butce
        --
            Sum(Coalesce(btc.alacak, 0) 
                    Over(Partition By hkodu.hesap_kodu, hkodu.hesap_adi, hkodu.parent_hesap_kodu_id
                         Order By hkodu.id
                         Rows Between Unbounded Preceding And Current Row) as alacak,
        --
            Sum(Coalesce(btc.borc, 0)) 
                    Over(Partition By hkodu.hesap_kodu, hkodu.hesap_adi, hkodu.parent_hesap_kodu_id
                         Order By hkodu.id
                         Rows Between Unbounded Preceding And Current Row) as borc               
    from       hesap_kodu hkodu
    left join  butce btc on btc.hesap_kodu_id = hkodu.id
    where      hkodu.active = true
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search