Expression type numeric is invalid Error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Expression type numeric is invalid Error

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-10-16 : 11:42:47

Hey Guys
I hope your well
I have a query however i am getting the following error message “Msg 447, Level 16, State 0, Line 1 Expression type numeric is invalid for COLLATE clause.“

This is my query
SELECT
sjo.ID,
sjo.MID,
sjo.Trade_Association_Name,
da1.Account_Name As Trade_Association_Name,
substring(do.[MM-CHN-AGENT],2,12) as Mass_Agent_Chain_No,
da.Account_ID,
da.Account_Name as Mass_Agent_Name,
CASE WHEN sjo.Trade_Association_Name collate database_default = da.Account_ID collate database_default THEN 'Same' ELSE 'Check' END AS [Indicator]
FROM Dan.Stg_Jitter_Opp2 sjo WITH(NOLOCK)
full outer JOIN [stg_LMPAB501] do ON sjo.MID collate database_default = do.[FDMSAccountNo] collate database_default
full outer JOIN SalesForce.DailyAccounts da ON substring(do.[MM-CHN-AGENT],2,12) collate database_default = da.MID_Internal collate database_default
inner JOIN SalesForce.DailyAccounts da1 ON sjo.Trade_Association_Name = da1.Account_ID
where sjo.MID <> do.[MM-CHN-AGENT]
and da.Account_ID <> sjo.Trade_Association_Name
and da.Account_ID = '0013000000brjgfAAA'
order by sjo.Trade_Association_Name asc

And Dan.Stg_Jitter_Opp2 table consists of the following
ColumnName Data Type
Id varchar(50)
Mid numeric(18, 0)
RecordTypeID varchar(50)
Trade_Association_Name varchar(50)

And [FDMS].[SalesForce].[DailyAccounts]table consists of the following

ColumnName Data Type
Id int
Account_Id varchar(18)
account_Name varchar(150)
mid_external varchar(15)
Mid_internal varchar(15)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-10-16 : 11:51:04

collate clause is only applicable to character based datatypes. check the datatype of:
Dan.Stg_Jitter_Opp2.MID
stg_LMPAB501.FDMSAccountNo

Be One with the Optimizer
TG

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-16 : 11:55:35

Check each of the columns where you are forcing collation, and either remove the collate option, or cast it to varchar or nvarchar before applying collate clause - for example, for Mid which is numeric, and assuming FDMSAccountNo is of type VARCHAR(15) you should do this:

....
full outer JOIN [stg_LMPAB501] do 
   ON CAST(sjo.MID AS VARCHAR(15)) collate database_default = do.[FDMSAccountNo] collate database_default
...

You can apply collate clause ONLY to character type columns. So in this example, I am first casting sjo.MID to the same type as do.FDMSAccountNo and then applying the collation clause.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-10-16 : 11:57:45

Be One with the Optimizer
TG

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-10-16 : 12:11:14

Hi TG
Dan.Stg_Jitter_Opp2.MID
Datatype : numeric(18, 0)
stg_LMPAB501.FDMSAccountNo
Datatype : nvarchar(12)

if i change Stg_Jitter_Opp2.MID to nvarchar the number comes in as 8+++05etc ,

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-10-16 : 13:22:59

Why do you feel you need to use the Collate clause? The data type of a column should be chosen to best represent the data that the column contains. You certainly don't want to change the data type just so you can use the collate clause.

EDIT:
A typical reason to use COLLATE is when you need to equate two character based columns where one is say case sensitive and the other is not. There is no reason I can think of why you would get any benefit from taking data that is numeric in nature and converting it to varchar and then applying a different collation. Plus you are applying the database default collation - if you convert a number to character it will already be the database default collation.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources