MS SQL Server 2014.
I have a SQL statement that works fine:
SELECT CONCAT (
CAST(T1.[F1] AS INTEGER),
CAST(T1.[F2] AS INTEGER)
) AS F3
FROM mytable AS T1
If I then put this into a view, and try to run I receive the error:
Operand data type int is invalid for concat operator
F1 and F2 both contain decimals but I want them concatenating e.g.:
F1 = 123.00000
F2 = 456.00000
Therefore F3 = 123456
Why does the view not allow this and is there a solution?
Don't use the visual designers.
They are buggy and in this case convert
SELECT CONCAT (
CAST(T1.[F1] AS INTEGER),
CAST(T1.[F2] AS INTEGER)
) AS F3
FROM mytable AS T1
to
SELECT { fn CONCAT(CAST(T1.F1 AS INTEGER), CAST(T1.F2 AS INTEGER)) } AS F3
FROM mytable AS T1
Calling the highly limited ODBC escape sequence version of the function (that only accepts two parameters which must be strings)
Just use a standard new query window to execute your CREATE VIEW/ALTER VIEW/SELECT FROM operations and you won't get this issue.