Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Any way to influence internal sql AS-Name or its use?

  1. #1
    Join Date
    May 2006
    Posts
    104

    Question Any way to influence internal sql AS-Name or its use?

    Our software supplier replied as follows as I asked about a problem in using their jdbc driver with Mondrian+JPivot. The 5.031, 6.20 and 6.21 references are versions of their product.

    I wrote:
    >> The following statement returns an SQLException "Unresolved reference(s):
    >> KM80.EGRP_BEZ" in 6.20 and 6.21. If I remove the "as c0", then it works
    >> fine. The statement was built and executed internally from JPivot while
    >> resolving an MDX (cube) query from Mondrian. We had this whole thing
    >> working with 5.031. Is this working as expected or should I be
    >> able to reference via c0 or km80.egrp_bez? Is the handling different
    >> between 5.031 and 6.2x?
    >>
    >> select "KM80"."EGRP_BEZ" as "c0" from "KM80" as "KM80" group by
    >> "KM80"."EGRP_BEZ" order by "KM80"."EGRP_BEZ" ASC

    They answered:
    >Well, the quick answers are that 6.2x is working as expected and the
    >behavior is different from 5.031. The issue is that the "group by"
    >happens _after_ the select, so the only columns that it sees are the
    >result columns of the select, "c0", not the original columns from the table.

    So my question here, is whether there is a way to influence the AS-name or its use in Pentaho's Data Analysis tool?
    Ralph

  2. #2
    Join Date
    Nov 1999
    Posts
    1,618

    Default

    What you call the "AS" name, I would call a column alias.

    What database are you using? I'm guessing it's something weird - e.g. DB2 through a non-standard driver. I know DB2 is picky about what you put in the ORDER BY clause, like, once you've defined an alias, you have to use it.

    If you could contribute a fix to SqlQuery.Dialect, that would be just dandy. See especially

    Code:
    /**
     * Returns true if this Dialect can include expressions in the ORDER BY
     * clause only by adding an expression to the SELECT clause and using
     * its alias.
     *
     * <p>For example, in such a dialect,
     * <blockquote>
     * <code>SELECT x FROM t ORDER BY x + y</code>
     * </blockquote>
     * would be illegal, but
     * <blockquote>
     * <code>SELECT x, x + y AS z FROM t ORDER BY z</code>
     * </blockquote>
     *
     * would be legal.</p>
     *
     * <p>MySQL, DB2 and Ingres are examples of such dialects.</p>
     *
     * @return Whether this Dialect can include expressions in the ORDER BY
     *   clause only by adding an expression to the SELECT clause and using
     *   its alias
     */
    public boolean requiresOrderByAlias() {
        return isMySQL() || isDB2() || isIngres();
    }
    Julian

  3. #3
    Join Date
    May 2006
    Posts
    104

    Default

    You guessed it, Julian. They wrote:

    >After looking at it further, we have clarified the problem and understand it
    >better. The problem is actually in the ORDER BY clause. BBj only allows
    >ORDER BY specifying the AS name when used in conjunction with a GROUP BY.
    >Other DBMSs allow you to refer to either the AS name or the original column
    >name, if and only if that column is one of the columns in the GROUP BY.

    "BBj" is a relatively new language from a company called BASIS from Albuquerque, New Mexico that melds business basic (in which our 20 yr. old app is written) and java together. It's their own jdbc solution that is touted as at least SQL 92 standard. They gave me a case number, which usually means they intend to correct the problem. The sql statement worked in an earlier version of BBj.

    Thanks again.
    Ralph

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.