isis-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From GESCONSULTOR - Óscar Bou <o....@gesconsultor.com>
Subject Re: what could be causing this kind of error
Date Wed, 26 Feb 2014 12:46:17 GMT
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 




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
View raw message