Using an If Else statement within select distinct

[Josh01]

Hi,

I am writing a query that requires the use of an if else statment. I have written the statement and tested it but i can not make it work as part of a larger query. Does anyone know how i could do this?

Cheers

Josh

 

[bperry]

Josh,
Can you post us an example of what works, and what doesn't work, so that we can see what it is you are dealing with?
tia
brian

 

[Josh01]

sure no problem

The if statment that works in isolation is

--check to see if the person has a leaving date
if (select max(c.working_to) from v_ssd_resource c,
m_ssd_resource_Cost_centre_history a
where a.consultant = c.consultant) is null
begin
--if not then select the period(monthno) where the month from the changedate = the actualmonthno
(select d.monthno from m_ssd_periods d, m_ssd_resource_Cost_centre_history a
where datepart(mm,a.changedate) = d.actualmonthno)
end

else
--if there is leaving date then select the period (monthno) where the month from the
--working_to date = the actualmonthno

begin
(select d.monthno from m_ssd_periods d, v_ssd_resource c
where datepart(mm,c.working_to) = d.actualmonthno)
end


cheers

 

[arrowhouse]

Can you show use the code that doesn't work?

Thanks.

 

[Josh01]

Thanks for all your help folks, i managed to figure it using a CASE statment


Cheers

Josh