asked on

Where is this 'Implicit conversion from data type varchar to money...'?

Why do I get multiple instances of this error:

Server: Msg 257, Level 16, State 3, Line 75
Implicit conversion from data type varchar to money is not allowed. Use the CONVERT function to run this query.

Whenever I run this dynamic SQL query, which you can see I've already tried very hard to cover my bases on:

DECLARE @CritSD varchar(50)
DECLARE @CritED varchar(50)
DECLARE @CritQuantity varchar(50)
DECLARE @CritQuantMeasure varchar(50)
DECLARE @TDP_CritCmmnCInMax money
DECLARE @TDP_CritCmmnCInMin money
DECLARE @TDP_CritCmmnADTMax money
DECLARE @TDP_CritCmmnADTMin money
DECLARE @TDP_CritCmmnADAMax money
DECLARE @TDP_CritCmmnADAMin money
DECLARE @CritEntryDateLast datetime
DECLARE @CritEntryDateFirst datetime
DECLARE @CritLatestTripLast datetime
DECLARE @CritLatestTripFirst datetime
DECLARE @CritTripsMax int
DECLARE @CritTripsMin int
DECLARE @TDP_CritCInMax money
DECLARE @TDP_CritCInMin money
DECLARE @TDP_CritADTMax money
DECLARE @TDP_CritADTMin money
DECLARE @TDP_CritADAMax money
DECLARE @TDP_CritADAMin money
DECLARE @IncludeNational varchar(1)
DECLARE @PromoName varchar(300)
DECLARE @PromoLevel varchar(50)

SET @CritSD                   = '2006-05-01'
SET @CritED                   = '2006-06-01'
SET @CritQuantity             = '25000'
SET @CritQuantMeasure            = 'ADT'
SET @TDP_CritCmmnCInMax       = 999999999999
SET @TDP_CritCmmnCInMin       = 999999999999
SET @TDP_CritCmmnADTMax       = 999999999999
SET @TDP_CritCmmnADTMin       = 999999999999
SET @TDP_CritCmmnADAMax       = 999999999999
SET @TDP_CritCmmnADAMin       = 999999999999
SET @CritEntryDateLast             = '2100-01-01'
SET @CritEntryDateFirst       = '1900-01-01'
SET @CritLatestTripLast       = '2100-01-01'
SET @CritLatestTripFirst       = '1900-01-01'
SET @CritTripsMax             = 99999999
SET @CritTripsMin             = 3
SET @TDP_CritCInMax             = 999999999999
SET @TDP_CritCInMin             = 10
SET @TDP_CritADTMax             = 999999999999
SET @TDP_CritADTMin             = 1
SET @TDP_CritADAMax             = 999999999999
SET @TDP_CritADAMin             = 1
SET @IncludeNational             = 'N'
SET @PromoName                   = 'tieredpertest'
SET @PromoLevel             = 'B01'

--declare perpare output table variables
DECLARE @OutputTableData varchar(3000)
DECLARE @OutputTableMailout varchar(3000)
DECLARE @OutputTableGroup varchar(3000)
SET @OutputTableData = 'gcgmkt.dbo.usr_store1_' +@PromoLevel+ '_' +@PromoName
SET @OutputTableMailout = 'gcgmkt.dbo.usr_mailout_store1_' +@PromoLevel+ '_' +@PromoName
SET @OutputTableGroup = 'gcgmkt.dbo.usr_group_store1_' +@PromoLevel+ '_' +@PromoName

--declare date variables and calculates adjusted criteria date for gaming
DECLARE @CritStartDate datetime
DECLARE @CritEndDate datetime
SET @CritStartDate = CONVERT(DATETIME, @CritSD)
SET @CritEndDate = CONVERT(DATETIME, @CritED)
print @CritSD
print @CritED
Print @CritStartDate
Print @CritEndDate

