[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*:
)
I still see some other errors in the SQL, though ("tranaction"
misspelling), and I'm not sure what the "_Party_ID_OID" suffix you have for
the fromProperty is about:
private java.math.BigInteger fromParty;
public java.math.BigInteger getFromParty(){ ... }
public void setFromParty*_Party_ID_OID*(java.math.BigInteger id ){ ... }
On 26 February 2014 12:38, james agada <[email protected]> wrote:
> 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
> <[email protected]>wrote:
>
> > On 24 February 2014 21:38, james agada <[email protected]> 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;
> > > }
> > >
> > > }
> > >
> > >
> >
>
