Menu
Could you please help me with next oracle sql query.
SELECT "NEWS"."NEWSID" as ID,
"NEWS"."SLUG",
"NEWS_TRANSLATION".*,
(SELECT FILENAME FROM NEWS_MEDIA WHERE NEWSID = ID AND rownum <= 1 ORDER BY POSITION ASC) as FILENAME
FROM "NEWS"
INNER JOIN "NEWS_TRANSLATION" ON NEWS.NEWSID = NEWS_TRANSLATION.NEWSID
WHERE (NEWS.PUBLISH = 1) AND (NEWS_TRANSLATION.LANG = :lang)
ORDER BY "NEWS"."NEWSID" DESC;
When I execute this query I have error
ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"
*Cause:
*Action:
Error at Line: 4 Column: 74
415
In Oracle, a subquery is a query within a query. You can create subqueries within your SQL statements. These subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause.
A subquery (the inner SELECT statement, where one SELECT statement is nested within another) can return zero or more rows or expressions. Each subquery must be delimited by parentheses, and must contain a Projection clause and a FROM clause.
A subquery nested in the WHERE clause of the SELECT statement is called a nested subquery. A subquery can contain another subquery. Oracle allows you to have an unlimited number of subquery levels in the FROM clause of the top-level query and up to 255 subquery levels in the WHERE clause.
A subquery in the WHERE clause of a SELECT statement is also called a nested subquery. A subquery can contain another subquery. Oracle Database imposes no limit on the number of subquery levels in the FROM clause of the top-level query. You can nest up to 255 levels of subqueries in the WHERE clause.
Try this:
SELECT "NEWS"."NEWSID" as ID,
"NEWS"."SLUG",
"NEWS_TRANSLATION".*,
(SELECT * FROM (SELECT FILENAME FROM NEWS_MEDIA WHERE NEWSID = ID ORDER BY POSITION ASC) WHERE rownum = 1) as FILENAME
FROM "NEWS"
INNER JOIN "NEWS_TRANSLATION" ON NEWS.NEWSID = NEWS_TRANSLATION.NEWSID
WHERE (NEWS.PUBLISH = 1) AND (NEWS_TRANSLATION.LANG = :lang)
ORDER BY "NEWS"."NEWSID" DESC;
When are you are using "order by" and "rownum" together, you need to first order them and look for the first record.
157
Here is the working code:
SELECT "NEWS"."NEWSID" as ID,
"NEWS"."SLUG",
"NEWS_TRANSLATION".*,
(SELECT FILENAME FROM NEWS_MEDIA WHERE NEWSID = ID AND rownum <= 1 ) as FILENAME
FROM "NEWS"
INNER JOIN "NEWS_TRANSLATION" ON NEWS.NEWSID = NEWS_TRANSLATION.NEWSID
WHERE (NEWS.PUBLISH = 1) AND (NEWS_TRANSLATION.LANG = :lang)
ORDER BY "NEWS"."NEWSID" DESC
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
