These conversion functions should be used within your Teradata SQL queries. the first argument is the value to be formatted or converted, and the second argument is a template that defines the output or input format. When it tries to convert the value 'Colgate' to a numeric(9,0) data type, you get the error.The type conversion functions use common calling function i.e. Query 2 throws an error because both predicates from sales have implicit convertions and to optimizer chose to begin from sales.pid before filtering only the rows that keep numbers. The query optimizer decides the comparison of a column without a implicit convertion is less expensive, so it starts by executing the predicate sales.type = 'number' and it generates a subset composed of the rows which contains all the values of sales.type as numbers (and you can convert real numbers from a string cloumn with no problem). In this case sales.pid has an implicit convertion on both queries since it's being compared to products.idn which has a different data type. Query 1 worked because when you removed the N (which is used to convert a string to nvarchar) SQL Server didn't need to perform an implicit convertion of sales.type from varchar to nvarchar. Knowing that, ff you check the execution plan it will help you understand the reason you get the error with one query and not with the other given the sample data you provided: The query optimizer might change the order things are done to get your result the fastest way among the options it come up with (given the result of the query isn't altered). but it would be better to fix the data model such as to ensure only like or compatible data types are compared. This can be addressed with TRY_CAST, TRY_CONVERT, CASE expression, etc. ='number' AND CONVERT_IMPLICIT(numeric(9,0).,0)=(1.)Īlthough the non-Unicode literal (or parameter) may workaround the problem, the query will still be vulnerable to run time errors. The plan with the non-Unicode literal has the same shape except the predicate order in the scan operator is reversed. As to whether or not this actually happens depends on the order of evaluation in the execution plan.īelow is the sales table clustered index scan predicate from the Unicode literal execution plan, showing the conversion occurs before the 'number' condition is evaluated: CONVERT_IMPLICIT(numeric(9,0).,0)=(1.) AND CONVERT_IMPLICIT(nvarchar(10).,0)=N'number' If I modify the query slightly it works: SELECTĪ conversion error will occur at run time when an attempt is made to convert the sales.pid value 'Colgate' value to numeric(9) to evaluate the join criteria. I don't see why casting NVARCHAR to NUMERIC is an issue: SELECT CAST (N'1' as NUMERIC) INNER JOIN sales ON products.idn = sales.pid Why does the following SELECT fail (For some reason beyond my control, the generated query has N literals for strings.)? SELECT The transaction filter takes care of JOINing correctly. Sales has mixed data i.e VARCHAR and NUMERIC. INSERT INTO sales (idn, pid, type) VALUES (2, 'Colgate', 'word') INSERT INTO sales (idn, pid, type) VALUES (1, 1, 'number') I have the following schema/data in MSSQL Server 2019 ( SQLFiddle): CREATE TABLE products(
0 Comments
Leave a Reply. |