• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 435
  • Last Modified:

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________________________________________________________________________________________________________________00000000F4BB', column 'Amount'. Use the CONVERT function to run this query.

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

Open in new window

0
Jeff S
Asked:
Jeff S
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
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)
      )
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now