August 1, 2006 at 2:30 pm
Hello,
I am receiving the following error after running the following script in the SQL Server 2000 Query Analyzer. I've confirmed that all of the fields from the various joined tables used in the sumation are of type MONEY and there are no single quotes surrounding the variables that could confuse the type as being a varchar. Also, when the lines from CREATE TABLE down to INSERT INTO are commented out, the query returns the proper results. I'm trying to insert those results into a temporary table.
Any help that can be provided would be greatly appreciated. Thanks.
ERROR MESSAGE:
Server: Msg 260, Level 16, State 1, Line 37
Disallowed implicit conversion from data type varchar to data type money, table 'tempdb.dbo.#tempPAC_Contributors_______________________________________________________________________________________________00000001FB9F', column 'TotalContribute'. Use the CONVERT function to run this query.
SCRIPT:
SET NOCOUNT ON
DECLARE @FY AS VARCHAR(50), @BegDate AS DATETIME, @EndDate AS DATETIME, @MinTransAmt AS MONEY, @MaxTransAmt AS MONEY
--SET @FY = 'FY2005'
--SET @BegDate = '10/1/04'
--SET @EndDate = '9/30/05'
SET @FY = 'FY2006'
SET @BegDate = '10/1/05'
SET @EndDate = '9/30/06'
SET @MinTransAmt = 500
SET @MaxTransAmt = 500000000
-- Create a temporary table used to define those who have donated $500 or more during the current fiscal year. The fields Pac500Date and Pac1000Date will be populated in the next step.
CREATE TABLE #tempPAC_Contributors
(FiscalYearnvarchar(6),
member_idnumeric(9),
FirstNamenvarchar(150),
MiddleInitialnvarchar(50),
LastNamenvarchar(150),
RanchBusinessnvarchar(150),
Addressnvarchar(200),
Citynvarchar(100),
Statenvarchar(10),
Zipnvarchar(15),
ZipPlus4nvarchar(10),
Countrynvarchar(30),
Emailnvarchar(150),
HomePhonenvarchar(50),
OfficePhonenvarchar(50),
TotalContributeMONEY,
Pac500Datedatetime,
Pact1000Datedatetime)
INSERT INTO #tempPAC_Contributors
SELECT
@FY AS FiscalYear,
m.member_id,
IsNull(First_Name, '') FirstName,
IsNull(Middle_Initial, '') AS MiddleInitial,
IsNull(Last_Name, '') AS LastName,
IsNull(Ranch_Business_Name, '') AS RanchBusiness,
IsNull(Address, '') AS StreetAddress,
IsNull(City, '') AS City,
IsNull(State, '') AS State,
IsNull(Zip, '') AS Zip,
IsNull(Zip_Plus_4, '') AS ZipPlus4,
IsNull(Country, '') AS Country,
IsNull(Email, '') AS Email,
IsNull(Home_Phone, '') AS HomePhone,
IsNull(Office_Phone, '') AS OfficePhone,
IsNull(Cell_Phone, '') AS CellPhone,
SUM(transaction_amount) TotalContribute
FROM PAC_PEF_Contribution pac
INNER JOIN Transactions t
ON pac.transaction_id = t.transaction_id
INNER JOIN members m
ON m.member_id = t.ncba_id
INNER JOIN contacts c
ON c.member_id = m.member_id
WHERE deposit_date BETWEEN @BegDate AND @EndDate
AND primary_contact = 1
GROUP BY
m.member_id,
IsNull(First_Name, ''),
IsNull(Middle_Initial, ''),
IsNull(Last_Name, '') ,
IsNull(Ranch_Business_Name, ''),
IsNull(Address, ''),
IsNull(City, '') ,
IsNull(State, '') ,
IsNull(Zip, '') ,
IsNull(Zip_Plus_4, '') ,
IsNull(Country, ''),
IsNull(Email, ''),
IsNull(Home_Phone, ''),
IsNull(Cell_Phone, ''),
IsNull(Office_Phone, '')
HAVING SUM(transaction_amount) BETWEEN @MinTransAmt AND @MaxTransAmt
ORDER BY SUM(transaction_amount) DESC, LastName, FirstName, RanchBusiness
SET NOCOUNT OFF
August 1, 2006 at 2:39 pm
You forgot the Cell_Phone column in the temp table, between Office_Phone and Total_Contribute.
-Eddie
Eddie Wuerch
MCM: SQL
August 1, 2006 at 3:16 pm
Thanks Eddie!
I've been staring at the code for so long that it had to be something like this. A second pair of eyes is always greatly appreciated.
Thanks again,
Grant
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply