如何在Oracle SQL的Select语句中重命名表列?

吉朗

在此处输入图片说明上面显示的是结果。我得到正确的数据,只需要更改列名。问题是我正在尝试重命名表中的列。我已经尝试过以下错误-java.sql.SQLSyntaxErrorException:ORA-00923:找不到期望的FROM关键字:

"SELECT to_char(logdate,'dd-MON-yy') AS DATE, adj_login_time AS LOGIN, adj_logout_time AS LOGOUT, adj_lunch_in AS LUNCHIN, adj_lunch_out AS LUNCHOUT, round(logduration,2) AS LOGDURATION " +
                        "FROM employee_time_log " +
                        "WHERE employee_id_number = " +userInputIdNumber +" AND ROWNUM <= 10" +
                        "ORDER BY logdate DESC");
AND
"SELECT to_char(logdate,'dd-MON-yy') AS 'Log Date', adj_login_time AS 'Log In Time', adj_logout_time AS 'Log Out Time', adj_lunch_in AS 'Lunch In', adj_lunch_out AS 'Lunch Out', round(logduration,2) AS 'Log Duration' " +
                        "FROM employee_time_log " +
                        "WHERE employee_id_number = " +userInputIdNumber +" AND ROWNUM <= 10" +
                        "ORDER BY logdate DESC");

我正在使用NetBeans IDE并在JAVA中进行编码。我尝试了上述其他几种方法,但都没有碰到运气。谢谢您的帮助!

private DefaultTableModel weeklyLogTableModel(ResultSet weeklyLogSet) throws SQLException
{
    ResultSetMetaData metaData = weeklyLogSet.getMetaData();

    Vector<String> columnNames = new Vector<String>();
    int columnCount = metaData.getColumnCount();
    for (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>();
        for (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 FROM EMPLOYEE_TIME_LOG for Weekly Log
            Statement weeklyLogstmt = dbConn.createStatement();
            ResultSet weeklyLogSet = weeklyLogstmt.executeQuery (
                    "SELECT to_char(logdate,'dd-MON-yy') AS 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 +" AND ROWNUM <= 10" +
                    "ORDER BY logdate DESC");

            jTable3.setModel((TableModel)weeklyLogTableModel(weeklyLogSet));
}
亚历克斯·普尔

除了@EvgeniyDorofeev正确指出的缺失空间外,在您的第一个查询中,您尝试将保留字 DATE用作列别名,这使解析器感到困惑。使用其他名称:

"SELECT to_char(logdate,'dd-MON-yy') AS ACTIVITY_DATE, adj_login_time AS LOGIN, adj_logout_time AS LOGOUT, adj_lunch_in AS LUNCHIN, adj_lunch_out AS LUNCHOUT, round(logduration,2) AS LOGDURATION " +
                        "FROM employee_time_log " +
                        "WHERE employee_id_number = " +userInputIdNumber +" AND ROWNUM <= 10 " +
                        "ORDER BY logdate DESC");

或者,如果您确实希望它具有该名称,则可以使用带引号的标识符(字符串中带有转义的双引号),尽管我确实建议不要这样做,因为它也会引起调用代码的混乱:

"SELECT to_char(logdate,'dd-MON-yy') AS \"DATE\", adj_login_time AS LOGIN, adj_logout_time AS LOGOUT, adj_lunch_in AS LUNCHIN, adj_lunch_out AS LUNCHOUT, round(logduration,2) AS LOGDURATION " +
                        "FROM employee_time_log " +
                        "WHERE employee_id_number = " +userInputIdNumber +" AND ROWNUM <= 10 " +
                        "ORDER BY logdate DESC");

在第二个版本中,您使用单引号试图将标识符引起来,但是这些标识符仅被视为字符串文字而不是标识符,并且在语法上此时无效。您必须对引用的标识符使用双引号:

"SELECT to_char(logdate,'dd-MON-yy') AS \"Log Date\", adj_login_time AS \"Log In Time\", adj_logout_time AS \"Log Out Time\", adj_lunch_in AS \"Lunch In\", adj_lunch_out AS \"Lunch Out\", round(logduration,2) AS \"Log Duration\" " +
                        "FROM employee_time_log " +
                        "WHERE employee_id_number = " +userInputIdNumber +" AND ROWNUM <= 10 " +
                        "ORDER BY logdate DESC");

您还在ROWNUM错误的位置进行检查;它将在之前应用ORDER BY,因此从任何一天开始您都会获得十个不确定的行,然后将这些行进行排序。如果您实际上想查看最近的十行,则需要在子查询中进行排序,然后将ROWNUM过滤器应用于该行:

"SELECT * FROM (" +
    "SELECT to_char(logdate,'dd-MON-yy') AS \"Log Date\", " +
        "adj_login_time AS \"Log In Time\", " +
        "adj_logout_time AS \"Log Out Time\", " +
        "adj_lunch_in AS \"Lunch In\", " +
        "adj_lunch_out AS \"Lunch Out\", " +
        "round(logduration,2) AS \"Log Duration\" " +
    "FROM employee_time_log " +
    "WHERE employee_id_number = " +userInputIdNumber + " " +
    "ORDER BY logdate DESC" +
    ") WHERE ROWNUM <= 10";

您还应该考虑使用参数化查询,而不是userInputIdNumber在字符串中嵌入,这部分是因为每个ID都需要对唯一查询进行硬解析,部分是避免SQL注入-尤其是当该值由用户提供时,看起来成为。

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章