RE: General queries

From: Weddle, Anthony (Anthony.Weddl..luescopesteel.com)
Date: Wed Apr 08 2009 - 19:24:02 EDT

  • Next message: Weddle, Anthony: "Reengineering Overwrites"

    Well, it looks like EJBQL could be the way to go. It's not as powerful
    as HQL (or maybe it just doesn't have quite the same features) but it
    seems to do a lot of what we might need. It does have some issues
    though.

    This is part of one HQL query that we use in one report:

    select ios,
      (select nvl(sum(iosbu.curQty),0)
            from Iosbu as iosbu
            where iosbu.iosb.ios = ios
              and iosbu.iosb.iosbs.iosbStat = 'A'
              and (iosbu.iosb.useByDt is null
              or iosbu.iosb.useByDt > sysdate)) as currentPaintQty,
      (select nvl(sum(iosbu.curQty),0)
            from Iosbu as iosbu
            where iosbu.iosb.ios = ios
            and (iosbu.iosb.iosbs.iosbStat != 'A'
              or iosbu.iosb.useByDt < sysdate)) as heldPaintQty
              from Ios as ios "

    EJBQL doesn't support subselects in the select clause or have something
    equivalent to the NVL function, so we'd have to do things differently.
    However, I wondered if it could cope with the first subselect and so
    ended up with this EJBQL query (accounting for different relationship
    names in Cayenne):

    select iosbu.toIosb.toIos, sum(iosbu.curQty)
      from Iosbu as iosbu, Ios as ios
      where iosbu.toIosb.toIosbs.id = 'A'
        and (iosbu.toIosb.useByDt is null
        or iosbu.toIosb.useByDt > CURRENT_DATE)
      group by iosbu.toIosb.toIos

    I know I didn't need the "from Ios" but cayenne didn't convert that
    properly, anyway. It created joins for the various relationships, adding
    the joined table between Iosbu and Ios, in the from clause, but without
    adding another comma. So it ended up with:

    SELECT t1.PART_CODE, SUM(t0.CUR_QTY) AS sc0 FROM IOSBU t0, INNER JOIN
    IOSB t1 ON (t0.IOSB_ID = t1.IOSB_ID) INNER JOIN IOSBS t3 ON
    (t1.IOSB_STAT = t3.IOSB_STAT) IOS t2 WHERE t3.IOSB_STAT = ? AND
    t1.USE_BY_DT IS NULL OR t1.USE_BY_DT > {fn CURDATE()} GROUP BY
    t1.PART_CODE

    Notice the "IOS t2" without a preceeding comma.

    As I didn't need to specify Ios in the from clause, I took it out. Then
    I got another problem. Note that selecting the relationship (ending in
    "toIos") results in Cayenne selecting the key for that related table
    (I'm assuming that Cayenne would subsequently use the key to retrieve
    the whole object) but it gets the type wrong. The key is a String but
    Cayenne seems to think it's a Long and tries to retrieve a Long from the
    result set, which fails.

    When I changed the query to select the id of the relationship, then it
    worked fine. So this was the final query:

    select iosbu.toIosb.toIos.id, sum(iosbu.curQty
      from Iosbu as iosbu
        where iosbu.toIosb.toIosbs.id = 'A'
          and (iosbu.toIosb.useByDt is null
          or iosbu.toIosb.useByDt > CURRENT_DATE)
    group by iosbu.toIosb.toIos.id

    I'm getting there!

    Cheers,
    Tony

    -----Original Message-----
    From: Andrus Adamchik [mailto:andru..bjectstyle.org]
    Sent: Wednesday, 8 April 2009 5:46 p.m.
    To: use..ayenne.apache.org
    Subject: Re: General queries

    Cayenne 3.0 includes support for EJBQLQuery which seem like you what
    you need here:

       http://cayenne.apache.org/doc/ejbqlquery.html

    We are following the JPA syntax per JSR-220
    (http://jcp.org/aboutJava/communityprocess/final/jsr220/index.html
    ). At does support aggregates, separate columns, subqueries, etc.

    This archive was generated by hypermail 2.0.0 : Wed Apr 08 2009 - 19:24:09 EDT