Missing Keyword Error In Case Statement
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the ora 00905 missing keyword case statement in where clause workings and policies of this site About Us Learn more about
Ora-00905 Missing Keyword Create Table
Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions ora-00905 missing keyword in oracle Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. ora-00905 missing keyword join Join them; it only takes a minute: Sign up SQL Case statement throwing missing keyword error up vote 2 down vote favorite 1 I'm trying this query: Select * from users_t t where case when sysdate <= to_date('20130131', 'yyyymmdd') then t.user_id=1254664 else t.user_id=1259753 End Why is it giving out "ORA-00905: missing keyword" error? sql oracle oracle10g oracle11g share|improve this
Ora-00905 Missing Keyword Explain Plan
question edited Apr 17 '13 at 18:29 Kara 3,16073147 asked Apr 17 '13 at 18:21 roshanK 1442616 add a comment| 4 Answers 4 active oldest votes up vote 3 down vote accepted You need a comparison operator outside the case statement: Select * from users_t t where (case when sysdate <= to_date('20130131', 'yyyymmdd') then 254664 else 1259753 End) = t.user_id However, you can write this without the case statement: select * from users_t t where ((sysdate <= to_date('20130131', 'yyyymmdd') and t.user_id = 254664) or ((sysdate > to_date('20130131', 'yyyymmdd') and t.user_id = 1259753) share|improve this answer edited Apr 17 '13 at 18:32 answered Apr 17 '13 at 18:24 Gordon Linoff 468k20141214 add a comment| up vote 3 down vote Your case statement is not correct. SELECT * FROM users_t t WHERE t.user_id = CASE WHEN SYSDATE <= TO_DATE('20130131', 'yyyymmdd') THEN 1254664 ELSE 1259753 END This will accomplish your task. Edit: Better formatting. share|improve this answer edited Apr 17 '13 at 18:43 answered Apr 17 '13 at 18:24 gustavodidomenico 2,81711346 thanks..it works.. –roshanK Apr 17
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company ora-00905 missing keyword select into Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs sql error ora-00905 missing keyword alter table Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers,
Ora-00905 Missing Keyword In Select Into Statement
just like you, helping each other. Join them; it only takes a minute: Sign up ORA-00905: missing keyword error in case after where clause up vote 0 down vote favorite I have the following query which is http://stackoverflow.com/questions/16067331/sql-case-statement-throwing-missing-keyword-error giving error ORA-00905: missing keyword. I've not been able to find the syntax despite continuous efforts for last few hours. Please help. SELECT a.DOCUMENT_CATEGORY, a.template_id, a.category_id, a.REVIEW_CATEGORY, a.WITH_BIDS, a.WITH_FINAL_DOCUMENTS, b.divn_id, b.deptt_id, a.vdr_id, C.DEPARTMENT, a.TEMPLATE_TITLE FROM DCTM_VDR_REF_DTLS a, DCTM_VDR_REF_MASTER b, VW_DIVN_DIR c WHERE b.DIVN_ID = c.DIVN_CODE AND b.DEPTT_ID = c.SECTN_CODE AND a.vdr_id = b.vdr_id AND (b.REFERENCE_NUMBER, b.APPROVED_ON) IN ( SELECT MAX (REFERENCE_NUMBER), MAX (APPROVED_ON) FROM DCTM_VDR_REF_MASTER WHERE REFERENCE_NUMBER = (SELECT DISTINCT NVL (TRIM (MR_NUMBER), TRIM (TENDER_NO)) http://stackoverflow.com/questions/19854541/ora-00905-missing-keyword-error-in-case-after-where-clause FROM EILEDMS.EIL_DOCUMENT_SV@EDMS_DBLINK WHERE object_name = 'A307-0IC-JA-MR-7960-1030-157-FOA' AND r_object_type = 'eil_foa_order_pr_doc' AND ( title = 'FOA' OR title = 'DRAFT FOA')) AND APPROVED_ON IS NOT NULL GROUP BY DIVN_ID, DEPTT_ID) AND REVIEW_CATEGORY <> 'Delete Category' AND (CASE (SELECT IS_SCHEDULE_LOCKED FROM DCTM_VENDOR_SCHEDULE WHERE SCH_ID = 359) WHEN 0 THEN 1 WHEN 1 THEN (a.template_id || '-' || a.category_id) IN (SELECT template_id || '-' || category_id FROM DCTM_VENDOR_SCH_UNLOCK_DTLS WHERE APPROVAL = 'Y' AND APPROVAL_UPTO >= SYSDATE AND CONSUMED = 0 AND sch_ID = 359) END) = 1 ORDER BY c.DEPARTMENT ASC, a.TEMPLATE_ID, a.SORT_ORDER, a.DOCUMENT_CATEGORY ASC Can't we use IN clause inside a THEN statement? sql oracle case where-clause toad share|improve this question edited Nov 8 '13 at 10:44 asked Nov 8 '13 at 8:26 6nagi9 3052519 1 The last WHEN 1 THEN statement in the CASE doesn't makes sense at all and is the cause of the problem. –tvm Nov 8 '13 at 9:06 Can't we include an IN clause inside a THEN statment? –6nagi9 Nov 8 '13 at 9:20 1 To elaborate on @tvm, you have a clause which is basically WHEN value THEN boolean which isn't allowed in Oracle. You'll need to turn the boolean into a SQL-supported datatype, and probably the value 0 or 1 in this particular case. –Colin 't Hart Nov 8 '13 at 9
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers http://stackoverflow.com/questions/29190285/ora-00905-missing-keyword-for-case-statement or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up “ORA-00905: missing keyword” for case statement up vote -1 down vote favorite DECLARE var_employee_number VARCHAR2(30); var_employee_name VARCHAR2(30); var_salary VARCHAR2(30); BEGIN SELECT employee_number,employee_name, CASE var_salary WHEN salary <= 10000 THEN salary:= salary* .02 /*I am missing keyword getting an error over here*/ WHEN salary BETWEEN 10000 AND 15000 THEN salary := salary * .02 WHEN salary BETWEEN 15000 AND 20000 THEN salary := salary * .025 END AS salary INTO var_employee_number,var_employee_name,var_salary FROM EMPLOYEES WHERE employee_number := var_employee_number; dbms_output.put_line('The salary is '||var_salary); END; I am trying to write a case statement when if your salary is in a particular range, the salary should be multiplied by either 20% or 25% depending on the salary range. oracle plsql case share|improve 00905 missing keyword this question asked Mar 22 '15 at 2:26 user3298508 83 Why are you putting a number into a VARCHAR2 variable? And where does the value of var_employee_number come from? –David Faber Mar 22 '15 at 2:37 add a comment| 1 Answer 1 active oldest votes up vote 2 down vote accepted Your CASE statement has a couple of problems: CASE var_salary -- you don't need var_salary here WHEN salary <= 10000 THEN salary:= salary* .02 /*I am getting an error over here*/ -- you don't need "salary:=" here WHEN salary BETWEEN 10000 AND 15000 THEN salary := salary * .02 WHEN salary BETWEEN 15000 AND 20000 THEN salary := salary * .025 END AS salary So correcting for these issues it should read just: CASE WHEN salary <= 10000 THEN salary * 0.2 WHEN salary BETWEEN 10000 AND 15000 THEN salary * 0.2 WHEN SALARY BETWEEN 15000 AND 20000 THEN salary * 0.25 END AS salary You might also note that the cases for <= 10000 and BETWEEN 10000 AND 15000 are the same, and so could be combined. Also keep in mind that BETWEEN ... AND is inclusive, so a value of 15000 will return 15000 * 0.2 (3000), rather than * 0.25. Also, please note that I corrected your numbers ... 20% is 0.2, not .02. share|improve this answer answered Mar 22 '15 at 2:34 David Faber 7,84411229 Thanks for the help!! &nda