ANonmous Change ANonmous Change - 4 months ago 14
SQL Question

pentaho report designer error in query

I use Pentaho Report Designer to generate reports. I am trying to create my first report. I have created a JDBC Data Source. Add

query
then in syntax i write

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


but query 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 because my query works well in other query analyzers?

Answer

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 )