• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Bear Bibeault
  • Paul Clapham
  • Jeanne Boyarsky
Sheriffs:
  • Devaka Cooray
  • Junilu Lacar
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Ron McLeod
  • Tim Holloway
  • Claude Moore
  • Stephan van Hulst
Bartenders:
  • Winston Gutkowski
  • Carey Brown
  • Frits Walraven

SQLite: SQL Query from Two Tables One with All Data and other SUM for Related BillNo  RSS feed

 
Ranch Hand
Posts: 39
Java MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,

I have Two Tables
Bill
TRX

i am trying to List all the data from Table Bill and its Total from Table TRX. the Common Key is "BillNo" in Both Tables.
Trying to List it in a TableView.

Below is what i have done so far which is not working.

OrderListModel.java which contains the Constructor and Getter&Setter as follows;


OrderListController.java which has the related code to display data in TableView;


Please kindly someone help me.

Thanks
OrderTableList.JPG
[Thumbnail for OrderTableList.JPG]
This is a TableView, BillValue Column should be fill with Sum(Price) in TRX Table with BillNo being the common key in Table Bill and Table TRX
 
Ifraz Imanudeen
Ranch Hand
Posts: 39
Java MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Ifraz Imanudeen wrote:Hi All,

I have Two Tables
Bill
TRX

i am trying to List all the data from Table Bill and its Total from Table TRX. the Common Key is "BillNo" in Both Tables.
Trying to List it in a TableView.

Below is what i have done so far which is not working.

OrderListModel.java which contains the Constructor and Getter&Setter as follows;


OrderListController.java which has the related code to display data in TableView;


Please kindly someone help me.

Thanks




ERROR Thrown for above code;

org.sqlite.SQLiteException: [SQLITE_SCHEMA]  The database schema changed (near "(": syntax error)
at org.sqlite.core.DB.newSQLException(DB.java:909)
at org.sqlite.core.DB.newSQLException(DB.java:921)
at org.sqlite.core.DB.throwex(DB.java:886)
at org.sqlite.core.NativeDB.prepare_utf8(Native Method)
at org.sqlite.core.NativeDB.prepare(NativeDB.java:127)
at org.sqlite.core.DB.prepare(DB.java:227)
at org.sqlite.core.CorePreparedStatement.<init>(CorePreparedStatement.java:45)
at org.sqlite.jdbc3.JDBC3PreparedStatement.<init>(JDBC3PreparedStatement.java:30)
at org.sqlite.jdbc4.JDBC4PreparedStatement.<init>(JDBC4PreparedStatement.java:19)
at org.sqlite.jdbc4.JDBC4Connection.prepareStatement(JDBC4Connection.java:48)
at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:263)
at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:235)
at loginfx.OrderListController.getOrderListTotal(OrderListController.java:71)
at loginfx.OrderListController.initialize(OrderListController.java:46)
at javafx.fxml.FXMLLoader.loadImpl(FXMLLoader.java:2548)
at javafx.fxml.FXMLLoader.loadImpl(FXMLLoader.java:2441)
at javafx.fxml.FXMLLoader.loadImpl(FXMLLoader.java:3214)
at javafx.fxml.FXMLLoader.loadImpl(FXMLLoader.java:3175)
at javafx.fxml.FXMLLoader.loadImpl(FXMLLoader.java:3148)
at javafx.fxml.FXMLLoader.loadImpl(FXMLLoader.java:3124)
at javafx.fxml.FXMLLoader.loadImpl(FXMLLoader.java:3104)
at javafx.fxml.FXMLLoader.load(FXMLLoader.java:3097)
at loginfx.formControl.ShowOrderList(formControl.java:110)
at loginfx.MainFormController.ShowOrderListForm(MainFormController.java:47)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at sun.reflect.misc.Trampoline.invoke(MethodUtil.java:71)
at sun.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at sun.reflect.misc.MethodUtil.invoke(MethodUtil.java:275)
at javafx.fxml.FXMLLoader$MethodHandler.invoke(FXMLLoader.java:1769)
at javafx.fxml.FXMLLoader$ControllerMethodEventHandler.handle(FXMLLoader.java:1657)
at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:86)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:49)
at javafx.event.Event.fireEvent(Event.java:198)
at javafx.scene.Node.fireEvent(Node.java:8411)
at javafx.scene.control.Button.fire(Button.java:185)
at com.sun.javafx.scene.control.behavior.ButtonBehavior.mouseReleased(ButtonBehavior.java:182)
at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(BehaviorSkinBase.java:96)
at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(BehaviorSkinBase.java:89)
at com.sun.javafx.event.CompositeEventHandler$NormalEventHandlerRecord.handleBubblingEvent(CompositeEventHandler.java:218)
at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:80)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:54)
at javafx.event.Event.fireEvent(Event.java:198)
at javafx.scene.Scene$MouseHandler.process(Scene.java:3757)
at javafx.scene.Scene$MouseHandler.access$1500(Scene.java:3485)
at javafx.scene.Scene.impl_processMouseEvent(Scene.java:1762)
at javafx.scene.Scene$ScenePeerListener.mouseEvent(Scene.java:2494)
at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:394)
at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:295)
at java.security.AccessController.doPrivileged(Native Method)
at com.sun.javafx.tk.quantum.GlassViewEventHandler.lambda$handleMouseEvent$353(GlassViewEventHandler.java:432)
at com.sun.javafx.tk.quantum.QuantumToolkit.runWithoutRenderLock(QuantumToolkit.java:389)
at com.sun.javafx.tk.quantum.GlassViewEventHandler.handleMouseEvent(GlassViewEventHandler.java:431)
at com.sun.glass.ui.View.handleMouseEvent(View.java:555)
at com.sun.glass.ui.View.notifyMouse(View.java:937)
at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at com.sun.glass.ui.win.WinApplication.lambda$null$147(WinApplication.java:177)
at java.lang.Thread.run(Thread.java:748)



