Expression type void type is invalid for COLLATE clause
-
Hi
I´m getting problmes to make run a qry. THere is an extract of tables and query... the error I'm getting is:
Server: Msg 447, Level 16, State 1, Line 1254
Expression type void type is invalid for COLLATE clause.
Server: Msg 1750, Level 16, State 1, Line 1254
Could not create constraint. See previous errors.
CREATE TABLE #Graf_1
(NumID bigint,
Month1 smalldatetime,
Month2 smalldatetime,
Month3 smalldatetime,
Month4 smalldatetime,
Month5 smalldatetime,
Month6 smalldatetime)
CREATE TABLE #Graf
(NumID bigint,
m1 BIGINT, m2 BIGINT, m3 BIGINT, m4 BIGINT, m5 BIGINT, m6 BIGINT)
Create Table #G_Temp ( NumID BIGINT ,
v1 varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS ,
v2 varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS ,
v3 varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS ,
v4 varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS ,
v5 varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS ,
v6 varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS)
INSERT INTO #G_Temp (NumID, v1,v2,v3,v4,v5,v6)
SELECT #Graf.NumID,
ISNULL(dbo.FloatToStr(SUM(CAST( m1 as decimal)/100 ) ,0,0,0),''),
ISNULL(dbo.FloatToStr(SUM(CAST( m2 as decimal)/100 ) ,0,0,0),''),
ISNULL(dbo.FloatToStr(SUM(CAST( m3 as decimal)/100 ) ,0,0,0),''),
ISNULL(dbo.FloatToStr(SUM(CAST( m4 as decimal)/100 ) ,0,0,0),''),
ISNULL(dbo.FloatToStr(SUM(CAST( m5 as decimal)/100 ) ,0,0,0),''),
ISNULL(dbo.FloatToStr(SUM(CAST( m6 as decimal)/100 ) ,0,0,0),'')
FROM #Graf
INNER JOIN #Graf_1
ON #Graf_1.NumID = #Graf.NumID
GROUP BY #Graf.NumID
This is an extract from actual tables... they are part of a very large SP, which involves several tables and a final result of a nearly 2 million rows table... as you may believe, it is not a very cute proc, but it's critical... and it is not working... (besides tables had grown and the performance is very low... but this is another problem, and dealing with it is that I struggle myself with this one...)
Thnaks
NICOLAS
-
Could you post the definition of FloatToStr as well?
-
It just performs a
@Result = ' $ ' + LTRIM(LEFT(CONVERT(CHAR(16), CAST(@VALOR AS MONEY), 1), 13))
where @Result is a varchar(20)
Nicolas
-
There seems to be four input parameters... Could you post a definition that I can copy and paste into my qa ( a "create function" statement), please
-
I'll just be spectator and wait for Jesper's impressive insights...
..but wanted to throw something in that I read yesterday in the great Ken Henderson's book..."str() is better than cast or convert because it provides for right justification and allows the number of decimal places to be specified..eg:
select str(pi(), 7, 4) as string, cast(pi() as char(16)) as cast"...
**ASCII stupid question, get a stupid ANSI !!!** -
I haven't got a clue what is wrong - but a good start is to be able to reconstruct the error
So much for the "impressive insights"...
-
PLease, note that this function is just to run on a specific DB...
ALTER FUNCTION dbo.FloatToStr
(@Value float, @fmtMoney bit,
@Decimals bit, @Thousands bit)
RETURNS VARCHAR(20) AS
BEGIN
-- fmtMoney=1 add symbol $
-- Decimales=1 show 2 decimal positions
-- Thousands = 1 show tousands separator
DECLARE @Result varchar (20)
DECLARE @MoneySymbol char(1)
DECLARE @delta int
IF @fmtMoney = 1
SET @MoneySymbol = '$'
ELSE SET @MoneySymbol = ''
IF @Decimals = 1
SET @delta = 16
ELSE SET @delta = 13
IF (@Valor >= 0) SELECT @Result = @MoneySymbol+' '+LTRIM(LEFT(CONVERT(CHAR(16),CAST(@Value AS MONEY), 1),@Delta))
ELSE SELECT @Result = @MOneySymbol+' -' + LTRIM(LEFT(CONVERT(CHAR(16), ABS(CAST(@Value AS MONEY)), 1),@Delta))
-- This a a weird thing....
-- it asumes that current format is 1,000,000.00
-- and forces a 1.000.000,00 return
IF @Thousands = 1 SET @Result = REPLACE(@Result, ',', '&')
ELSE SET @Result = REPLACE(@Result, ',', '')
SET @Result = REPLACE(@Result, '.', ',')
SET @Result = REPLACE(@Result, '&', '.')
RETURN @Result
END
-
I doesn't compile, @Valor is undefined
I think I will also need some sample data in order to reconstruct the error
Or maybe someone with more insight than me can see what the problem is without executing your code
-
I'm sorry... I just forgot to translate... replace VALOR for VALUE (same meaning different language).. the original was in spanish, but I translate var names to english before post and forgot to replace that...
btw, this function is used ev'ry where through the db, so I don't think it's the problem... and so long so far, I've solved the problem by forcing COLLATE in some comparisons ( WHERE a = b COLLATE... ) but I don't understand what happened, because server config has not changed and the original tables has the same collation all of them...
Nicolas...
-
Could it be that tempdb has a different collation than the db in which you execute your code?
-
YES YES YES YES
The tempdb has different collation... but I wonder why never we've had problems...
Thanks a lot... I would try to make them change it...
Nicolas
-
Jesper - I knew it was only a matter of time! Thanks again for yet another lesson!
**ASCII stupid question, get a stupid ANSI !!!** -
Come on, I hadn't a clue until Nicolas' last post
I am not an expert - yesterday I couldn't find out how to backup a single db to a file and had to ask a colleague Besides I must say that I have really learned a lot from everyone else by participating in the discussions in here.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply