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/ORA-00933 SQL Command Not Properly Ended
The WHERE
clause must be before the GROUP BY
.
See oracle documentation about SELECT
SELECT COUNT(*), action_date
FROM summary
WHERE num_actions = 500
GROUP BY action_date
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"""
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
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
Related Topics
Regular Expression to Match Common SQL Syntax
Postgresql Order by Issue - Natural Sort
Delete Parent If It's Not Referenced by Any Other Child
SQL Update Order of Evaluation
What Is the Equivalent Postgresql Syntax to Oracle's Connect by ... Start With
Oracle: Combine Multiple Results in a Subquery into a Single Comma-Separated Value
How to Make a Parametrized SQL Query on Classic Asp
Selecting the Second Row of a Table Using Rownum
How to Select Even Records from a Table in Oracle
Hive Query Performance for High Cardinality Field
Does the Jdbc Spec Prevent '' from Being Used as an Operator (Outside of Quotes)
Select a Random Sample of Results from a Query Result
Create a SQL Query to Retrieve Most Recent Records
Move SQL Data from One Table to Another
Mysql: Group_Concat with Left Join
How to Efficiently Delete Rows While Not Using Truncate Table in a 500,000+ Rows Table
Find All Records Which Have a Count of an Association Greater Than Zero