IF inside of CASE

Can anyone help me with condition inside CASE. Below is my code and I get
syntax error at IF line. Thanks.
SELECT TOP 100 PERCENT
dbo.WorkerDeductions.EmployeeNumber,
dbo.WorkerDeductions.DedCode,
dbo.WorkerDeductions.DedAmt,
dbo.WorkerDeductions.DeductionBalance,
dbo.WorkerDeductions.DedPercent,
dbo.PayInfoNHS.EarnGross,
dbo.PayInfoNHS.CheckID,
DedCalc = CASE
WHEN dbo.WorkerDeductions.DeductionBalance > 0 THEN
IF dbo.WorkerDeductions.DeductionBalance > dbo.WorkerDeductions.DedAmt
BEGIN
dbo.WorkerDeductions.DedAmt
END
ELSE dbo.WorkerDeductions.DeductionBalance
WHEN dbo.WorkerDeductions.DedPercent > 0 THEN
ROUND(dbo.WorkerDeductions.DedPercent * dbo.PayInfoNHS.EarnGross, 2)
ELSE dbo.WorkerDeductions.DedAmt
END
FROM dbo.WorkerDeductions INNER JOIN
dbo.PayInfoNHS ON dbo.WorkerDeductions.EmployeeNumber =
dbo.PayInfoNHS.EmployeeNumber INNER JOIN
dbo.DeductionCodeLookup ON dbo.WorkerDeductions.DedCode =
dbo.DeductionCodeLookup.DedCode
WHERE (dbo.PayInfoNHS.CheckDate = CONVERT(DATETIME, '2005-03-18 00:00:00',
102))
ORDER BY dbo.WorkerDeductions.EmployeeNumberYou can't use IF in a query; IF is for flow, not statement-level control.
Try a nested CASE:
CASE
WHEN ... THEN
CASE WHEN ... THEN
ELSE ...
END
WHEN ... THEN
CASE WHEN ... THEN
ELSE ...
END
ELSE
..
END
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:ODfTYAyLFHA.2384@.tk2msftngp13.phx.gbl...
> Can anyone help me with condition inside CASE. Below is my code and I get
> syntax error at IF line. Thanks.
> SELECT TOP 100 PERCENT
> dbo.WorkerDeductions.EmployeeNumber,
> dbo.WorkerDeductions.DedCode,
> dbo.WorkerDeductions.DedAmt,
> dbo.WorkerDeductions.DeductionBalance,
> dbo.WorkerDeductions.DedPercent,
> dbo.PayInfoNHS.EarnGross,
> dbo.PayInfoNHS.CheckID,
> DedCalc = CASE
> WHEN dbo.WorkerDeductions.DeductionBalance > 0 THEN
> IF dbo.WorkerDeductions.DeductionBalance > dbo.WorkerDeductions.DedAmt
> BEGIN
> dbo.WorkerDeductions.DedAmt
> END
> ELSE dbo.WorkerDeductions.DeductionBalance
> WHEN dbo.WorkerDeductions.DedPercent > 0 THEN
> ROUND(dbo.WorkerDeductions.DedPercent * dbo.PayInfoNHS.EarnGross, 2)
> ELSE dbo.WorkerDeductions.DedAmt
> END
> FROM dbo.WorkerDeductions INNER JOIN
> dbo.PayInfoNHS ON dbo.WorkerDeductions.EmployeeNumber =
> dbo.PayInfoNHS.EmployeeNumber INNER JOIN
> dbo.DeductionCodeLookup ON dbo.WorkerDeductions.DedCode =
> dbo.DeductionCodeLookup.DedCode
> WHERE (dbo.PayInfoNHS.CheckDate = CONVERT(DATETIME, '2005-03-18 00:00:00',
> 102))
> ORDER BY dbo.WorkerDeductions.EmployeeNumber
>|||David C,
You can nest a CASE expression inside another, but you can not use IF inside
a CASE.
AMB
"David C" wrote:

> Can anyone help me with condition inside CASE. Below is my code and I get
> syntax error at IF line. Thanks.
> SELECT TOP 100 PERCENT
> dbo.WorkerDeductions.EmployeeNumber,
> dbo.WorkerDeductions.DedCode,
> dbo.WorkerDeductions.DedAmt,
> dbo.WorkerDeductions.DeductionBalance,
> dbo.WorkerDeductions.DedPercent,
> dbo.PayInfoNHS.EarnGross,
> dbo.PayInfoNHS.CheckID,
> DedCalc = CASE
> WHEN dbo.WorkerDeductions.DeductionBalance > 0 THEN
> IF dbo.WorkerDeductions.DeductionBalance > dbo.WorkerDeductions.DedAmt
> BEGIN
> dbo.WorkerDeductions.DedAmt
> END
> ELSE dbo.WorkerDeductions.DeductionBalance
> WHEN dbo.WorkerDeductions.DedPercent > 0 THEN
> ROUND(dbo.WorkerDeductions.DedPercent * dbo.PayInfoNHS.EarnGross, 2)
> ELSE dbo.WorkerDeductions.DedAmt
> END
> FROM dbo.WorkerDeductions INNER JOIN
> dbo.PayInfoNHS ON dbo.WorkerDeductions.EmployeeNumber =
> dbo.PayInfoNHS.EmployeeNumber INNER JOIN
> dbo.DeductionCodeLookup ON dbo.WorkerDeductions.DedCode =
> dbo.DeductionCodeLookup.DedCode
> WHERE (dbo.PayInfoNHS.CheckDate = CONVERT(DATETIME, '2005-03-18 00:00:00',
> 102))
> ORDER BY dbo.WorkerDeductions.EmployeeNumber
>
>|||That worked. Thank you.
David
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!