2

I am trying to create my first report in Pentaho Report Designer. I have created a JDBC Data Source and added a query:

SELECT a.* 
FROM   (SELECT Sum(loan_receivable_detail.interest) AS interest, 
               loan_account_opening_id 
        FROM   loan_receivable_detail 
        GROUP  BY loan_account_opening_id) AS a

However when I save the query, it changes to: 

SELECT
     a.*,
     sum(loan_receivable_detail.interest) AS interest,
     loan_account_opening_id AS interest
FROM
     `loan_receivable_detail`
GROUP BY
     loan_account_opening_id,
     as,
     a

Is there any problem in my query causing it to change like this? The syntax works well in other query analyzers...

Sean Branchaw
  • 597
  • 1
  • 5
  • 21
ANonmous Change
  • 798
  • 3
  • 10
  • 32

1 Answers1

1

Your query should be valid, but it seems like Pentaho is having problems parsing it.

You could try to use 

SELECT SUM(loan_receivable_detail.interest) AS interest, 
               loan_account_opening_id 
        FROM   loan_receivable_detail 
        GROUP  BY loan_account_opening_id

Which gives the same results as your query but without the subquery (Hopefully it will make Pentaho happy )

Sean Branchaw
  • 597
  • 1
  • 5
  • 21
Petr Chudanic
  • 547
  • 4
  • 12
  • the query was only sample query. I need query with sub queries. – ANonmous Change Jan 04 '14 at 09:50
  • seems like pentaho is having some problems with subqueries as there are few unresolved bugs on their jira about that (i.e. http://jira.pentaho.com/browse/PRD-4360). do not know if it would help you but I would consider moving those subqueries to DB - creating some view based on your query as workaround – Petr Chudanic Jan 04 '14 at 12:48
  • 2
    note those problems are only when using the wizard query designer. If you just paste your query in then Pentaho wont reformat it or do anything with it. – Codek Jan 06 '14 at 16:31