--create dynamic sql insert selects
DECLARE @thisSQL varchar(5000)
SET @thisSQL = 'CREATE TABLE ' + @OutputTableData + ' (Player_id varchar(50)
      , FirstName varchar(300)
      , LastName varchar(300)
      , Address1a varchar(300)
      , Address1b varchar(300)
      , City1 varchar(300)
      , State1 varchar(50)
      , Zip1 varchar(50)
      , CoinIn money
      , ADT money
      , ADA money
      , Trips int
      , LatestTrip datetime
      , EntryDate datetime)

INSERT INTO ' + @OutputTableData + '
      (Player_id
      , FirstName
      , LastName
      , Address1a
      , Address1b
      , City1
      , State1
      , Zip1
      , CoinIn
      , ADT
      , ADA
      , Trips
      , LatestTrip
      , EntryDate)

SELECT       store1_statdetail.Meta_ID as Player_ID
      , store1_PLAYER.FirstName as FirstName
      , store1_PLAYER.LastName as LastName
      , store1_ACCOUNT.Address1A as Address1A
      , store1_ACCOUNT.Address1B as Address1B
      , store1_ACCOUNT.City1 as City1
      , store1_ACCOUNT.State1 as State1
      , store1_ACCOUNT.Zip1 as Zip1
      , CONVERT(money, SUM(store1_STATDETAIL.CashIn)) As CoinIn
      , CONVERT(money, (SUM(store1_statdetail.TWin) / COUNT(DISTINCT store1_statdetail.GamingDate))) AS ADT
      , CONVERT(money, ((SUM(store1_statdetail.CashIn) - SUM(store1_statdetail.CashOut) - SUM(store1_statdetail.JackPot)) / COUNT(DISTINCT store1_statdetail.GamingDate))) AS ADA
      , COUNT(DISTINCT store1_statdetail.GamingDate) AS Trips
      , MAX(CONVERT(DATETIME, store1_STATDETAIL.GamingDate, 102)) AS LatestTrip
      , MIN(CONVERT(DATETIME, store1_PLAYER.EntryDate, 102)) as EntryDate
FROM (store1oasissql.WinOasis.dbo.CDS_STATDETAIL AS store1_STATDETAIL
            INNER JOIN store1oasissql.WinOasis.dbo.CDS_ACCOUNT AS store1_ACCOUNT
            ON store1_STATDETAIL.Meta_ID=store1_ACCOUNT.Account_ID)
      INNER JOIN store1oasissql.WinOasis.dbo.CDS_PLAYER AS store1_PLAYER
      ON (store1_STATDETAIL.Meta_ID=store1_PLAYER.Account_ID)
      AND (store1_ACCOUNT.Account_ID=store1_PLAYER.Account_ID)
WHERE (store1_statdetail.IDType = ''P'') AND (store1_statdetail.StatType = ''SLOT'') AND (store1_ACCOUNT.MailFlag=''Y'')
AND (CONVERT(DATETIME, store1_statdetail.GamingDate) BETWEEN CONVERT(DATETIME, '+@CritSD+') AND CONVERT(DATETIME, '+@CritED+'))
GROUP BY Meta_ID
--combined
HAVING (CONVERT(money, (SUM(store1_STATDETAIL.CashIn))) / 2 >= CONVERT(money, '''+@TDP_CritCmmnCInMin+''')
            AND CONVERT(money, (SUM(store1_STATDETAIL.CashIn))) / 2 <= CONVERT(money, '''+@TDP_CritCmmnCInMax+'''))
      AND CONVERT(money, (SUM(store1_statdetail.TWin) / COUNT(DISTINCT store1_statdetail.GamingDate)) >= CONVERT(money, '''+@TDP_CritCmmnADTMin+''')
      AND CONVERT(money, (SUM(store1_statdetail.TWin) / COUNT(DISTINCT store1_statdetail.GamingDate))) <= CONVERT(money, '''+@TDP_CritCmmnADTMax+'''))
