{"id":68,"date":"2008-04-22T11:11:55","date_gmt":"2008-04-22T15:11:55","guid":{"rendered":"http:\/\/www.dev-notes.com\/blog\/2008\/04\/22\/oracle-sql-case-expression\/"},"modified":"2008-04-22T11:11:55","modified_gmt":"2008-04-22T15:11:55","slug":"oracle-sql-case-expression","status":"publish","type":"post","link":"https:\/\/www.dev-notes.com\/blog\/2008\/04\/22\/oracle-sql-case-expression\/","title":{"rendered":"Oracle SQL case expression"},"content":{"rendered":"<p>The example below retrieves a sample list of employees, their job roles, and how many hours they have billed to a particular customer.<\/p>\n<pre class=\"code\">\nselect e.emp_name, e.job_role, sum(b.hours) from employees e, billing b where e.emp_id=b.emp_id and b.customer_id=126 group by e.emp_name, e.job_role;\n\nemp_name  job_role      sum(b.hours)\n--------- ------------- ------------\nSandy     attorney      10\nBob       programmer    4\nJill      programmer    8\nJohn      accountant    32\nBarbara   secretary     20\nDanny     proj manager  8\n<\/pre>\n<p>The company has a simple billing rule.  Attorney hours are billed at $200 per hour, programmers at $100 per hour, accountants at $100 per hour, and all others at $25 per hour.  We can use the case statement to help us derive how much to bill the customer.<\/p>\n<pre class=\"code\">\nselect e.emp_name, e.job_role, sum(b.hours),  \ncase when job_role='attorney' then sum(b.hours)*200\n  when job_role='programmer' then sum(b.hours)*100\n  when job_role='accountant' then sum(b.hours)*100\n  else charge=sum(b.hours)*25\nend as charge\nfrom employees e, billing b where e.emp_id=b.emp_id and b.customer_id=126 group by e.emp_name, e.job_role;\n\nemp_name  job_role      sum(b.hours)  charge\n--------- ------------- ------------- -------\nSandy     attorney      10            200\nBob       programmer    4             400\nJill      programmer    8             800\nJohn      accountant    32            3200\nBarbara   secretary     20            500\nDanny     proj manager  8             200\n<\/pre>\n<p>Of course, this is just a hands-on illustration of the case expression in Oracle SQL.  In real life, it is arguable that this type of business logic should probably be written elsewhere, perhaps with the base rates stored in a table, so that it can be easily modified if need be.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The case expression in Oracle is a powerful but yet simple features that allows something like an if statement in PL\/SQL.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20],"tags":[],"class_list":["post-68","post","type-post","status-publish","format-standard","hentry","category-oracle"],"_links":{"self":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/68","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/comments?post=68"}],"version-history":[{"count":0,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/68\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/media?parent=68"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/categories?post=68"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/tags?post=68"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}