Quey Tunning

Question

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

ISNULL requires two arguments;

ISNULL ( check_expression , replacement_value )

so to replace the value ISNULL as 0 use

ISNULL([T_ICPAudit.Amount],0)

b_boy SSCertifiable Points: 5145 More actions · Answer 2

Am still getting the same error message:

Msg 4145, Level 15, State 1, Line 5

An expression of non-boolean type specified in a context where a condition is expected, near 'ELSE'.

Msg 156, Level 15, State 1, Line 9

Incorrect syntax near the keyword 'THEN'.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

IF is procedural statement.

SELECTCASE TransactionType

WHEN 'REFUND' THEN CAST(-Amount AS MONEY)

WHEN 'CHARGE' THEN CAST(Amount AS MONEY)

ELSE CAST(0 AS MONEY)

END AS [Amount],

TransactionType,

TransactionStatus,

TransactionResult,

ICPBatchNumber,

CONVERT(VARCHAR(11), RecordCreated, 106) AS [RecordCreated]

FROMCMDATA.dbo.T_ICPAudit

WHERETransactionResult = 'ACCEPTED'

AND RecordCreated >= '2006-06-23 00:00:01'

N 56°04'39.16"
E 12°55'05.25"

b_boy SSCertifiable Points: 5145 More actions · Answer 4

I ran it again and got the following:

Msg 8117, Level 16, State 1, Line 1

Operand data type nvarchar is invalid for minus operator.

seems its data type related

b_boy SSCertifiable Points: 5145 More actions · Answer 5

Fixed, incorrect placement of syntax, eventually used:

WHEN 'REFUND' THEN - CAST (Amount AS MONEY)

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 6

So the query work now?

N 56°04'39.16"
E 12°55'05.25"

b_boy SSCertifiable Points: 5145 More actions · Answer 7

Yeah, got one question for you though, what are the best and easily comprehensible books out there to understand T-SQL development?

Cookies on SQLServerCentral