AND (CONVERT(money, ((SUM(store1_statdetail.CashIn) - SUM(store1_statdetail.CashOut) - SUM(store1_statdetail.JackPot)) / COUNT(DISTINCT store1_statdetail.GamingDate))) >= CONVERT(money, '''+@TDP_CritCmmnADAMin+''')
AND CONVERT(money, ((SUM(store1_statdetail.CashIn) - SUM(store1_statdetail.CashOut) - SUM(store1_statdetail.JackPot)) / COUNT(DISTINCT store1_statdetail.GamingDate))) <= CONVERT(money, '''+@TDP_CritCmmnADAMax+'''))

--points
OR (CONVERT(money, SUM(store1_STATDETAIL.CashIn)) / 2 >= CONVERT(money, '''+@TDP_CritCInMin+''')
AND CONVERT(money, ''SUM(store1_STATDETAIL.CashIn)) / 2 <= CONVERT(money, '''+@TDP_CritCInMax+'''))
--adt
OR (CONVERT(money, (SUM(store1_statdetail.TWin) / COUNT(DISTINCT store1_statdetail.GamingDate))) >= CONVERT(money, '''+@TDP_CritADTMin+''')
AND CONVERT(money, (SUM(store1_statdetail.TWin) / COUNT(DISTINCT store1_statdetail.GamingDate))) <= CONVERT(money, '''+@TDP_CritADTMax+'''))
--ada
OR ((CONVERT(money, (SUM(store1_statdetail.CashIn) - SUM(store1_statdetail.CashOut) - SUM(store1_statdetail.JackPot)) / COUNT(DISTINCT store1_statdetail.GamingDate))) >= CONVERT(money, '''+@TDP_CritADAMin+''')
AND (CONVERT(money, (SUM(store1_statdetail.CashIn) - SUM(store1_statdetail.CashOut) - SUM(store1_statdetail.JackPot)) / COUNT(DISTINCT store1_statdetail.GamingDate))) <= CONVERT(money, '''+@TDP_CritADAMax+'''))

--trips
AND COUNT(DISTINCT store1_statdetail.GamingDate) BETWEEN '+@CritTripsMax+' AND '+@CritTripsMin+'
--entry date
AND MAX(CONVERT(DATETIME, store1_PLAYER.EntryDate, 102)) BETWEEN '+@CritEntryDateLast+' AND '+@CritEntryDateFirst+'
--latest trip
AND MAX(CONVERT(DATETIME, store1_STATDETAIL.GamingDate, 102)) BETWEEN '+@CritLatestTripLast+' AND '+@CritLatestTripFirst+'
--no address nulls
AND (MAX(store1_ACCOUNT.City1) IS NOT NULL)
AND (MAX(store1_ACCOUNT.Address1a) IS NOT NULL)
AND (MAX(store1_ACCOUNT.State1) IS NOT NULL)
AND (MAX(store1_ACCOUNT.Zip1) IS NOT NULL)
ORDER BY ADT DESC'

Print (@thisSQL)

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aneesh

DECLARE @CritSD varchar(50)
DECLARE @CritED varchar(50)
DECLARE @CritQuantity varchar(50)
DECLARE @CritQuantMeasure varchar(50)
DECLARE @TDP_CritCmmnCInMax money
DECLARE @TDP_CritCmmnCInMin money
DECLARE @TDP_CritCmmnADTMax money
DECLARE @TDP_CritCmmnADTMin money
DECLARE @TDP_CritCmmnADAMax money
DECLARE @TDP_CritCmmnADAMin money
DECLARE @CritEntryDateLast datetime
DECLARE @CritEntryDateFirst datetime
DECLARE @CritLatestTripLast datetime
DECLARE @CritLatestTripFirst datetime
DECLARE @CritTripsMax int
DECLARE @CritTripsMin int
DECLARE @TDP_CritCInMax money
DECLARE @TDP_CritCInMin money
DECLARE @TDP_CritADTMax money
DECLARE @TDP_CritADTMin money
DECLARE @TDP_CritADAMax money
DECLARE @TDP_CritADAMin money
DECLARE @IncludeNational varchar(1)
DECLARE @PromoName varchar(300)
DECLARE @PromoLevel varchar(50)

