Case statement issue - SQL Server Forums

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

SQL Server Development (2000)

Case statement issue

Author

Topic

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2008-08-27 : 12:48:49

Below is the code.

Problem is here ...

If tblpayments.paymenttype = 'Credit' the 'Account Credit-' + tblpayments.DisplayDescription is not happening.

Does anyone have any input as to why?

CREATE PROCEDURE ASP_InvoicesPaymentsTOT

AS

SELECT tblPayments.DatePaid AS [Date], tblPayments.ClientNo AS ClientID,
Sum(-CAST([AmtPaid] AS dec(12,2))) AS Amount,
[Transaction]= case when tblpayments.paymenttype = 'Credit' then
'Account Credit-' + tblpayments.DisplayDescription else
'Payment Received-' + case when tblpayments.paymenttype = 'Check' then
'Check # ' + tblpayments.checkno else 'CC Payment'
end
end, 'PMT' as Type
FROM tblPayments
WHERE (((tblPayments.PaymentType)<>'NSF' And (tblPayments.PaymentType)<>'Refund'))
GROUP BY tblPayments.DatePaid, tblPayments.ClientNo, case when tblpayments.paymenttype = 'Credit' then
'Account Credit-' + tblpayments.DisplayDescription else
'Payment Received-' + case when tblpayments.paymenttype = 'Check' then
'Check # ' + tblpayments.checkno else 'CC Payment'
end
end, tblPayments.CheckNo
HAVING (((Sum(-CAST([AmtPaid] AS dec(12,2))))<>0))
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 13:22:18

Are you trying to append string 'Account Credit-' to field tblpayments.DisplayDescription? whats the fields datatype? Also i think Sum(-CAST([AmtPaid] AS dec(12,2))) should be
(-1) * Sum(-CAST([AmtPaid] AS dec(12,2)))

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2008-08-27 : 13:33:40

Its a nvarchar.

All this is taken from an access application and being moved to SQL server. So its obviously different in functionality between the two.

Do I need to cast tblpayments.DisplayDescription to a straight varchar?

I'll check on the sum thing. It seems to be working as written.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 13:41:54

quote:
Originally posted by mxfrail

Its a nvarchar.

All this is taken from an access application and being moved to SQL server. So its obviously different in functionality between the two.

Do I need to cast tblpayments.DisplayDescription to a straight varchar?No need if nvarchar

I'll check on the sum thing. It seems to be working as written.
sorry i meant (-1) * Sum(CAST([AmtPaid] AS dec(12,2)))

whats the error you're getting?

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2008-08-27 : 13:50:03

No error.

It just returns nothing.

When it should be Account Credit- + whatever is in displaydescription which happens to be null.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 13:53:47

ok. that because concatenating with NULL value yields NULL. make it like this

'Account Credit-' + COALESCE(tblpayments.DisplayDescription,'')

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2008-08-27 : 13:56:45

Awesome!

You have a quick explaination of that function?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 14:21:59

quote:
Originally posted by mxfrail

Awesome!

You have a quick explaination of that function?

yeah sure. COALESCE accepts a number of params and returns the first occurance of non null value out of them.

COALESCE(p1,p2,p3,p4,..)
returns first non null value out of p1,p2,p3,p4,..
have a loak at books online for detailed explanation

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources