0

i want to calculate or get the price of parent items on bill of materials query

here is the fiddle-> https://www.db-fiddle.com/f/o3jLgZxKNLG14mna8QGdVN/6

please note in the second row, betax has qty = 2 and the other parent items could behave the same

qty of the first row, always will be 1

what i need is to get the 'price' and 'subtotal' for every parent

0

the price will surely be null if you tell it to. Initialize the base case for the CTE with 0 instead:

with recursive cte_main as 
(
    select 'ALFA001'::character varying as id_father
         , 'ALFA001'::character varying as id_son
         , 'ALFA001' as tree 
         , 1::double precision as qty
         , 0::double precision as price
         , 0::double precision as subtotal
         , 'ALFA001'::character varying as sort
         , 0 as levell  

Second, you need to handle cases where the table contains null. You can use COALESCE for that purpose:

    union all
    select e.id_father
         , e.id_son
         , repeat('......',levell+1) || e.id_son as tree
         , e.qty
         , coalesce(e.price,0)
         , e.qty * coalesce(e.price,0) as subtotal
         , sort || ' a- ' || e.id_son::character varying as sort  
         , levell+1 
    from public.main as e
    inner join cte_main 
        on e.id_father = cte_main.id_son 
)
select * from cte_main 
order by sort

Since all your attributes may contain null, you need to take that into consideration for the other attributes as well.

| improve this answer | |
  • I apologize for not being clear on my first attempt. I really appreciate your interest – nkne Jun 19 at 6:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.