SET @CritSD = '2006-05-01'
SET @CritED = '2006-06-01'
SET @CritQuantity = '25000'
SET @CritQuantMeasure = 'ADT'
SET @TDP_CritCmmnCInMax = 999999999999
SET @TDP_CritCmmnCInMin = 999999999999
SET @TDP_CritCmmnADTMax = 999999999999
SET @TDP_CritCmmnADTMin = 999999999999
SET @TDP_CritCmmnADAMax = 999999999999
SET @TDP_CritCmmnADAMin = 999999999999
SET @CritEntryDateLast = '2100-01-01'
SET @CritEntryDateFirst = '1900-01-01'
SET @CritLatestTripLast = '2100-01-01'
SET @CritLatestTripFirst = '1900-01-01'
SET @CritTripsMax = 99999999
SET @CritTripsMin = 3
SET @TDP_CritCInMax = 999999999999
SET @TDP_CritCInMin = 10
SET @TDP_CritADTMax = 999999999999
SET @TDP_CritADTMin = 1
SET @TDP_CritADAMax = 999999999999
SET @TDP_CritADAMin = 1
SET @IncludeNational = 'N'
SET @PromoName = 'tieredpertest'
SET @PromoLevel = 'B01'

--declare perpare output table variables
DECLARE @OutputTableData varchar(3000)
DECLARE @OutputTableMailout varchar(3000)
DECLARE @OutputTableGroup varchar(3000)
SET @OutputTableData = 'gcgmkt.dbo.usr_store1_' +@PromoLevel+ '_' +@PromoName
SET @OutputTableMailout = 'gcgmkt.dbo.usr_mailout_store1_' +@PromoLevel+ '_' +@PromoName
SET @OutputTableGroup = 'gcgmkt.dbo.usr_group_store1_' +@PromoLevel+ '_' +@PromoName

--declare date variables and calculates adjusted criteria date for gaming
DECLARE @CritStartDate datetime
DECLARE @CritEndDate datetime
SET @CritStartDate = CONVERT(DATETIME, @CritSD)
SET @CritEndDate = CONVERT(DATETIME, @CritED)
print @CritSD
print @CritED
Print @CritStartDate
Print @CritEndDate

--create dynamic sql insert selects
DECLARE @thisSQL varchar(8000)
SET @thisSQL = 'CREATE TABLE ' + @OutputTableData + ' (Player_id varchar(50)
, FirstName varchar(300)
, LastName varchar(300)
, Address1a varchar(300)
, Address1b varchar(300)
, City1 varchar(300)
, State1 varchar(50)
, Zip1 varchar(50)
, CoinIn money
, ADT money
, ADA money
, Trips int
, LatestTrip datetime
, EntryDate datetime)

INSERT INTO ' + @OutputTableData + '
(Player_id
, FirstName
, LastName
, Address1a
, Address1b
, City1
, State1
, Zip1
, CoinIn
, ADT
, ADA
, Trips
, LatestTrip
, EntryDate)

