T-SQL Case Statement ... Data Type Conversion Error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

T-SQL Case Statement ... Data Type Conversion Error

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-02-14 : 08:32:06

Brett writes "In the SQL statement below (SQL Server 2000), I am interrogating the value of char(1) field ([JournalID]) to see if the financial record is a Receipt ('R') or Payment ('P') - (or in other words a debit or credit). If the [JournalID] field is an 'R' then I want to use a multiplier of -1 in my calculations, else 1 if 'P'.

So, my T-SQL statement looks like:

---------------------------------------------
SELECT tbl_CashBook.TransactionDate, tbl_CashBook.JournalID + tbl_CashBook.CashBookID AS TransID,
tbl_CashBook.TransactionDetails, tbl_CashBook.ChequeDetails, tbl_CashBook_Details.CashBookDetailsAmount AS ItemAmount,

((CASE tbl_CashBook.JournalID WHEN 'R' THEN (-1) ELSE (1) END) * tbl_CashBook_Details.CashBookDetailsAmount) AS CalcItemAmount

FROM tbl_CashBook INNER JOIN tbl_CashBook_Details ON tbl_CashBook.CashBookID = tbl_CashBook_Details.CashBookID
---------------------------------------------

Though syntacically the statement is ok, when I run it, Query Analyser returns the error:
Server: Msg 245, Level 16, State 1, Line 2
Syntax error converting the varchar value 'R' to a column of data type int.

If I use the CAST statement (see below) it returns the same error message:
---------------------------------------------
(CAST(CASE tbl_CashBook.JournalID WHEN 'R' THEN (-1) ELSE (1) END AS INT) * tbl_CashBook_Details.CashBookDetailsAmount) AS CalcItemAmount
---------------------------------------------

And the same with CONVERT:
---------------------------------------------
(CONVERT(INT,(CASE tbl_CashBook.JournalID WHEN 'R' THEN (-1) ELSE (1) END )) * tbl_CashBook_Details.CashBookDetailsAmount) AS CalcItemAmount
---------------------------------------------

AM I ABLE TO DO THIS SOMEHOW??? SURELY IT CAN BE DONE.

It worked so easily in Microsoft Access with an IIF. One would think that SQL Server could do it, with its CASE statement.

Thanks"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-14 : 09:35:07

[code]SELECT tbl_CashBook.TransactionDate,
tbl_CashBook.JournalID + tbl_CashBook.CashBookID AS TransID,
tbl_CashBook.TransactionDetails,
tbl_CashBook.ChequeDetails,
tbl_CashBook_Details.CashBookDetailsAmount AS ItemAmount,
CASE
WHEN tbl_CashBook.JournalID = 'R' THEN -1
ELSE 1
END * tbl_CashBook_Details.CashBookDetailsAmount AS CalcItemAmount
FROM tbl_CashBook
INNER JOIN tbl_CashBook_Details ON tbl_CashBook.CashBookID = tbl_CashBook_Details.CashBookID[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources