-1

Im trying to use a simple case when statement in my SQL query, but it doesnt seem to work. Here's my query :

     SELECT location_id, rtb_user_id, start_time, end_time, tt_no, assn_by,
       assn_time, loc_id, loc_name, parent_loc_id, hr_level, userid,
       firstname, lastname, pwd, email, empid, locationid, departmentid,
       designationid, enabled, login_name, user_roles, cell_no,
       reporting_to, theme_name, cmp_emp_id, cmp_alias, cmp_mail,
       cmp_phone, v.cust_id, v.cust_mob, v.mac_addr, v.email_id,
       CASE WHEN v.PROJ_ID = '10070' THEN
       (   'pid='
       || v.proj_id
       || CHR (38)
       || 'issuetype='
       || v.issue_id
       || CHR (38)
       || 'customfield_10233='
       || v.cust_id
       || CHR (38)
       || 'customfield_10014='
       || v.mac_addr
       || CHR (38)
       || 'customfield_10016='
       || v.cust_mob
       || CHR (38)
       || 'reporter='
       || v.email_id) as jira_url
       else
       (   'pid=' || v.proj_id
       || CHR (38)|| 'issuetype='
       || v.issue_id|| CHR (38)
       || 'customfield_10004='  || v.cust_id
       || CHR (38)  || 'customfield_10014='
       || v.mac_addr  || CHR (38) || 'customfield_10016='
       || v.cust_mob|| CHR (38)
       || 'reporter='|| v.email_id) as jira_url
       END   
  FROM rtb_visit_assn v, rtb_locations l, rtb_users u
 WHERE v.location_id = l.loc_id
   AND v.rtb_user_id = u.userid
   AND l.loc_id = u.locationid);

it gives errors like "missing right paranthesis" or "missing keyword"

user3176971
  • 599
  • 2
  • 6
  • 16
  • 4
    You have 2 aliases for CASE `CASE WHEN THEN ELSE END` is a single thing – Mihai Dec 12 '14 at 10:27
  • 2
    ... as a complement of Mihai's comment, concretely, you have to move `as jira_url` right after the `end` of your case statement. Should close this question as a typo ? (extra parenthesis too at the end of the statement...) – Sylvain Leroux Dec 12 '14 at 10:31
  • WHAAAT?? such a stupid mistake. I should get my sleep. Thank you guys. Appreciate the help. – user3176971 Dec 12 '14 at 10:33

1 Answers1

0

You are not matching up your parenthesis, hence the error. Count the ( and then count the ) - they need to match.

MikeH
  • 115
  • 8