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

[Ifraz Imanudeen]

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;
public class OrderListModel {    private Integer Nbr;    private String Status;    private String Date;    private Integer BillNbr;    private Integer Cif;    private String Customer;    private Integer Mobile;    private Double BillValue;

OrderListController.java which has the related code to display data in TableView;
public class OrderListController implements Initializable {    @FXML private TableView<OrderListModel> OrderListTable;    @FXML private TableColumn<?, Integer> nbr;    @FXML private TableColumn<?, ?> status;    @FXML private TableColumn<?, ?> dtd;    @FXML private TableColumn<?, ?> billnbr;    @FXML private TableColumn<?, ?> cif;    @FXML private TableColumn<?, ?> customer;    @FXML private TableColumn<?, ?> mobile;    @FXML private TableColumn<?, ?> billvalue;              Connection connect;    private PreparedStatement pst = null;    private ResultSet rs = null;    private ObservableList<OrderListModel> oblist;        @Override    public void initialize(URL url, ResourceBundle rb) {      oblist = FXCollections.observableArrayList();      loadTableView();      getOrderListTotal();    }                      public void getOrderListTotal(){           try(Connection connect = SqliteConnection.Connector();)           {               pst = connect.prepareStatement("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");               rs = pst.executeQuery();               while(rs.next()){                   oblist.add(new OrderListModel(rs.getString("Status"),rs.getString("dtd"),rs.getInt("BillNo"),rs.getInt("CIF"),rs.getString("CustomerName"),rs.getInt("Mobile"),rs.getDouble("TRX.SUM(Price)")));               }           }           catch(SQLException ex)           {               Logger.getLogger(InvoiceContoller.class.getName()).log(Level.SEVERE, null, ex);           }           OrderListTable.setItems(oblist);       }       public void loadTableView(){           nbr.setCellValueFactory(nbr-> new ReadOnlyObjectWrapper<>(OrderListTable.getItems().indexOf(nbr.getValue())+1));           status.setCellValueFactory(new PropertyValueFactory<>("Status"));           dtd.setCellValueFactory(new PropertyValueFactory<>("Date"));           billnbr.setCellValueFactory(new PropertyValueFactory<>("BillNbr"));           cif.setCellValueFactory(new PropertyValueFactory<>("Cif"));           customer.setCellValueFactory(new PropertyValueFactory<>("Customer"));           mobile.setCellValueFactory(new PropertyValueFactory<>("Mobile"));           billvalue.setCellValueFactory(new PropertyValueFactory<>("BillValue"));       }     }

Please kindly someone help me.

Thanks

[Ifraz Imanudeen]

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;
public class OrderListModel {    private Integer Nbr;    private String Status;    private String Date;    private Integer BillNbr;    private Integer Cif;    private String Customer;    private Integer Mobile;    private Double BillValue;

OrderListController.java which has the related code to display data in TableView;
public class OrderListController implements Initializable {    @FXML private TableView<OrderListModel> OrderListTable;    @FXML private TableColumn<?, Integer> nbr;    @FXML private TableColumn<?, ?> status;    @FXML private TableColumn<?, ?> dtd;    @FXML private TableColumn<?, ?> billnbr;    @FXML private TableColumn<?, ?> cif;    @FXML private TableColumn<?, ?> customer;    @FXML private TableColumn<?, ?> mobile;    @FXML private TableColumn<?, ?> billvalue;              Connection connect;    private PreparedStatement pst = null;    private ResultSet rs = null;    private ObservableList<OrderListModel> oblist;        @Override    public void initialize(URL url, ResourceBundle rb) {      oblist = FXCollections.observableArrayList();      loadTableView();      getOrderListTotal();    }                      public void getOrderListTotal(){           try(Connection connect = SqliteConnection.Connector();)           {               pst = connect.prepareStatement("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");               rs = pst.executeQuery();               while(rs.next()){                   oblist.add(new OrderListModel(rs.getString("Status"),rs.getString("dtd"),rs.getInt("BillNo"),rs.getInt("CIF"),rs.getString("CustomerName"),rs.getInt("Mobile"),rs.getDouble("TRX.SUM(Price)")));               }           }           catch(SQLException ex)           {               Logger.getLogger(InvoiceContoller.class.getName()).log(Level.SEVERE, null, ex);           }           OrderListTable.setItems(oblist);       }       public void loadTableView(){           nbr.setCellValueFactory(nbr-> new ReadOnlyObjectWrapper<>(OrderListTable.getItems().indexOf(nbr.getValue())+1));           status.setCellValueFactory(new PropertyValueFactory<>("Status"));           dtd.setCellValueFactory(new PropertyValueFactory<>("Date"));           billnbr.setCellValueFactory(new PropertyValueFactory<>("BillNbr"));           cif.setCellValueFactory(new PropertyValueFactory<>("Cif"));           customer.setCellValueFactory(new PropertyValueFactory<>("Customer"));           mobile.setCellValueFactory(new PropertyValueFactory<>("Mobile"));           billvalue.setCellValueFactory(new PropertyValueFactory<>("BillValue"));       }     }

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)

[Knute Snortum]

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]

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.  

[Paul Clapham]

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]

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

"SELECT Bill.Status, Bill.dtd, Bill.BillNo, Bill.CIF, Bill.CustomerName, Bill.Mobile, SUM(TRX.Price) AS 'TOTALPRICE' FROM Bill JOIN TRX ON  Bill.BillNo = TRX.BillNo GROUP BY Bill.BillNo"

 oblist.add(new OrderListModel(rs.getString("Status"),rs.getString("dtd"),rs.getInt("BillNo"),rs.getInt("CIF"),rs.getString("CustomerName"),rs.getInt("Mobile"),rs.getDouble("TOTALPRICE")));

[Knute Snortum]

Good job figuring it out, and thanks for posting the solution back to the thread.