shown above outcome. right data, need change column names. problem trying rename columns in table. have tried following error - java.sql.sqlsyntaxerrorexception: ora-00923: keyword not found expected:
"select to_char(logdate,'dd-mon-yy') date, adj_login_time login, adj_logout_time logout, adj_lunch_in lunchin, adj_lunch_out lunchout, round(logduration,2) logduration " + "from employee_time_log " + "where employee_id_number = " +userinputidnumber +" , rownum <= 10" + "order logdate desc"); , "select to_char(logdate,'dd-mon-yy') 'log date', adj_login_time 'log in time', adj_logout_time 'log out time', adj_lunch_in 'lunch in', adj_lunch_out 'lunch out', round(logduration,2) 'log duration' " + "from employee_time_log " + "where employee_id_number = " +userinputidnumber +" , rownum <= 10" + "order logdate desc"); i using netbeans ide , coding in java. have tried several other variations of above no luck. help!
private defaulttablemodel weeklylogtablemodel(resultset weeklylogset) throws sqlexception { resultsetmetadata metadata = weeklylogset.getmetadata(); vector<string> columnnames = new vector<string>(); int columncount = metadata.getcolumncount(); (int column = 1; column <= columncount; column++) { columnnames.add(metadata.getcolumnname(column)); } vector<vector<object>> data = new vector<vector<object>>(); while (weeklylogset.next()) { vector<object> vector = new vector<object>(); (int columnindex = 1; columnindex <= columncount; columnindex++) { vector.add(weeklylogset.getobject(columnindex)); } data.add(vector); } return new defaulttablemodel(data, columnnames); } private void userdashboard(int userinputidnumber) { jpanel1.setvisible(false); jpanel2.setvisible(true); jpanel3.setvisible(false); jpanel4.setvisible(false); try { //getting information employee_time_log weekly log statement weeklylogstmt = dbconn.createstatement(); resultset weeklylogset = weeklylogstmt.executequery ( "select to_char(logdate,'dd-mon-yy') date, adj_login_time, adj_logout_time, adj_lunch_in, adj_lunch_out, round(logduration,2) " + "from employee_time_log " + "where employee_id_number = " +userinputidnumber +" , rownum <= 10" + "order logdate desc"); jtable3.setmodel((tablemodel)weeklylogtablemodel(weeklylogset)); }
aside missing space @evgeniydorofeev correctly pointed out, in first query you're trying using reserved word date column alias, , confusing parser. use different name:
"select to_char(logdate,'dd-mon-yy') activity_date, adj_login_time login, adj_logout_time logout, adj_lunch_in lunchin, adj_lunch_out lunchout, round(logduration,2) logduration " + "from employee_time_log " + "where employee_id_number = " +userinputidnumber +" , rownum <= 10 " + "order logdate desc"); or if want have name use quoted identifier (with escaped double quotes within string), though i'd recommend against can cause confusion calling code too:
"select to_char(logdate,'dd-mon-yy') \"date\", adj_login_time login, adj_logout_time logout, adj_lunch_in lunchin, adj_lunch_out lunchout, round(logduration,2) logduration " + "from employee_time_log " + "where employee_id_number = " +userinputidnumber +" , rownum <= 10 " + "order logdate desc"); in second version you're using single quotes attempt make identifiers quoted, seen string literals rather identifiers, , not valid @ point in syntax. have use double quotes quoted identifiers:
"select to_char(logdate,'dd-mon-yy') \"log date\", adj_login_time \"log in time\", adj_logout_time \"log out time\", adj_lunch_in \"lunch in\", adj_lunch_out \"lunch out\", round(logduration,2) \"log duration\" " + "from employee_time_log " + "where employee_id_number = " +userinputidnumber +" , rownum <= 10 " + "order logdate desc"); you're doing rownum check in wrong place; applied before order by, you'll ten indeterminate rows day, ordered. if want see ten recent rows you'll need ordering in subquery , apply rownum filter that:
"select * (" + "select to_char(logdate,'dd-mon-yy') \"log date\", " + "adj_login_time \"log in time\", " + "adj_logout_time \"log out time\", " + "adj_lunch_in \"lunch in\", " + "adj_lunch_out \"lunch out\", " + "round(logduration,2) \"log duration\" " + "from employee_time_log " + "where employee_id_number = " +userinputidnumber + " " + "order logdate desc" + ") rownum <= 10"; you should consider using parameterised queries rather embedding userinputidnumber in string, partly because each id require hard parse of unique query, , partly avoid sql injection - if value being supplied user, seems be.
Comments
Post a Comment