Error in the "FROM CLAUSE"

Question

I have this query below. I've checked it in ms access and it works fine. But when I add it to my program, A "Syntax error in FROM clause." appears. Any suggestions on how to resolve this?

String query ="TRANSFORM COUNT(a.present)\n" +
                            "SELECT e.ID,e.firstName,e.lastName,e.position,e.rate\n" +
                            "FROM employees e \n" +
                            "INNER JOIN attendance a \n" +
                            "ON e.ID = a.empID \n" +
                            "GROUP BY e.ID,e.firstName,e.lastName,e.position,e.rate \n" +
                            "PIVOT a.dateAttended";

this is the full method where the query belongs. The connectToDB method is used to insert the result set to a jtable. Are jtables able to insert pivot queries?

private void attendanceView() throws ClassNotFoundException{
        try{
            String query ="TRANSFORM COUNT(a.present) " +
                          "SELECT e.ID,e.firstName,e.lastName,e.position,e.rate " +
                          "FROM employees e " +
                          "INNER JOIN attendance a " +
                          "ON e.ID = a.empID " +
                          "GROUP BY e.ID,e.firstName,e.lastName,e.position,e.rate " +
                          "PIVOT a.dateAttended";
            Object[][] result = connectToDB(query);
            System.out.print(result);
            monthlyAttendanceTable.setModel(new javax.swing.table.DefaultTableModel(
                    result, new String [] {"Employee ID","First Name","Last Name", "Position", "Rate","",""}
            )
            {
                Class[] types = new Class [] {
                    java.lang.Integer.class, java.lang.String.class, java.lang.String.class, java.lang.String.class, java.lang.Integer.class, java.lang.Integer.class, java.lang.Integer.class, java.lang.Integer.class,java.lang.Integer.class,java.lang.Integer.class,java.lang.String.class
                };
                boolean[] canEdit = new boolean [] {
                    false, false, false, false, false, false, false, false, false,false
                };

                public Class getColumnClass(int columnIndex) {
                    return types [columnIndex];
                }

                public boolean isCellEditable(int rowIndex, int columnIndex) {

                    return canEdit [columnIndex];
                }
            });
        }catch (ClassNotFoundException ex) {
                Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
                ex.printStackTrace();
            } 
    }

I added a new line in my query string for easy readability of my code. I'll try your suggestion. — Rocky, May 09 '14 at 15:59
I don't know ms-access, but maybe it requires windows newlines (`\r\n`)? Java treats `\n` as the LF char exclusively; it never expands it to the OS's concept of a newline. — yshavit, May 09 '14 at 16:00
I've tried this, but the same error occurs. String query ="TRANSFORM COUNT(a.present) SELECT e.ID,e.firstName,e.lastName,e.position,e.rate FROM employees e INNER JOIN attendance a ON e.ID = a.empID GROUP BY e.ID,e.firstName,e.lastName,e.position,e.rate PIVOT a.dateAttended"; — Rocky, May 09 '14 at 16:02
I use newline characters as well in my java-embedded SQL. I think it's great for debugging. — wvdz, May 09 '14 at 16:02
@Sireesh java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause. — Rocky, May 09 '14 at 16:05
It is better to provide the screenshots for its resolution. This info is not good enough. — Sireesh Yarlagadda, May 09 '14 at 16:10
@Sireesh I updated my question and added the whole method. PLease take a look at it. — Rocky, May 09 '14 at 16:17

score -1 · Answer 1 · answered May 09 '14 at 16:02

-1

Try this:

Spaces matters alot while spilting the same query into multiple lines.

String query ="TRANSFORM COUNT(a.present) " +
                            "SELECT e.ID,e.firstName,e.lastName,e.position,e.rate " +
                            "FROM employees e " +
                            "INNER JOIN attendance a " +
                            "ON e.ID = a.empID " +
                            "GROUP BY e.ID,e.firstName,e.lastName,e.position,e.rate " +
                            "PIVOT a.dateAttended";

answered May 09 '14 at 16:02

Sireesh Yarlagadda

12,978
3
74
76

More details are needed for other users to help you. Also, you might consider wrapping up your previous questions and awarding answers (checkbox on left). [link]http://stackoverflow.com/questions/23568914/error-in-the-from-clause [/link] [link]http://stackoverflow.com/questions/23552345/checking-if-a-jbutton-is-clicked-in-another-java-file[/link] – SeraM May 09 '14 at 16:05

Error in the "FROM CLAUSE"

1 Answers1