SELECT store1_statdetail.Meta_ID as Player_ID
, store1_PLAYER.FirstName as FirstName
, store1_PLAYER.LastName as LastName
, store1_ACCOUNT.Address1A as Address1A
, store1_ACCOUNT.Address1B as Address1B
, store1_ACCOUNT.City1 as City1
, store1_ACCOUNT.State1 as State1
, store1_ACCOUNT.Zip1 as Zip1
, CONVERT(money, SUM(store1_STATDETAIL.CashIn)) As CoinIn
, CONVERT(money, (SUM(store1_statdetail.TWin) / COUNT(DISTINCT store1_statdetail.GamingDate))) AS ADT
, CONVERT(money, ((SUM(store1_statdetail.CashIn) - SUM(store1_statdetail.CashOut) - SUM(store1_statdetail.JackPot)) / COUNT(DISTINCT store1_statdetail.GamingDate))) AS ADA
, COUNT(DISTINCT store1_statdetail.GamingDate) AS Trips
, MAX(CONVERT(DATETIME, store1_STATDETAIL.GamingDate, 102)) AS LatestTrip
, MIN(CONVERT(DATETIME, store1_PLAYER.EntryDate, 102)) as EntryDate
FROM (store1oasissql.WinOasis.dbo.CDS_STATDETAIL AS store1_STATDETAIL
INNER JOIN store1oasissql.WinOasis.dbo.CDS_ACCOUNT AS store1_ACCOUNT
ON store1_STATDETAIL.Meta_ID=store1_ACCOUNT.Account_ID)
INNER JOIN store1oasissql.WinOasis.dbo.CDS_PLAYER AS store1_PLAYER
ON (store1_STATDETAIL.Meta_ID=store1_PLAYER.Account_ID)
AND (store1_ACCOUNT.Account_ID=store1_PLAYER.Account_ID)
WHERE (store1_statdetail.IDType = ''P'') AND (store1_statdetail.StatType = ''SLOT'') AND (store1_ACCOUNT.MailFlag=''Y'')
AND (CONVERT(DATETIME, store1_statdetail.GamingDate) BETWEEN CONVERT(DATETIME, '+@CritSD+') AND CONVERT(DATETIME, '+@CritED+'))
GROUP BY Meta_ID
--combined
HAVING (CONVERT(money, (SUM(store1_STATDETAIL.CashIn))) / 2 >= CONVERT(money, '''+CAST(@TDP_CritCmmnCInMin as varchar )+''')
AND CONVERT(money, (SUM(store1_STATDETAIL.CashIn))) / 2 <= CONVERT(money, '''+CAST(@TDP_CritCmmnCInMax as varchar )+'''))
AND CONVERT(money, (SUM(store1_statdetail.TWin) / COUNT(DISTINCT store1_statdetail.GamingDate)) >= CONVERT(money, '''+CAST( @TDP_CritCmmnADTMin as varchar)+''')
AND CONVERT(money, (SUM(store1_statdetail.TWin) / COUNT(DISTINCT store1_statdetail.GamingDate))) <= CONVERT(money, '''+CAST(@TDP_CritCmmnADTMax as varchar)+'''))
AND (CONVERT(money, ((SUM(store1_statdetail.CashIn) - SUM(store1_statdetail.CashOut) - SUM(store1_statdetail.JackPot)) / COUNT(DISTINCT store1_statdetail.GamingDate))) >= CONVERT(money, '''+Cast(@TDP_CritCmmnADAMin as varchar)+''')
AND CONVERT(money, ((SUM(store1_statdetail.CashIn) - SUM(store1_statdetail.CashOut) - SUM(store1_statdetail.JackPot)) / COUNT(DISTINCT store1_statdetail.GamingDate))) <= CONVERT(money, '''+cast(@TDP_CritCmmnADAMax as varchar)+'''))

--points
OR (CONVERT(money, SUM(store1_STATDETAIL.CashIn)) / 2 >= CONVERT(money, '''+CAST(@TDP_CritCInMin as varchar)+''')
AND CONVERT(money, ''SUM(store1_STATDETAIL.CashIn)) / 2 <= CONVERT(money, '''+CAST(@TDP_CritCInMax as varchar)+'''))
--adt
OR (CONVERT(money, (SUM(store1_statdetail.TWin) / COUNT(DISTINCT store1_statdetail.GamingDate))) >= CONVERT(money, '''+CAST(@TDP_CritADTMin as varchar)+''')
AND CONVERT(money, (SUM(store1_statdetail.TWin) / COUNT(DISTINCT store1_statdetail.GamingDate))) <= CONVERT(money, '''+CAST(@TDP_CritADTMax as varchar)+'''))
--ada
OR ((CONVERT(money, (SUM(store1_statdetail.CashIn) - SUM(store1_statdetail.CashOut) - SUM(store1_statdetail.JackPot)) / COUNT(DISTINCT store1_statdetail.GamingDate))) >= CONVERT(money, '''+CAST(@TDP_CritADAMin as varchar)+''')
AND (CONVERT(money, (SUM(store1_statdetail.CashIn) - SUM(store1_statdetail.CashOut) - SUM(store1_statdetail.JackPot)) / COUNT(DISTINCT store1_statdetail.GamingDate))) <= CONVERT(money, '''+CAST(@TDP_CritADAMax as varchar)+'''))

