Reputation: 971
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
Upvotes: 7
Views: 24635
Reputation: 551
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.
Upvotes: 10
Reputation: 1
SELECT STUDENT_NAME, STUDENT_ALIAS_NAME
FROM school
WHERE STUDENT_NAME IN (
SELECT STUDENT_NAME
FROM school
WHERE STUDENT_DEPARMENT= 0
);
Upvotes: -2
Reputation: 1764
Try This:
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
Upvotes: 1
Reputation: 5416
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
Upvotes: 2