CODEWITHSUNDEEP
sqlpostgresqlrecursive-query
benHikesTen
benHikesTen

Reputation: 21

Recursive CTE must not omit column names

ERROR: Recursive CTE must not omit column names.

I'm trying to write my first recursive query, using Postgresql. I'm getting an error message that the query must not omit column names - I am declaring column names, and can't figure this out. Any pointers on what I'm doing wrong would be much appreciated.

with recursive account_hierarchy as (

select company,entry_no,parent_entry_no, description from fin_bc_raw.r_gl_account_category
where parent_entry_no =0

union all

select company,entry_no,parent_entry_no,description from fin_Bc_raw.r_gl_account_category lvl2
left join account_hierarchy on lvl2.company=account_hierarchy.company and lvl2.parent_entry_no = account_hierarchy.entry_no
)
select company,entry_no,parent_entry_no, description 
from account_hierarchy

Upvotes: 1

Views: 4493

Answers (1)

Edouard
Edouard

Reputation: 7065

First you must list the columns in the cte header (see the manual) because these columns are referenced in the recursive part of the recursive cte itself.

Then you should prefix the column names with the table alias in the recursive part because different joined tables share the same column names.

Then you should use arrays if you want to build multilevel hierarchies in your recursive cte.

Then you should check that there is no infinite loop by introcing a check in the recursive part of the recursive cte.

Finally you will have to filter the resulting rows of the recursive cte so that to select the relevant ones only.

As an example (not tested !) :

with recursive account_hierarchy (company, entry_no, description, parent_entry_no, parent_hierarchy_company, parent_hierarchy_no, parent_hierarchy_description )
as (
-- start from the leaf companies with no children companies
select parent.company
     , parent.entry_no
     , parent.description
     , parent.parent_entry_no
     , array[parent.company]
     , array[parent.entry_no]
     , array[parent.description]
  from fin_bc_raw.r_gl_account_category AS parent
  left join fin_bc_raw.r_gl_account_category AS children
    on children.parent_entry_no = parent.entry_no
 where children.parent_entry_no IS NULL

union all
-- concatenate the parent companies to the already seleccted children companies
select h.company
     , h.entry_no
     , h.description
     , lv12.parent_entry_no
     , lv12.company || h.parent_hierarchy_company
     , lv12.entry_no || h.parent_hierarchy_no
     , lv12.description || h.parent_hierarchy_description 
  from fin_Bc_raw.r_gl_account_category lvl2
 inner join account_hierarchy h
    on h.parent_entry_no = lv12.entry_no
 where NOT h.parent_hierarchy_no @> array[lv12.entry_no] -- avoid inifinite loops
)
select DISTINCT ON (entry_no)
       company, entry_no, description
     , parent_hierarchy_company, parent_hierarchy_no, parent_hierarchy_description
  from account_hierarchy
 ORDER BY entry_no, array_length(parent_hierarchy_no, 1) DESC

Upvotes: 0

Related Questions