Staff note (Knute Snortum):

There is no need to quote all or any of the previous post. Quote only what you need for context, which in this case is nothing.

 
Sheriff
Posts: 5745
148
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for posting the error message.  Here's what to look for:

org.sqlite.SQLiteException: [SQLITE_SCHEMA]  The database schema changed (near "(": syntax error)
at org.sqlite.core.DB.newSQLException(DB.java:909)
at org.sqlite.core.DB.newSQLException(DB.java:921)
at org.sqlite.core.DB.throwex(DB.java:886)
at org.sqlite.core.NativeDB.prepare_utf8(Native Method)
at org.sqlite.core.NativeDB.prepare(NativeDB.java:127)
at org.sqlite.core.DB.prepare(DB.java:227)
at org.sqlite.core.CorePreparedStatement.<init>(CorePreparedStatement.java:45)
at org.sqlite.jdbc3.JDBC3PreparedStatement.<init>(JDBC3PreparedStatement.java:30)
at org.sqlite.jdbc4.JDBC4PreparedStatement.<init>(JDBC4PreparedStatement.java:19)
at org.sqlite.jdbc4.JDBC4Connection.prepareStatement(JDBC4Connection.java:48)
at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:263)
at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:235)
at loginfx.OrderListController.getOrderListTotal(OrderListController.java:71)
at loginfx.OrderListController.initialize(OrderListController.java:46)
...  


The message tells you what's wrong up front.  Then it's sometimes difficult to find your class, but scan down the left-hand side, looking for your package name.  Soon you will find the class, method and line number where the error occurred.

So in class OrderListController, method getOrderListTotal, line 71, there is an SQL syntax error.  Try executing the SQL by itself.  
 
Ifraz Imanudeen
Ranch Hand
Posts: 39
Java MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


Knute Snortum

That's the help i am seeking for.

How to write the SQL statement to obtain the results.

