Home > Net >  Oracle SQL: dummy variable from 2 data sets
Oracle SQL: dummy variable from 2 data sets

Time:05-07

I have two data tables. They both have an employee id column and a sales quarter column, in addition to lots of other columns. The first, which ill call "Roster", has one row per employee per sales quarter. The second has multiple rows per employee per quarter, with each row being a type of sale made, a column called "salestype" and this second table called "QuarterlySales". Basically, I need to return a number of columns from the first table, but I also need a dummy variable indicating if the employee made a sales type of "cold call".

I tried an if statement but didnt work.

select employeeid, quarter, variablea, variableb, ....., 
if saletype = 'cold call' then 1 else 0 end as ColdCall
from Roster r
left join quarterly sales q
on r.employeeid = q.employeeid
and r.quarter = q.quarter
where blah blah blah conditions;

Im sure my approach is wrong. I welcome any help.

CodePudding user response:

If clauses are not possible in this way, you need a case when instead:

...CASE WHEN saletype = 'cold call' THEN 1 ELSE 0 END AS ColdCall...
  • Related