0 Replies Latest reply on Aug 3, 2005 3:35 AM by catherine lo

    SELECT COUNT...GROUP BY declared-sql

    catherine lo Newbie

      I tried to execute the following query using JBoss declared-sql:
      SELECT c.Status, Month(c.firstUsed), COUNT(c.serialNumber) FROM Card c WHERE c.status = 4 GROUP BY Month(c.firstUsed)

      I have the following in the jbosscmp-jdbc.xml:


      <query-method>
      <method-name>ejbSelectCountCardsUsed</method-name>
      <method-params>
      </method-params>
      </query-method>

      <declared-sql>
      < select >
      <ejb-name><![CDATA[Card]]></ejb-name>
      <field-name><![CDATA[status]]></field-name>
      < alias ><![CDATA[c]]>< /alias >
      <additional-columns><![CDATA[, Month(c.firstUsed), COUNT(c.serialNumber)]]></additional-columns>
      < /select >
      < where ><![CDATA[ c.status = 4 GROUP BY Month(c.firstUsed)]]>< /where >
      </declared-sql>


      And I have the following method in the Card Entity Bean:

      /**
      * @ejb.home-method view-type="local"
      */
      public Collection ejbHomeCountCardsUsed() throws FinderException {
      return ejbSelectCountCardsUsed();
      }

      /**
      * @ejb.select query=""
      */
      public abstract Collection ejbSelectCountCardsUsed() throws FinderException;


      When I read from the Collection returned by ejbHomeCountCardsUsed(), I was only be able to get the c.Status value. How can I get the Month(c.firstUsed), COUNT(c.serialNumber)?

      Or is there any better way to implement this SELECT COUNT...GROUP BY query?