Create or replace view does not work. Sent from my iPad
> On Mar 12, 2014, at 6:41 PM, GESCONSULTOR - Óscar Bou > <[email protected]> wrote: > > Hi, James. > > You can simply do a search on Eclipse for that string. Or perhaps you're > using a database where that view has been created previously. > > Depending on the RDBMS you use, you can use something similar to "CREATE OR > REPLACE VIEW" instead of "CREATE VIEW". > > See [1] for an example using MySQL. Same happens with other RDMBS. > > HTH, > > Oscar > > [1] http://www.w3schools.com/sql/sql_view.asp > > >> El 12/03/2014, a las 16:07, james agada <[email protected]> escribió: >> >> I thought I did. I am persistently getting another error now. >>> >>> 16:14:35,803 [Schema main DEBUG] Creating table >>> "DailySalesTotalForParty" >>> >>> 16:14:35,803 [Schema main DEBUG] Creating table >>> "DailySalesTotalForParty" >>> >>> 16:14:35,803 [Schema main DEBUG] CREATE VIEW >>> DailySalesTotalForParty ( "transactionDate", "totalAmount" , >>> "fromPartyReference", "transactiontype" ) AS SELECT >>> "Transaction"."transactionDate" , SUM("Transaction"."faceValue") AS >>> totalAmount , "Transaction"."fromPartyReference" , >>> "Transaction"."transactiontype" FROM "Transaction" GROUP BY >>> "Transaction"."transactionDate", "Transaction"."fromPartyReference", >>> "Transaction"."transactiontype" >>> >>> 16:14:35,803 [Schema main DEBUG] CREATE VIEW >>> DailySalesTotalForParty ( "transactionDate", "totalAmount" , >>> "fromPartyReference", "transactiontype" ) AS SELECT >>> "Transaction"."transactionDate" , SUM("Transaction"."faceValue") AS >>> totalAmount , "Transaction"."fromPartyReference" , >>> "Transaction"."transactiontype" FROM "Transaction" GROUP BY >>> "Transaction"."transactionDate", "Transaction"."fromPartyReference", >>> "Transaction"."transactiontype" >>> >>> 16:14:35,803 [Schema main DEBUG] CREATE VIEW >>> DailySalesTotalForParty ( "transactionDate", "totalAmount" , >>> "fromPartyReference", "transactiontype" ) AS SELECT >>> "Transaction"."transactionDate" , SUM("Transaction"."faceValue") AS >>> totalAmount , "Transaction"."fromPartyReference" , >>> "Transaction"."transactiontype" FROM "Transaction" GROUP BY >>> "Transaction"."transactionDate", "Transaction"."fromPartyReference", >>> "Transaction"."transactiontype" >>> >>> 16:14:35,804 [Datastore main ERROR] Error thrown >>> executing CREATE VIEW DailySalesTotalForParty ( "transactionDate", >>> "totalAmount" , "fromPartyReference", "transactiontype" ) AS SELECT >>> "Transaction"."transactionDate" , SUM("Transaction"."faceValue") AS >>> totalAmount , "Transaction"."fromPartyReference" , >>> "Transaction"."transactiontype" FROM "Transaction" GROUP BY >>> "Transaction"."transactionDate", "Transaction"."fromPartyReference", >>> "Transaction"."transactiontype" : object name already exists: >>> DAILYSALESTOTALFORPARTY in statement [CREATE VIEW DailySalesTotalForParty ( >>> "transactionDate", "totalAmount" , "fromPartyReference", >>> "transactiontype" ) AS SELECT "Transaction"."transactionDate" , >>> SUM("Transaction"."faceValue") AS totalAmount , >>> "Transaction"."fromPartyReference" , "Transaction"."transactiontype" >>> FROM "Transaction" GROUP BY "Transaction"."transactionDate", >>> "Transaction"."fromPartyReference", "Transaction"."transactiontype" ] >>> >>> java.sql.SQLSyntaxErrorException: object name already exists: >>> DAILYSALESTOTALFORPARTY in statement [CREATE VIEW DailySalesTotalForParty ( >>> "transactionDate", "totalAmount" , "fromPartyReference", >>> "transactiontype" ) AS SELECT "Transaction"."transactionDate" , >>> SUM("Transaction"."faceValue") AS totalAmount , >>> "Transaction"."fromPartyReference" , "Transaction"."transactiontype" >>> FROM "Transaction" GROUP BY "Transaction"."transactionDate", >>> "Transaction"."fromPartyReference", "Transaction"."transactiontype" ] >>> >>> at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) >>> >>> Not sure why it says object name already exists!!. >> >> >> On Thu, Feb 27, 2014 at 12:03 AM, Dan Haywood >> <[email protected]>wrote: >> >>> Glad you finally sorted it. >>> >>> >>>> On 26 February 2014 16:34, james agada <[email protected]> wrote: >>>> >>>> SOLVED: >>>> 1. Fix SQL issues and ensure the columns are in the GROUP BY Clause if >>> they >>>> are not an aggregate >>>> 2. Replace entity reference with direct column reference. it appears the >>>> referenced objects have column in table with _ID_OID postfix >>>> 3. Match the types for the entity reference column - they are BIGINT, >>>> declare as Long. >>>> >>>> >>>>> On Wed, Feb 26, 2014 at 4:05 PM, james agada <[email protected]> >>>> wrote: >>>> >>>>> Oops. Fixed the missing comma but the error is still there in a >>> different >>>>> way >>>>> >>>>> integration.tests.actions.ToDoItemTest_completed Time elapsed: 0 sec >>>> <<< >>>>>> ERROR! >>>>>> >>>>>> java.lang.RuntimeException: >>>>>> org.datanucleus.exceptions.NucleusDataStoreException: An exception was >>>>>> thrown while adding/validating class(es) : user lacks privilege or >>>> object >>>>>> not found: fromParty >>>>>> >>>>>> java.sql.SQLSyntaxErrorException: user lacks privilege or object not >>>>>> found: fromParty >>>>> >>>>> >>>>> On Wed, Feb 26, 2014 at 3:31 PM, Dan Haywood < >>>> [email protected] >>>>>> wrote: >>>>> >>>>>> You are still missing the comma before "fromParty" field in the >>> SELECT >>>>>> clause. >>>>>> >>>>>> @Extension(vendorName = "datanucleus", key = >>> "view-definition", >>>>>> >>>>>> value = "CREATE VIEW \"DailySalesTotalForParty\" " + >>>>>> >>>>>> "( " + >>>>>> >>>>>> " {this.transactionDate}, " + >>>>>> >>>>>> " {this.totalAmount} " + >>>> <<<<<<<<<<<<<< >>>>>> HERE <<<<<<<<<<<<< >>>>>> >>>>>> " {this.fromParty} " + >>>>>> >>>>>> ") AS " + >>>>>> >>>>>> >>>>>> >>>>>> >>>>>>> On 26 February 2014 14:20, james agada <[email protected]> wrote: >>>>>>> >>>>>>> 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 < >>>>>>> [email protected]> 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 <[email protected]> >>>>>> 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 >>>>>>>> <[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; >>>>>>>> } >>>>>>>> >>>>>>>> } >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> Ó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. > > > Óscar Bou Bou > Responsable de Producto > Auditor Jefe de Certificación ISO 27001 en BSI > CISA, CRISC, APMG ISO 20000, ITIL-F > > <contactenos.html.gif> 902 900 231 / 620 267 520 > <Pasted Graphic 1.tiff> http://www.twitter.com/oscarbou > > <gesdatos-software.gif> http://es.linkedin.com/in/oscarbou > > <blog.png> http://www.GesConsultor.com > > <gesconsultor_logo_blue_email.png> > > > 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. > > > > >
