Hi guyz.. IF Then

Can anyone tell me wat is the error in this code
SELECT dept_master.dept_name,
dept_master.dept_cd,
If emp_master.leave_stat = 'L' AND emp_master.emp_stat = 'A'
Then Count(emp_master.emp_code) EndIf as leave,
If emp_master.leave_stat = 'N' AND emp_master.emp_stat = 'A'
Then Count(emp_master.emp_code) EndIf as working,
If emp_master.emp_stat = 'T' Then Count(emp_master.emp_code)
EndIf as term
FROM dept_master,
emp_master,
system_parametersHi
You can't use if within a select statement. Checkout the Case function in
Books Online. It is best to post DDL and example data, then you will get a
meaningful example see http://www.aspfaq.com/etiquette.asp?id=5006
You have no join conditions either so assuming you have a dept_code
SELECT d.dept_name,
d.dept_cd,
SUM(CASE WHEN e.leave_stat = 'L' AND e.emp_stat = 'A'
Then 1 ELSE 0 End) as leave,
SUM(CASE WHEN e.leave_stat = 'N' AND e.emp_stat = 'A'
Then 1 ELSE 0 End) as working,
SUM(CASE WHEN e.emp_stat = 'T' Then 1 ELSE 0 End) as term
FROM dept_master d
JOIN emp_master e ON e.dept_code = e.dept_code
GROUP BY d.dept_name, d.dept_cd
John
"pal" wrote:
> Can anyone tell me wat is the error in this code
> SELECT dept_master.dept_name,
> dept_master.dept_cd,
> If emp_master.leave_stat = 'L' AND emp_master.emp_stat = 'A'
> Then Count(emp_master.emp_code) EndIf as leave,
> If emp_master.leave_stat = 'N' AND emp_master.emp_stat = 'A'
> Then Count(emp_master.emp_code) EndIf as working,
> If emp_master.emp_stat = 'T' Then Count(emp_master.emp_code)
> EndIf as term
> FROM dept_master,
> emp_master,
> system_parameters
>|||pal
Take a look at CASE expression in the BOL
"pal" <jayadevpaleri@.gmail.com> wrote in message
news:1189493775.303896.266250@.22g2000hsm.googlegroups.com...
> Can anyone tell me wat is the error in this code
> SELECT dept_master.dept_name,
> dept_master.dept_cd,
> If emp_master.leave_stat = 'L' AND emp_master.emp_stat = 'A'
> Then Count(emp_master.emp_code) EndIf as leave,
> If emp_master.leave_stat = 'N' AND emp_master.emp_stat = 'A'
> Then Count(emp_master.emp_code) EndIf as working,
> If emp_master.emp_stat = 'T' Then Count(emp_master.emp_code)
> EndIf as term
> FROM dept_master,
> emp_master,
> system_parameters
>sql