Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: learning grouping: why group doesn't work for joined SQL?

  1. #1

    Default learning grouping: why group doesn't work for joined SQL?

    Dear all. I am learning and experimenting Pentaho Report Designer.

    I can do the grouping by:
    1. use sampledata, set "SELECT * FROM EMPLOYEES ORDER BY EMPLOYEE.OFFICECODE" as data source;
    2. Create a new group by the field "OFFICECODE", Add a text field for OFFICECODE in group header which was set to be visible on the UI. Add text fields for FIRSTNAME and LASTNAME in Item Band;
    3. Click preview I can see the employee list grouped by their OFFICECODE. There are 7 groups.
    I cannot do grouping by:
    1. use sampledata, set data source as:
      Code:
      SELECT EMPLOYEES.*, OFFICES.* 
      FROM EMPLOYEES, OFFICES 
      WHERE EMPLOYEES.OFFICECODE = OFFICES.OFFICECODE 
      ORDERED BY EMPLOYEE.OFFICECODE
      note this data source looks very like the one in above example, the rows and their orders are the same, only difference been more columns.
    2. Same as above (2)
    3. Click preview I can see all employees listed in one group, the group header (appeared only once on top) says "1" (which is OFFICECODE).
    While in the second case, I expect in step 3 to see the identical result as in the first case. Did I do anything wrong? Is there a way to use group when doing joined SQL query?

    Thanks in advance!

  2. #2
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    Well, it should work perfectly. But what could have happened is that your result set does not contain a column called "EMPLOYEE.OFFICECODE". (The field to name mapping in the reporting engine is case-sensitive, while the database might treat everything case-insensitive. Sometimes the databases return full-qualified names if there would be two columns with the same name in the resultset. Other databases just behave strange )

    When executing your second query, look at the result-set that is returned (the names of the columns should be listed in the report-designer's property-area for the datasource) and make sure that your group references to one of the columns there and that the column-name given there is matching whatever the datasource contains.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  3. #3

    Default

    Thank you very much! Now I got it running, I don't know which if my changes fixed it but I have following changes:
    1. Use the query builder, don't write SQL myself (except for the order-by clause which must be written myself because I cannot find the UI widget to add an order-by clause; however once I added order-by clause manually, I can see it on the UI and change ASC to DESC if needed)
    2. I used
      Code:
      "EMPLOYEES"."OFFICECODE"
      instead of
      Code:
      EMPLOYEES.OFFICECODE
      .
    3. Instead of using SELECT * I used detailed column names in SELECT clause. That is, don't use the asterisk.

    I do used preview before I sent my first post, which shows my query is legal in sense of SQL, but thanks for pointing out Pentaho is not strictly doing the db behavior.

  4. #4
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    I do used preview before I sent my first post, which shows my query is legal in sense of SQL, but thanks for pointing out Pentaho is not strictly doing the db behavior.
    The sad truth is, there is no such thing like "strict db behavior". There is no common standard for dealing with databases, as every database implements its own behavior and the SQL and JDBC standards only give rough estaminates on what you can expect from a database. From the very beginning we made the choice of *not* concentrating on a few selected databased but to rely on the JDBC standard alone. Therefore our tools heavily rely on the database drivers to be sane and the databases to behave sane.

    As almost every database has its insanity, the overall count of insane items is quite large. However, unless you do not switch your database system on a daily base, the situation is not as bad as it sounds at first. Most JDBC drivers expose well-defined behavior in themselves, and thus the insanity is more on a global scale than on a local driver scale. What drives people crazy is that the very same task that worked in one particluar way on a database suddenly works slightly different or exposes subtle differences in an other database system.

    if you want to have a fun day, approach an application developer and ask him to add support for another database to the companies applications. Try to keep a straight face while explaining him that this task cant be that hard at all, as all databases use JDBC/ODBC and SQL anyway.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.