May 15, 2008 at 2:22 pm
I have the following code:
SELECTTop 1 B.OrderNumber
,B.[OrderGroupNumber]
,A.PaymentTypeXID
,'PaymentType' = Case
WHEN a.PaymentTypeXID IN (0,7,9,16) THEN A.[CCName]
ELSE c.PaymentType
END
,C.PaymentType
,C.PaymentCategory
,A.PaymentID
,S.XID
,'AccountID' = CASE
WHEN A.[PaymentTypeXID]=0 AND S.[XID] IN (0,1,2,3) THEN a.[CClastfour]
WHEN A.[PaymentTypeXID]=1 THEN A.BankAccount--Bank Draft
WHEN A.[PaymentTypeXID]=5 THEN a.[CheckNumber]-- Personal Check
WHEN A.[PaymentTypeXID]=2 THEN a.[GCCode]-- Gift Certficate
WHEN A.[PaymentTypeXID]=6 THEN a.[CheckNumber]-- Cashier Check
WHEN A.[PaymentTypeXID]=7 THEN a.[CClastfour]-- Credit Card Credit
WHEN A.[PaymentTypeXID]=8 THEN ''-- Consultant Account
WHEN A.[PaymentTypeXID]=13 THEN ''-- Credit Personal Check
WHEN A.[PaymentTypeXID]=14 THEN '' -- Credit Money Order
WHEN A.[PaymentTypeXID]=4 THEN a.[CheckNumber]-- Money Order
WHEN A.[PaymentTypeXID]=16 THEN a.[CCLastFour] -- Re-occuring Payment
WHEN A.[PaymentTypeXID]=9 THEN a.[CClastFour] -- Blind Credit
WHEN A.[PaymentTypeXID]=11 THEN 'Pay #'+ CONVERT(VARCHAR(20),b.ordernumber) COLLATE SQL_Latin1_General_CP1_CI_AS + CONVERT(VARCHAR(20),a.[ConsultantID]) COLLATE SQL_Latin1_General_CP1_CI_AS
WHEN A.[PaymentTypeXID]=10 THEN a.[BankAccount] -- Re-occuring Payment
WHEN A.[PaymentTypeXID]=12 THEN a.[GCCode]-- Gift Certficate Credit
WHEN A.[PaymentTypeXID]=15 THEN '' -- Credit Cashier Check
END
--,A.[BankAccount]
,'ApprovalCode' = CASE
WHEN A.[PaymentTypeXID]=0 AND S.[XID] IN (0,1,2,3) THEN p.ResponseAuthorizationCode
WHEN A.[PaymentTypeXID]=0 AND S.[XID] IN (0,1,2,3) AND p.ResponseAuthorizationCode IS NULL THEN 'APPROVED'
WHEN a.[PaymentTypeXID]=2 THEN 'APPROVED'
WHEN a.[PaymentTypeXID]=1 THEN 'APPROVED'
WHEN a.[PaymentTypeXID]=6 THEN 'APPROVED'
WHEN a.[PaymentTypeXID]=5 THEN 'APPROVED'
WHEN a.[PaymentTypeXID]=7 THEN p.ResponseAuthorizationCode
WHEN a.[PaymentTypeXID]=7 AND p.ResponseAuthorizationCode IS NULL THEN 'APPROVED'
WHEN a.[PaymentTypeXID]=8 THEN 'APPROVED'
WHEN a.[PaymentTypeXID]=13 THEN 'APPROVED'
WHEN a.[PaymentTypeXID]=14 THEN 'APPROVED'
WHEN a.[PaymentTypeXID]=4 THEN 'APPROVED'
WHEN a.[PaymentTypeXID]=16 THEN 'APPROVED'
WHEN a.[PaymentTypeXID]=9 THEN 'APPROVED'
WHEN a.[PaymentTypeXID]=10 THEN 'APPROVED'
WHEN a.[PaymentTypeXID]=11 THEN 'APPROVED'
WHEN a.[PaymentTypeXID]=12 THEN 'APPROVED'
WHEN a.[PaymentTypeXID]=15 THEN 'APPROVED'
END
,A.[CCName]
,A.[CCLastFour]
,P.ResponseAuthorizationCode AS AuthorizationCode
,A.[CheckNumber]
,A.TransactionTotal
,A.[ConsultantID]
FROMdbo.uvw_OrderGroupPayment AS A With (NoLock)
INNER JOIN dbo.OrderForm AS B With (NoLock) ON
A.ORderGroupNumber = B.OrderGroupNumber
LEFT OUTER JOIN SharedDimension.dbo.DimPaymentType AS C With (NoLock) ON
A.PaymentTypeXID = C.XID
LEFT OUTER JOIN CreditCardProcessorLog AS P With (noLock) ON
A.OrderGroupNumber = P.OrderGroupNumber
LEFT OUTER JOIN SharedDimension.dbo.DimCCTranType AS S WITH (NOLock) ON
P.[TransactionTypeXID] = S.[XID]
WHEREB.OrderNumber = @OrderNumber
AND PaymentTypeXID NOT IN (3,8,11,12,13,14,15)
Error:Msg 451, Level 16, State 1, Line 4
Cannot resolve collation conflict for column 4 in SELECT statement.
I have checked the collation on both databases and they are the same. any ideas?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
May 15, 2008 at 2:35 pm
Collation can be by database, table, or even column. Script the tables out, looking for collation differences.
May 15, 2008 at 2:58 pm
I found the differences;
FROMdbo.uvw_OrderGroupPayment AS A With (NoLock) -- this is a view
INNER JOIN dbo.OrderForm AS B With (NoLock) ON
A.ORderGroupNumber = B.OrderGroupNumber
LEFT OUTER JOIN SharedDimension.dbo.DimPaymentType AS C With (NoLock) ON
A.PaymentTypeXID = C.XID
The difference is SharedDimension.dbo.DimPaymentType is
COLLATE SQL_Latin1_General_CP1_CI_AS
and the OrderPayment table which the uvw_OrderGroupPayment view is based on is
COLLATE Latin1_General_CI_AS
But how do I *** the collation statements into the proc?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
May 15, 2008 at 3:52 pm
I figured it out. It was
,'PaymentType' = Case
WHEN a.PaymentTypeXID IN (0,7,9,16) THEN A.[CCName] COLLATE SQL_Latin1_General_CP1_CI_AS
WHEN a.PaymentTypeXID IN (2) THEN 'Certficate'
END
Thanks
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply