SQL Error: Ora-00933: SQL Command Not Properly Ended

SQL Error: ORA-00933: SQL command not properly ended

Oracle does not allow joining tables in an UPDATE statement. You need to rewrite your statement with a co-related sub-select

Something like this:

UPDATE system_info
SET field_value = 'NewValue' 
WHERE field_desc IN (SELECT role_type 
                     FROM system_users 
                     WHERE user_name = 'uname')

For a complete description on the (valid) syntax of the UPDATE statement, please read the manual:

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10008.htm#i2067715

SQL command not properly ended with WHERE statement

You probably want this:

select * from (
    SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY)
) WHERE rownum = 1;

You have a syntax error in your query, because the ORDER BY comes after the WHERE clause. So, you'll have this after fixing the syntax error:

SELECT CITY, LENGTH(CITY) 
FROM STATION
WHERE ROWNUM = 1
ORDER BY LENGTH(CITY)

If you recall, the WHERE clause is evaluated before the ORDER BY clause.
Since, ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on. the ROWNUM return values 1,2,3,... based on the order in which data from table station is fetched (could be in any order), which is probably not what you want.

If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, then you can force the ROWNUM condition to be applied after the ordering of the rows.

So, you query becomes this:

select * from (
    SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY)
) WHERE rownum = 1;

In this, the rows are first sorted based on the length of content of CITY column inside the subquery, and then applies ROWNUM in the increasing order of the length of CITY column. So, now you can filter out the first row using ROWNUM = 1 predicate.

Alternatively, in Oracle database 12c and above, you can use the FETCH FIRST N ROWS ONLY clause to make your life easier (no subquery required).

SELECT CITY, LENGTH(CITY) 
FROM STATION
ORDER BY LENGTH(CITY)
FETCH FIRST 1 ROW ONLY;

This clause will be evaluated after the ordering is done and returns the top one row.

SQL Error: ORA-00933: SQL command not properly ended. When using a simple insert

Remove semi-colon, here:

string command = "insert into SOMETABLE (GROUPID, USERID, REMOVED) values 
('00000000000000000000000000000000', '00000000000000000000000000000000', 0);";
                                                                           ^
                                                                           |
                                                                        here

SQL error - ORA-00933: SQL command not properly ended

You could use:

select r.room_type_id as RoomType       -- rt alias changed to r to match group by
      ,count(r.no_of_reservations) as NoOfReservations
from Room_type rt                        -- explicit join
join Room r
  on rt.room_type_id = r.room_type_id
join Reservation res
  on r.room_id = res.room_id
where extract(month from res.check_in_date) = 3
  and extract(month from res.check_out_date) = 3
group by r.room_type_id       --swapped lines group by <=> order by
order by NoOfReservations;    --alias from SELECT

How to fix ORA-00933: SQL command not properly ended using sqoop

You guys are on right track but the statement need a change. Need to put an AND before $CONDITIONS. sqoop uses this to validate the sql against metadata.
You can use this sqoop-

sqoop import --connect jdbc:oracle:thin:@connection --query "SELECT * FROM ACTB_HISTORY WHERE trn_dt>='01-09-2021' and trn_dt<='01-08-2022' AND $CONDITIONS"; --username stg_uat -P  --target-dir  \user\hive\warehouse--hive-import --hive-database dwhpd --create-hive-table --hive-table actb_hist -m 1

cx_Oracle ORA-00933 SQL command not properly ended

print (statmentApp)

gives:

SELECT TITLE,APP_URL from SOME.DATABASEWHERE UPPER(APP_URL) LIKE UPPER(:tc)AND ACTIVE_FLAG = :y

showing that the string concatenation worked too well. To retain necessary whitespace you'll find it easier to use triple quotes:

statmentApp = """SELECT TITLE,APP_URL from SOME.DATABASE
                 WHERE UPPER(APP_URL) LIKE UPPER(:tc)
                 AND ACTIVE_FLAG = :y"""

---

Related Topics