CODEWITHSUNDEEP
sqloracle-databaseoracle-apex
Dev
Dev

Reputation: 33

Incorporating DECODE() into SQL?

I'm stuck in a bit of a rut here. I want to incorporate a column for the city location using the DECODE() function to change the department number into the city name for a column named 'loc', kinda like this: decode(deptno,10,'NEW YORK') . But I'm not sure how to fit it in there without the code not running. Everything I've tried so far has been unsuccessful. Any help would be greatly appreciated, thank you.

Create view emp_view_updt as Select e.empno, e.ename, e.job, d.deptno, d.loc from emp e inner join dept d on e.deptno = d.deptno;

Upvotes: 0

Views: 62

Answers (2)

Renz Dominique
Renz Dominique

Reputation: 168

Create view emp_view_updt as 
    Select e.empno, e.ename, e.job, d.deptno, 
       DECODE (d.loc, 10, 'NEW YORK', 
                          2, 'San Francisco', 
                          3, 'New Jersey', 
                          4, 'Seattle',
                          'Non domestic') as loc 
    from emp e inner join 
         dept d 
         on e.deptno = d.deptno;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Use a case expression! decode() is not only highly specific to Oracle, but Oracle has supported case for decades.

So:

create view emp_view_updt as
    select e.empno, e.ename, e.job, d.deptno,
           (case when deptno = 10 then 'NEW YORK' end) as loc
    from emp e inner join
         dept d
         on e.deptno = d.deptno;

You can repeat when/then clauses for other values.

Upvotes: 3

Related Questions