Case Syntax Issues 1

[MatthewPeters]

I need some help with CASE syntax. I think I need to use CASE here, I'm open to any other suggestions. Here's the basic problem. I've got two tables: GLAccounts and GLDetails.

GLAccounts : GLAcctID, PCNum, AcctNum, AcctType, . . .
GLDetails : GLDetailsID, GLAcctID, Balance, . . .

I want to select the balances for each account and then multiply the balance by -1 when the acctType meets a certain criteria.

Here's what I've go so far.
SELECT a.AcctNum,
a.AcctDesc,
a.AcctType as type,
d.Balance AS MTD =
case type
when 'I' then d.Balance * -1
when 'O' then d.Balance * -1
end
FROM GLAccounts a
INNER JOIN GLDetails d ON a.GLAcctID = d.GLAcctID
INNER JOIN ProfitCenters p ON a.PCNum = p.PCNum
INNER JOIN reports_MO r ON d.reportID_MO = r.reportID_MO
WHERE r.year = 2002 AND r.month = 8 AND a.PCNum = 71 AND
(a.AcctNum = 329.50 or a.AcctNum = 401.00 or
a.AcctNum = 402.01 or a.AcctNum = 404.00 or
a.AcctNum = 406.01 or a.AcctNum = 430.00 or
(a.AcctNum >= 407.00 and a.AcctNum <= 409.99) or
(a.AcctNum >= 415.00 and a.AcctNum <= 416.00) or
(a.AcctNum >= 420.01 and a.AcctNum <= 420.02)
)

 

[sunila7]

Hi,

Try this query.....

SELECT a.AcctNum,
a.AcctDesc,
a.AcctType as type,

case type
when 'I' then d.Balance * -1
when 'O' then d.Balance * -1
end MTD
FROM GLAccounts a
INNER JOIN GLDetails d ON a.GLAcctID = d.GLAcctID
INNER JOIN ProfitCenters p ON a.PCNum = p.PCNum
INNER JOIN reports_MO r ON d.reportID_MO = r.reportID_MO
WHERE r.year = 2002 AND r.month = 8 AND a.PCNum = 71 AND
(a.AcctNum = 329.50 or a.AcctNum = 401.00 or
a.AcctNum = 402.01 or a.AcctNum = 404.00 or
a.AcctNum = 406.01 or a.AcctNum = 430.00 or
(a.AcctNum >= 407.00 and a.AcctNum <= 409.99) or
(a.AcctNum >= 415.00 and a.AcctNum <= 416.00) or
(a.AcctNum >= 420.01 and a.AcctNum <= 420.02)
)

Sunil

 

[MatthewPeters]

Sunil,

I ran your version of the query and received the following error:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'type'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'type'.

So I made the following changes and it worked like a charm. Thanks alot.

SELECT a.AcctNum,
a.AcctDesc,
a.AcctType,

case a.AcctType
when 'I' then d.Balance * -1
when 'O' then d.Balance * -1
end MTD
FROM GLAccounts a
INNER JOIN GLDetails d ON a.GLAcctID = d.GLAcctID
INNER JOIN ProfitCenters p ON a.PCNum = p.PCNum
INNER JOIN reports_MO r ON d.reportID_MO = r.reportID_MO
WHERE r.year = 2002 AND r.month = 8 AND a.PCNum = 71 AND
(a.AcctNum = 329.50 or a.AcctNum = 401.00 or
a.AcctNum = 402.01 or a.AcctNum = 404.00 or
a.AcctNum = 406.01 or a.AcctNum = 430.00 or
(a.AcctNum >= 407.00 and a.AcctNum <= 409.99) or
(a.AcctNum >= 415.00 and a.AcctNum <= 416.00) or
(a.AcctNum >= 420.01 and a.AcctNum <= 420.02)
)

Matt