postgresql sql with recursive returns no data

[alain_vu]

Hello,

I have folowing sql that I can execute in an SQL-window in PgAdmin on my database but used in an Jasper Report, I get no data:

select * from (
WITH RECURSIVE q AS
(
SELECT  h, 1 AS level, ARRAY[h.code] AS breadcrumb, a.name as nummer, ac.code as rekening, a.ref as ref, a.amount as amount, res_company.name as company_name, account_period.name as period_name,
account_analytic_dimension.sequence as an_dim_seq, account_analytic_dimension.name as an_dim_name,
a.write_date as datum, account_analytic_journal.name as an_jrnl_name
FROM    account_analytic_account h
INNER JOIN         res_company on res_company.id = h.company_id
LEFT OUTER JOIN    account_analytic_line a ON a.account_id = h.id
LEFT OUTER JOIN    account_account ac ON ac.id = a.general_account_id
LEFT OUTER JOIN    account_analytic_journal on account_analytic_journal.id = a.journal_id
LEFT OUTER JOIN    account_period on account_period.id = a.period_id
LEFT OUTER JOIN    account_fiscalyear on account_fiscalyear.id = account_period.fiscalyear_id
LEFT OUTER JOIN    account_analytic_dimension on account_analytic_dimension.id = h.dimension_id
WHERE h.code = 'B01'
  and res_company.name = 'Natuurpunt Beheer vzw'
  and (
       a.name IS NULL
       or
       '2013' = account_fiscalyear.name
      )

UNION ALL

SELECT  hi, q.level + 1 AS level, breadcrumb || hi.code, al.name as nummer, ac.code as rekening, al.ref as ref, al.amount as amount, res_company.name as company_name, account_period.name as period_name,
account_analytic_dimension.sequence as an_dim_seq, account_analytic_dimension.name as an_dim_name,
al.write_date as datum, account_analytic_journal.name as an_jrnl_name
FROM    q
JOIN    account_analytic_account hi ON hi.parent_id = (q.h).id
INNER JOIN         res_company on res_company.id = hi.company_id
LEFT OUTER JOIN    account_analytic_line al ON al.account_id = hi.id
LEFT OUTER JOIN    account_account ac ON ac.id = al.general_account_id
LEFT OUTER JOIN    account_analytic_journal on account_analytic_journal.id = al.journal_id
LEFT OUTER JOIN    account_period on account_period.id = al.period_id
LEFT OUTER JOIN    account_fiscalyear on account_fiscalyear.id = account_period.fiscalyear_id
LEFT OUTER JOIN    account_analytic_dimension on account_analytic_dimension.id = hi.dimension_id

)
SELECT  REPEAT('  ', level) || (q.h).id,
(q.h).parent_id,
(q.h).code,
rekening,
level,
nummer,
ref,
amount,
company_name,
period_name,
an_dim_seq,
an_dim_name,
datum,
an_jrnl_name,
breadcrumb::VARCHAR AS path
FROM    q
ORDER BY
breadcrumb) as t

 

Does anybody have any idea what may be the reason?

Thanks in advance for any help