isis-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From james agada <okwuiag...@gmail.com>
Subject Re: what could be causing this kind of error
Date Wed, 26 Feb 2014 14:20:41 GMT
>
> This is my class - cleaned up

/*

 *  Licensed to the Apache Software Foundation (ASF) under one

 *  or more contributor license agreements.  See the NOTICE file

 *  distributed with this work for additional information

 *  regarding copyright ownership.  The ASF licenses this file

 *  to you under the Apache License, Version 2.0 (the

 *  "License"); you may not use this file except in compliance

 *  with the License.  You may obtain a copy of the License at

 *

 *        http://www.apache.org/licenses/LICENSE-2.0

 *

 *  Unless required by applicable law or agreed to in writing,

 *  software distributed under the License is distributed on an

 *  "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY

 *  KIND, either express or implied.  See the License for the

 *  specific language governing permissions and limitations

 *  under the License.

 */


package dom.todo;


import java.math.BigDecimal;

import java.util.List;


import javax.jdo.annotations.Extension;

import javax.jdo.annotations.IdentityType;

import javax.jdo.annotations.InheritanceStrategy;


import org.joda.time.LocalDate;


import org.apache.isis.applib.AbstractViewModel;

import org.apache.isis.applib.annotation.Bookmarkable;

import org.apache.isis.applib.annotation.DescribedAs;

import org.apache.isis.applib.annotation.Hidden;

import org.apache.isis.applib.annotation.Immutable;

import org.apache.isis.applib.annotation.Optional;

import org.apache.isis.applib.annotation.Render;

import org.apache.isis.applib.annotation.Render.Type;

import org.apache.isis.applib.annotation.Title;




@javax.jdo.annotations.PersistenceCapable(

    identityType = IdentityType.NONDURABLE,

    table = "DailySalesTotalForParty",

    extensions = {

        @Extension(vendorName = "datanucleus", key = "view-definition",

            value = "CREATE VIEW \"DailySalesTotalForParty\" " +

                    "( " +

                    "  {this.transactionDate}, " +

                    "  {this.totalAmount} " +

                    "  {this.fromParty} " +

                    ") AS " +

                    "SELECT " +

                    "   \"Transaction\".\"transactionDate\" , " +


                    "   SUM(\"Transaction\".\"faceValue\") AS
\"totalAmount\" ," +

                    "   \"Transaction\".\"fromParty\"  " +

                    "  FROM " +

"   \"Transaction\" " +

                    "GROUP BY " +

                    " \"transactionDate\",  " +

" \"fromParty\" " +

                    "ORDER BY " +

                    " \"transactionDate\" ")

    })

@javax.jdo.annotations.Inheritance(strategy = InheritanceStrategy.NEW_TABLE)