--trips
AND COUNT(DISTINCT store1_statdetail.GamingDate) BETWEEN '+CAST(@CritTripsMax as Varchar)+' AND '+CAST(@CritTripsMin as Varchar)+'
--entry date
AND MAX(CONVERT(DATETIME, store1_PLAYER.EntryDate, 102)) BETWEEN '''+CONVERT(varchar(10),@CritEntryDateLast,102)+''' AND '''+CONVERT(varchar(10),@CritEntryDateFirst,102)+'''
--latest trip
AND MAX(CONVERT(DATETIME, store1_STATDETAIL.GamingDate, 102)) BETWEEN '''+CONVERT(varchar(10),@CritLatestTripLast,102)+''' AND '''+CONVERT(varchar(10),@CritLatestTripFirst,102)+'''
--no address nulls
AND (MAX(store1_ACCOUNT.City1) IS NOT NULL)
AND (MAX(store1_ACCOUNT.Address1a) IS NOT NULL)
AND (MAX(store1_ACCOUNT.State1) IS NOT NULL)
AND (MAX(store1_ACCOUNT.Zip1) IS NOT NULL)
ORDER BY ADT DESC'

Print (@thisSQL)

bgarrabrant

ASKER

Thanks! Cool. It works but I don't understand why. What is the CAST function doing for me in this case?

Aneesh

bgarrabrant,
You are adding two different datatypes thru the '+' operation, read the fiollowing section in BOL...

mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsqlref.chm::/ts_ca-co_2f3o.htm

bgarrabrant

ASKER

I knew much of that (although the implicit conversion chart will come in handy).

I guess where I'm unclear (and thanks, after providing the answer, for providing the understanding as well) is why, to make it work in a dynamic SQL command, Ineed to take a MONEY variable, Cast it into VARCHAR, and CONVERT it back to MONEY in order to do the comparison operands I have built into the statement.

HAVING (CONVERT(money, (SUM(store1_STATDETAIL.CashIn))) / 2 >= CONVERT(money, '''+CAST(@TDP_CritCmmnCInMin as varchar )+''')
AND CONVERT(money, (SUM(store1_STATDETAIL.CashIn))) / 2 <= CONVERT(money, '''+CAST(@TDP_CritCmmnCInMax as varchar )+'''))

I've written other dynamic SQL statements using money variables without all this conversion. Why doesn't this work?--

HAVING (CONVERT(money, (SUM(store1_STATDETAIL.CashIn))) / 2 >= CONVERT(money, '_@TDP_CritCmmnCInMin+')
AND CONVERT(money, (SUM(store1_STATDETAIL.CashIn))) / 2 <= CONVERT(money, '+@TDP_CritCmmnCInMax+'))

Also, for the following comparisons, do I need to cast my variables into VARCHAR, as you have shown, and then CONVERT them back to INT (for @CritTripsMax) or DATETIME (for @CritEntryDateLast) in order for the comparison operands to work as expected, or are those conversions implicit here? I probably need to drop the ,102 and use more characters for the VARCHAR conversion, at least, correct?--

--trips
AND COUNT(DISTINCT store1_statdetail.GamingDate) BETWEEN '+CAST(@CritTripsMax as Varchar)+' AND '+CAST(@CritTripsMin as Varchar)+'
--entry date
AND MAX(CONVERT(DATETIME, store1_PLAYER.EntryDate, 102)) BETWEEN '''+CONVERT(varchar(10),@CritEntryDateLast,102)+''' AND '''+CONVERT(varchar(10),@CritEntryDateFirst,102)+'''
--latest trip
AND MAX(CONVERT(DATETIME, store1_STATDETAIL.GamingDate, 102)) BETWEEN '''+CONVERT(varchar(10),@CritLatestTripLast,102)+''' AND '''+CONVERT(varchar(10),@CritLatestTripFirst,102)+'''