error in sql statement using if statement

can anyone one help me with this statement.

select if tdy.classyear = 1 then

begin
select td.firstallowance, td.degreecode, tdy.classyear, tdy.yearcode
from tei_degree td left join tei_degreeyears tdy on td.degree_id = tdy.degree_id
where tdy.classyear = 1
end
else
begin
select td.otherallowance, td.degreecode, tdy.classyear, tdy.yearcode
from tei_degree td left join tei_degreeyears tdy on td.degree_id = tdy.degree_id
where tdy.classyear <> 1
end

from tei_degree td left join tei_degreeyears tdy on td.degree_id = tdy.degree_id

im quite new to sql and cant figure out the problem

can anyone help
colin

Colin:

See if this works better:

if ( select tdy.classyear
from tei_degree td
left join tei_degreeyears tdy
on td.degree_id = tdy.degree_id
) = 1 then

begin
select td.firstallowance, td.degreecode, tdy.classyear, tdy.yearcode
from tei_degree td left join tei_degreeyears tdy on td.degree_id = tdy.degree_id
where tdy.classyear = 1
end
else
begin
select td.otherallowance, td.degreecode, tdy.classyear, tdy.yearcode
from tei_degree td left join tei_degreeyears tdy on td.degree_id = tdy.degree_id
where tdy.classyear <> 1
end

|||I'm still getting an error with that statement.

the error says incorrect syntax near then and else statement.

i've check all the tables names and field names and there fine.

anyo other suggestions
|||

Remove the word 'then' and you should be fine.

Chris

|||cheers that worked
|||

hi!

You cannot use if inside a select statement.... Use CASE and it will work for sure....

select CASE tdy.classyear

WHEN tdy.classyear = 1 then

begin
select td.firstallowance, td.degreecode, tdy.classyear, tdy.yearcode
from tei_degree td left join tei_degreeyears tdy on td.degree_id = tdy.degree_id
where tdy.classyear = 1
end

ELSE
begin
select td.otherallowance, td.degreecode, tdy.classyear, tdy.yearcode
from tei_degree td left join tei_degreeyears tdy on td.degree_id = tdy.degree_id
where tdy.classyear <> 1
end
END
from tei_degree td left join tei_degreeyears tdy on td.degree_id = tdy.degree_id

try this and let me know ur comments...

Thanks

sql