Choosing Oracle with a Subquery

Question

Could you help me with the following 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 request, I have an error

 ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis" *Cause: *Action: Error at Line: 4 Column: 74 

Answer 1

Try the following:

 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 you use "order by" and "rownum" together, you need to order them first and find the first entry.

Answer 2

Try removing the order by clause in the subquery.

Answer 3

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 

Answer 4

Try the following:

 SELECT "NEWS"."NEWSID" AS ID, "NEWS"."SLUG", "NEWS_TRANSLATION".*, (SELECT FILENAME FROM NEWS_MEDIA WHERE NEWSID = "NEWS"."NEWSID" 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 

Answer 5

 SELECT STUDENT_NAME, STUDENT_ALIAS_NAME FROM school WHERE STUDENT_NAME IN ( SELECT STUDENT_NAME FROM school WHERE STUDENT_DEPARMENT= 0 );