@javax.jdo.annotations.Queries( {

@javax.jdo.annotations.Query(

name="DailySalesForParty", language="JDOQL",

value="SELECT FROM dom.todo.DailySalesTotalForParty WHERE fromParty ==
:party ")

})

@Bookmarkable

@Immutable

public class DailySalesTotalForParty  {


        // //////////////////////////////////////

/**

    @javax.jdo.annotations.Column(allowsNull = "false")

    private String fromParty;


    /**

     * Lazily loaded from the {@link #getReference() reference}, provides
access

     * to the underlying {@link Property}.

      @Optional

    @Title(sequence = "1")

    public String getFromParty() {

        return fromParty;

    }


    public void setFromParty(final String party) {

        this.fromParty = party;

    }

*/

    // //////////////////////////////////////

 private java.math.BigInteger fromParty;

 @Optional

public java.math.BigInteger getFromParty(){

return this.fromParty;

}

public void setFromParty_Party_ID_OID(java.math.BigInteger id ){

this.fromParty =id;

}


 //////////////////////////////////////

    private LocalDate transactionDate;


    @Title(sequence = "2", prepend = " - ")

    public LocalDate getTransactionDate() {

        return transactionDate;

    }


    public void setTransactionDate(final LocalDate dueDate) {

        this.transactionDate = dueDate;

    }


    // //////////////////////////////////////


    private BigDecimal totalAmount;


    public BigDecimal getTotalAmount() {

        return totalAmount;

    }


    public void setTotalAmount(final BigDecimal total) {

        this.totalAmount = total;

    }




    // //////////////////////////////////////


   /** @Render(Type.EAGERLY)

    public List<Transaction> getTransactions() {

    return transactions.findTransactionForPartyOnDate(getFromParty(),
getTransactionDate());

    }

*/

    // //////////////////////////////////////


    private Partytypes partytypes;


    final public void injectPartytypes(final Partytypes partytypes) {

        this.partytypes = partytypes;

    }


    private Transactions transactions;


    final public void injectTransactions(final Transactions transactions) {

        this.transactions = transactions;

    }


}


This is the error


15:34:03,784  [Schema               main       DEBUG]  CREATE VIEW
> "DailySalesTotalForParty" (   "transactionDate",   "totalAmount"
> "fromParty" ) AS SELECT    "Transaction"."transactionDate" ,
> SUM("Transaction"."faceValue") AS "totalAmount" ,
> "Transaction"."fromParty"    FROM    "Transaction" GROUP BY
> "transactionDate",   "fromParty" ORDER BY  "transactionDate"
>
> 15:34:03,790  [Datastore            main       ERROR]  Error thrown
> executing CREATE VIEW "DailySalesTotalForParty" (   "transactionDate",
> "totalAmount"   "fromParty" ) AS SELECT    "Transaction"."transactionDate"
> ,    SUM("Transaction"."faceValue") AS "totalAmount" ,
> "Transaction"."fromParty"    FROM    "Transaction" GROUP BY
> "transactionDate",   "fromParty" ORDER BY  "transactionDate"  : unexpected
> token: fromParty required: )
>
> java.sql.SQLSyntaxErrorException: unexpected token: fromParty required: )
>
> at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
>
> at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
>
> at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
>
> at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source)
>
>
SQL seems ok, what am i missing?




On Wed, Feb 26, 2014 at 1:46 PM, GESCONSULTOR - Óscar Bou <
o.bou@gesconsultor.com> wrote:

> Hi, James.
>
> I think that the GROUP BY clause should include all fields referenced in
> the SELECT that are not part of aggregation formulas (like SUM, AVG, MIN,
> MAX, etc.).
>
> As "Transaction"."fromParty_Party_ID_OID" is referenced on the SELECT part
> without being included on any of those aggregation formulas, it should be
> included on the GROUP BY section.
>
> HTH,
>
> Oscar
>
>
>
> El 26/02/2014, a las 13:38, james agada <okwuiagada@gmail.com> escribió:
>
> Took your advice and got it to work when I do not have the fromParty
> attribute. What could i be doing wrong. This is the error
>
> 47:29,831  [Schema               main       DEBUG]  Check of existence of
>
> "DailySalesTotalForParty" returned no table
>
> 13:47:29,831  [Schema               main       DEBUG]  Check of existence
> of "DailySalesTotalForParty" returned no table
>
> 13:47:29,831  [Schema               main       DEBUG]  Creating table
> "DailySalesTotalForParty"
>
> 13:47:29,831  [Schema               main       DEBUG]  Creating table
> "DailySalesTotalForParty"
>
> 13:47:29,831  [Schema               main       DEBUG]  Creating table
> "DailySalesTotalForParty"
>
> 13:47:29,832  [Schema               main       DEBUG]  CREATE VIEW
> "DailySalesTotalForParty" (   "transactionDate",   "totalAmount"
> "fromParty" ) AS SELECT    "Transaction"."transactionDate" ,
> SUM("Transaction"."faceValue") AS "totalAmount" ,
> "Transaction"."fromParty_Party_ID_OID" AS "fromParty"   FROM
> "Transaction" GROUP BY  "transactionDate"  ORDER BY  "transactionDate"
>
> 13:47:29,832  [Schema               main       DEBUG]  CREATE VIEW
> "DailySalesTotalForParty" (   "transactionDate",   "totalAmount"
> "fromParty" ) AS SELECT    "Transaction"."transactionDate" ,
> SUM("Transaction"."faceValue") AS "totalAmount" ,
> "Transaction"."fromParty_Party_ID_OID" AS "fromParty"   FROM
> "Transaction" GROUP BY  "transactionDate"  ORDER BY  "transactionDate"
>
> 13:47:29,832  [Schema               main       DEBUG]  CREATE VIEW
> "DailySalesTotalForParty" (   "transactionDate",   "totalAmount"
> "fromParty" ) AS SELECT    "Transaction"."transactionDate" ,
> SUM("Transaction"."faceValue") AS "totalAmount" ,
> "Transaction"."fromParty_Party_ID_OID" AS "fromParty"   FROM
> "Transaction" GROUP BY  "transactionDate"  ORDER BY  "transactionDate"
>
> 13:47:29,833  [Datastore            main       ERROR]  Error thrown
> executing CREATE VIEW "DailySalesTotalForParty" (   "transactionDate",
> "totalAmount"   "fromParty" ) AS SELECT    "Transaction"."transactionDate"
> ,    SUM("Transaction"."faceValue") AS "totalAmount" ,
> "Transaction"."fromParty_Party_ID_OID" AS "fromParty"   FROM
> "Transaction" GROUP BY  "transactionDate"  ORDER BY  "transactionDate"  :
> unexpected token: fromParty required: )
>
> java.sql.SQLSyntaxErrorException: unexpected token: fromParty required: )
>
> at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
>
> at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
>
> at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
>
>
>
> And this is the class now
>
> package dom.todo;
>
>
> import java.math.BigDecimal;
>
> import java.util.List;
>
>
> import javax.jdo.annotations.Extension;
>
> import javax.jdo.annotations.IdentityType;
>
> import javax.jdo.annotations.InheritanceStrategy;
>
>
> import org.joda.time.LocalDate;
>
>
> import org.apache.isis.applib.AbstractViewModel;
>
> import org.apache.isis.applib.annotation.Bookmarkable;
>
> import org.apache.isis.applib.annotation.DescribedAs;
>
> import org.apache.isis.applib.annotation.Hidden;
>
> import org.apache.isis.applib.annotation.Immutable;
>
> import org.apache.isis.applib.annotation.Optional;
>
> import org.apache.isis.applib.annotation.Render;
>
> import org.apache.isis.applib.annotation.Render.Type;
>
> import org.apache.isis.applib.annotation.Title;
>
>
>
>
> /**
>
> * View model that provides a summary of the sales made on a given day by
> each party
>
> @javax.jdo.annotations.PersistenceCapable(
>
> identityType = IdentityType.NONDURABLE,
>
> table = "DailySalesTotalForParty",
>
> extensions = {
>
> @Extension(vendorName = "datanucleus", key = "view-definition",
>
> value = "CREATE VIEW \"DailySalesTotalForParty\" " +
>
> "( " +
>
> "  {this.transactionDate}, " +
>
> "  {this.fromParty}, " +
>
> "  {this.transactiontype}, " +
>
> "  {this.totalAmount} " +
>
> ") AS " +
>
> "SELECT " +
>
> "   \"Tranaction\".\"transactionDate\" , " +
>
> "   \"Transaction.\".\"fromParty\", " +
>
> "   \"Transaction.\".\"transactiontype\", " +
>
> "   SUM(\"Transaction\".\"facevalue\") AS \"totalAmount\", " +
>
> "  FROM \"Transaction\" " +
>
> "GROUP BY " +
>
> " \"fromParty\", " +
>
> " \"transactionDate\" , " +
>
> " \"transactiontype\""  +
>
> "ORDER BY " +                     " \"fromParty\", " +
>
> " \"transactionDate\" , " +
>
> " \"transactiontype\"" )
>
> })
>
>
>
> */
>
> @javax.jdo.annotations.PersistenceCapable(
>
>    identityType = IdentityType.NONDURABLE,
>
>    table = "DailySalesTotalForParty",
>
>    extensions = {
>
>        @Extension(vendorName = "datanucleus", key = "view-definition",
>
>            value = "CREATE VIEW \"DailySalesTotalForParty\" " +
>
>                    "( " +
>
>                    "  {this.transactionDate}, " +
>
>                    "  {this.totalAmount} " +
>
>                    "  {this.fromParty} " +
>
>                    ") AS " +
>
>                    "SELECT " +
>
>                    "   \"Transaction\".\"transactionDate\" , " +
>
>
>                    "   SUM(\"Transaction\".\"faceValue\") AS
> \"totalAmount\" ," +
>
>                    "   \"Transaction\".\"fromParty_Party_ID_OID\" AS
> \"fromParty\" " +
>
>                    "  FROM " +
>
> "   \"Transaction\" " +
>
>                    "GROUP BY " +
>
>                    " \"transactionDate\"  " +
>
>                    "ORDER BY " +
>
>                    " \"transactionDate\" ")
>
>    })
>
> @javax.jdo.annotations.Inheritance(strategy =
> InheritanceStrategy.NEW_TABLE)
>
> @javax.jdo.annotations.Queries( {
>
> @javax.jdo.annotations.Query(
>
> name="DailySalesForParty", language="JDOQL",
>
> value="SELECT FROM dom.todo.DailySalesTotalForParty WHERE fromParty ==
> :party ")
>
> })
>
> @Bookmarkable
>
> @Immutable
>
> public class DailySalesTotalForParty  {
>
>
>        // //////////////////////////////////////
>
> /**
>
>    @javax.jdo.annotations.Column(allowsNull = "false")
>
>    private String fromParty;
>
>
>    /**
>
>     * Lazily loaded from the {@link #getReference() reference}, provides
> access
>
>     * to the underlying {@link Property}.
>
>      @Optional
>
>    @Title(sequence = "1")
>
>    public String getFromParty() {
>
>        return fromParty;
>
>    }
>
>
>    public void setFromParty(final String party) {
>
>        this.fromParty = party;
>
>    }
>
> */
>
>    // //////////////////////////////////////
>
> private java.math.BigInteger fromParty;
>
> @Optional
>
> public java.math.BigInteger getFromParty(){
>
> return this.fromParty;
>
> }
>
> public void setFromParty_Party_ID_OID(java.math.BigInteger id ){
>
> this.fromParty =id;
>
> }
>
>
> //////////////////////////////////////
>
>    private LocalDate transactionDate;
>
>
>    @Title(sequence = "2", prepend = " - ")
>
>    public LocalDate getTransactionDate() {
>
>        return transactionDate;
>
>    }
>
>
>    public void setTransactionDate(final LocalDate dueDate) {
>
>        this.transactionDate = dueDate;
>
>    }
>
>
>    // //////////////////////////////////////
>
>
>    private BigDecimal totalAmount;
>
>
>    public BigDecimal getTotalAmount() {
>
>        return totalAmount;
>
>    }
>
>
>    public void setTotalAmount(final BigDecimal total) {
>
>        this.totalAmount = total;
>
>    }
>
>
>
>
>    // //////////////////////////////////////
>
>
>   /** @Render(Type.EAGERLY)
>
>    public List<Transaction> getTransactions() {
>
>    return transactions.findTransactionForPartyOnDate(getFromParty(),
> getTransactionDate());
>
>    }
>
> */
>
>    // //////////////////////////////////////
>
>
>    private Partytypes partytypes;
>
>
>    final public void injectPartytypes(final Partytypes partytypes) {
>
>        this.partytypes = partytypes;
>
>    }
>
>
>    private Transactions transactions;
>
>
>    final public void injectTransactions(final Transactions transactions) {
>
>        this.transactions = transactions;
>
>    }
>
>
> }
>
>
>
> On Mon, Feb 24, 2014 at 11:44 PM, Dan Haywood
> <dan@haywood-associates.co.uk>wrote:
>
> On 24 February 2014 21:38, james agada <okwuiagada@gmail.com> wrote:
>
> You are right. It comes from this definition - not sure what is wrong
>
> with
>
> it
>
> I can see several issues, some trivial, some probably the cause...
>
>
> If, once you've addressed these, there are still issues, then I suggest you
> strip the class back to a single field, get that working, and then build it
> up bit by bit...
>
> Dan
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> * /* *  Licensed to the Apache Software Foundation (ASF) under one *  or
> more contributor license agreements.  See the NOTICE file *  distributed
> with this work for additional information *  regarding copyright
>
> ownership.
>
> The ASF licenses this file *  to you under the Apache License, Version
>
> 2.0
>
> (the *  "License"); you may not use this file except in compliance *
>
> with
>
> the License.  You may obtain a copy of the License at * *
> http://www.apache.org/licenses/LICENSE-2.0
> <http://www.apache.org/licenses/LICENSE-2.0> * *  Unless required by
> applicable law or agreed to in writing, *  software distributed under the
> License is distributed on an *  "AS IS" BASIS, WITHOUT WARRANTIES OR
> CONDITIONS OF ANY *  KIND, either express or implied.  See the License
>
> for
>
> the *  specific language governing permissions and limitations *  under
>
> the
>
> License. */*
>
> (trivial) you probably will want to change the license...
>
>
>
>
> * package dom.todo;*
>
> (trivial) ... and the package
>
>
>
> [snip]
>
>
>
> /**
> * View model that provides a summary of the sales made on a given day by
> each party
> */
> @javax.jdo.annotations.PersistenceCapable(
>    identityType = IdentityType.NONDURABLE,
>    table = "DailySalesTotalForParty",
>    extensions = {
>        @Extension(vendorName = "datanucleus", key = "view-definition",
>            value = "CREATE VIEW \"DailySalesTotalForParty\" " +
>                    "( " +
>                    "  {this.transactionDate}, " +
>
> *                     "  {this.fromPartyId}, " +*
>
>
> I don't think JDO is clever enough to convert this id into a reference to a
> Party (how would it know?)  So you should do what we did
> in InvoiceSummaryForPropertyDueDate in Estatio (which is what I think you
> based this on) and make FromParty a derived field that does the lookup from
> a (new) fromPartyId field.
>
>
>
>
>
> *                     "  {this.transactiontype}, " +*
>
>
> there is no 'transactiontype' field on your class ...
>
>
>
>                     "  {this.totalAmount} " +
>                    ") AS " +
>                    "SELECT " +
>
> *                     "   \"Tranaction\".\"transactiondate\" , " +*
>
>
>
> the typo on this line "Tranaction".
>
> Also, I suggest keeping capitalization consistent, ie transactionDate, not
> transactiondate.
>
>
>
>                     "   \"Transaction.\".\"fromParty\", " +
>                    "   \"Transaction.\".\"transactiontype\", "
> +
>                    "   SUM(\"Transaction\".\"facevalue\") AS
> \"totalAmount\", " +
>                    "  FROM \"Transaction\" " +
>                    "GROUP BY " +
>                    " \"Transaction\".\"fromParty\", " +
>                    " \"Transaction\".\"transactiondate\" , " +
>                    " \"Transaction\".\"transactiontype\""  +
>                    "ORDER BY " +                     "
> \"Transaction\".\"fromParty\", " +
>                    " \"Transaction\".\"transactiondate\" , " +
>                    " \"Transaction\".\"transactiontype\"" )
>    })
> @javax.jdo.annotations.Inheritance(strategy =
> InheritanceStrategy.NEW_TABLE)
> @javax.jdo.annotations.Queries( {
>        @javax.jdo.annotations.Query(
>                                name="DailySalesForParty",
> language="JDOQL",
>                                value="SELECT FROM
> dom.todo.DailySalesTotalForParty WHERE fromParty == :party ")
>                        })
> @Bookmarkable
> @Immutable
> public class DailySalesTotalForParty  {
>
>        // //////////////////////////////////////
>
>
>    @javax.jdo.annotations.Column(allowsNull = "false")
>    private Party fromParty;
>
>
>
>
>
>
> *     /**      * Lazily loaded from the {@link #getReference()
>
> reference},
>
> provides access      * to the underlying {@link Property}.      */*
>
>
>
> This comment is from the Estatio code (but does describe what you should do
> to resolve "FromParty").
>
>
>     @Optional
>    @Title(sequence = "1")
>    public Party getFromParty() {
>        return fromParty;
>    }
>
>    public void setFromParty(final Party party) {
>        this.fromParty = party;
>    }
>
>    // //////////////////////////////////////
>
>    private LocalDate transactionDate;
>
>    @Title(sequence = "2", prepend = " - ")
>    public LocalDate getTransactionDate() {
>        return transactionDate;
>    }
>
>    public void setTransactionDate(final LocalDate dueDate) {
>        this.transactionDate = dueDate;
>    }
>
>    // //////////////////////////////////////
>
>    private BigDecimal totalAmount;
>
>    public BigDecimal getTotalAmount() {
>        return totalAmount;
>    }
>
>    public void setTotalAmount(final BigDecimal total) {
>        this.totalAmount = total;
>    }
>
>
>    // //////////////////////////////////////
>
>   /** @Render(Type.EAGERLY)
>    public List<Transaction> getTransactions() {
>        return transactions.findTransactionForPartyOnDate(getFromParty(),
> getTransactionDate());
>    }
> **/
>    // //////////////////////////////////////
>
>    private Partytypes partytypes;
>
>    final public void injectPartytypes(final Partytypes partytypes) {
>        this.partytypes = partytypes;
>    }
>
>    private Transactions transactions;
>
>    final public void injectTransactions(final Transactions
>
> transactions) {
>
>        this.transactions = transactions;
>    }
>
> }
>
>
>
>
>
> Óscar Bou Bou
> Responsable de Producto
> Auditor Jefe de Certificación ISO 27001 en BSI
> CISA, CRISC, APMG ISO 20000, ITIL-F
>
>    902 900 231 / 620 267 520
>    http://www.twitter.com/oscarbou
>
>    http://es.linkedin.com/in/oscarbou
>
>    http://www.GesConsultor.com <http://www.gesconsultor.com/>
>
>
>
> Este mensaje y los ficheros anexos son confidenciales. Los mismos
> contienen información reservada que no puede ser difundida. Si usted ha
> recibido este correo por error, tenga la amabilidad de eliminarlo de su
> sistema y avisar al remitente mediante reenvío a su dirección electrónica;
> no deberá copiar el mensaje ni divulgar su contenido a ninguna persona.
> Su dirección de correo electrónico junto a sus datos personales constan en
> un fichero titularidad de Gesdatos Software, S.L. cuya finalidad es la de
> mantener el contacto con Ud. Si quiere saber de qué información disponemos
> de Ud., modificarla, y en su caso, cancelarla, puede hacerlo enviando un
> escrito al efecto, acompañado de una fotocopia de su D.N.I. a la siguiente
> dirección: Gesdatos Software, S.L. , Paseo de la Castellana, 153 bajo -
> 28046 (Madrid), y Avda. Cortes Valencianas num. 50, 1ºC - 46015 (Valencia).
> Asimismo, es su responsabilidad comprobar que este mensaje o sus archivos
> adjuntos no contengan virus informáticos, y en caso que los tuvieran
> eliminarlos.
>
>
>
>
>
>

Mime
  • Unnamed multipart/related (inline, None, 0 bytes)
View raw message