"SELECT Bill.Status, Bill.dtd, Bill.BillNo, Bill.CIF, Bill.CustomerName, Bill.Mobile, TRX.SUM(Price) FROM Bill INNER JOIN TRX ON  Bill.BillNo = TRX.BillNo"

I have Two Tables
Bill
TRX

i am trying to List all the data from Table "Bill" and its Total from Table "TRX" . the Common Key is "BillNo" in Both Tables.
Trying to List it in a TableView.




Knute Snortum wrote:Thanks for posting the error message.  Here's what to look for:

org.sqlite.SQLiteException: [SQLITE_SCHEMA]  The database schema changed (near "(": syntax error)
at org.sqlite.core.DB.newSQLException(DB.java:909)
at org.sqlite.core.DB.newSQLException(DB.java:921)
at org.sqlite.core.DB.throwex(DB.java:886)
at org.sqlite.core.NativeDB.prepare_utf8(Native Method)
at org.sqlite.core.NativeDB.prepare(NativeDB.java:127)
at org.sqlite.core.DB.prepare(DB.java:227)
at org.sqlite.core.CorePreparedStatement.<init>(CorePreparedStatement.java:45)
at org.sqlite.jdbc3.JDBC3PreparedStatement.<init>(JDBC3PreparedStatement.java:30)
at org.sqlite.jdbc4.JDBC4PreparedStatement.<init>(JDBC4PreparedStatement.java:19)
at org.sqlite.jdbc4.JDBC4Connection.prepareStatement(JDBC4Connection.java:48)
at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:263)
at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:235)
at loginfx.OrderListController.getOrderListTotal(OrderListController.java:71)
at loginfx.OrderListController.initialize(OrderListController.java:46)
...  


The message tells you what's wrong up front.  Then it's sometimes difficult to find your class, but scan down the left-hand side, looking for your package name.  Soon you will find the class, method and line number where the error occurred.

So in class OrderListController, method getOrderListTotal, line 71, there is an SQL syntax error.  Try executing the SQL by itself.  

 
Marshal
Posts: 24195
54
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Ifraz Imanudeen wrote:SELECT Bill.Status, Bill.dtd, Bill.BillNo, Bill.CIF, Bill.CustomerName, Bill.Mobile, TRX.SUM(Price) FROM Bill INNER JOIN TRX ON  Bill.BillNo = TRX.BillNo



Notice that the SQL error message said

The database schema changed (near "(": syntax error)



Notice particularly that it mentions near "(", although the rest of the message is rather obscure. Anyway there's only one "(" in your proposed SQL so you should conclude that the error is something to do with that.

And what I notice then is that using a SUM(x) function requires "GROUP BY x" later in the SQL. Your SQL doesn't have that. Just offhand I don't recall exactly how to join and group at the same time so just tacking on "GROUP BY TRX.Price" at the end might not be the whole answer.
 
Ifraz Imanudeen
Ranch Hand
Posts: 39
Java MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Paul Clapham wrote:

Ifraz Imanudeen wrote:SELECT Bill.Status, Bill.dtd, Bill.BillNo, Bill.CIF, Bill.CustomerName, Bill.Mobile, TRX.SUM(Price) FROM Bill INNER JOIN TRX ON  Bill.BillNo = TRX.BillNo



Notice that the SQL error message said

The database schema changed (near "(": syntax error)



Notice particularly that it mentions near "(", although the rest of the message is rather obscure. Anyway there's only one "(" in your proposed SQL so you should conclude that the error is something to do with that.

And what I notice then is that using a SUM(x) function requires "GROUP BY x" later in the SQL. Your SQL doesn't have that. Just offhand I don't recall exactly how to join and group at the same time so just tacking on "GROUP BY TRX.Price" at the end might not be the whole answer.



Thanks for the Tip of Group By.

Got it Working.

For other Viewers Reference, below was my Query




 
Knute Snortum
Sheriff
Posts: 5745
148
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Good job figuring it out, and thanks for posting the solution back to the thread.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!