Jeff S
asked on
SQL 2005 - Disallowed implicit conversion from data type varchar to data type money,
I am getting the following Error. Suggestions?
Server: Msg 260, Level 16, State 1, Line 69
Disallowed implicit conversion from data type varchar to data type money, table 'tempdb.dbo.#Tmp__________ __________ __________ __________ __________ __________ __________ __________ __________ __________ __________ __00000000 F4BB', column 'Amount'. Use the CONVERT function to run this query.
Server: Msg 260, Level 16, State 1, Line 69
Disallowed implicit conversion from data type varchar to data type money, table 'tempdb.dbo.#Tmp__________
SET NOCOUNT ON
declare @groupby1 varchar(60),
@groupby2 varchar(60)
set @groupby1 = convert(varchar(60),'None')
set @groupby2 = convert(varchar(60),'None')
SELECT
pm.DateDeleted,
pm.DeletedBy,
pm.PayerName,
pm.PayerType,
pm.DateOfEntry,
pm.Amount,
CASE
WHEN pm.paymenttype = '1' THEN 'Cash'
WHEN pm.paymenttype = '2' THEN 'Check'
WHEN pm.paymenttype = '3' THEN 'Credit Card'
WHEN pm.paymenttype = '4' THEN 'EFT'
WHEN pm.paymenttype = '5' THEN 'Money Order'
WHEN pm.paymenttype = '6' THEN 'Conveyance'
ELSE ''
END AS PaymentMethod,
pp.PatientId,
pp.PatientProfileID,
pp.Last,
pp.First,
dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix) AS [Patient Name],
pv.Visit,
pv.TicketNumber,
case @groupby1
when 'Patient' then dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix)
when 'Payer Name' then pm.PayerName
when 'Payment Method' then CASE
WHEN pm.paymenttype = '1' THEN 'Cash'
WHEN pm.paymenttype = '2' THEN 'Check'
WHEN pm.paymenttype = '3' THEN 'Credit Card'
WHEN pm.paymenttype = '4' THEN 'EFT'
WHEN pm.paymenttype = '5' THEN 'Money Order'
WHEN pm.paymenttype = '6' THEN 'Conveyance'
ELSE ''
END
when 'User' then pm.DeletedBy
else 'None'
end as Group1,
case @groupby2
when 'Patient' then dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix)
when 'Payer Name' then pm.PayerName
when 'Payment Method' then CASE
WHEN pm.paymenttype = '1' THEN 'Cash'
WHEN pm.paymenttype = '2' THEN 'Check'
WHEN pm.paymenttype = '3' THEN 'Credit Card'
WHEN pm.paymenttype = '4' THEN 'EFT'
WHEN pm.paymenttype = '5' THEN 'Money Order'
WHEN pm.paymenttype = '6' THEN 'Conveyance'
ELSE ''
END
when 'User' then pm.DeletedBy
else 'None'
end as Group2
INTO #Tmp
FROM cusPMDeleteLog pm
LEFT JOIN cusVTDeleteLog vt ON pm.PaymentMethodId = vt.PaymentMethodId
LEFT JOIN patientvisit pv ON pv.PatientVisitId = vt.PatientVisitId
LEFT JOIN patientprofile pp ON pp.PatientProfileId = pv.PatientProfileId
SELECT
*
FROM
#tmp
WHERE
[Amount]<> '0.00'
AND -- Filter on Deleted Date
(
DateDeleted >= ISNULL('01/01/2009','01/01/1900') AND [DateDeleted] < DATEADD(d,1, ISNULL('12/31/2009','01/01/3000'))
)
AND --Filter on Patient
(
(NULL IS NOT NULL AND [PatientProfileID] IN (NULL)) OR
(NULL IS NULL)
)
DROP TABLE #tmp
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.