CAST to int used with LEFT on an nvarchar field not working properly?

Question

I'm trying to create a view where I union 4 tables. One of the fields is a document number. The only way I can pull the related document number from one of the tables is by running a LEFT on the 6 left-most characters. But when I do this and then CAST to integer I still cannot query the field. It's like it's looking past my where clause and trying to convert ALL of the characters in the field.

This is what I'm doing:

SELECT     dbo.OSLP.U_RepStatus AS RepStatus, dbo.OSLP.SlpCode, dbo.OPCH.CardCode, dbo.OPCH.CardName, 'IN' AS DocTypeDesc, dbo.OPCH.DocNum, 
                      dbo.OPCH.DocEntry, dbo.OPCH.DocDate, dbo.OPCH.NumAtCard, dbo.OPCH.DocStatus, dbo.OPCH.DocTotal, dbo.OPCH.U_CommInvNo AS ARInvNo
FROM         dbo.OPCH INNER JOIN
                      dbo.OCRD ON dbo.OPCH.CardCode = dbo.OCRD.CardCode INNER JOIN
                      dbo.OSLP ON dbo.OCRD.U_SalesRepNumber = dbo.OSLP.SlpCode
WHERE     (dbo.OSLP.U_RepStatus IN ('Dir', 'Ind', 'Sal')) AND (dbo.OPCH.U_CommInvNo IS NOT NULL)
UNION
SELECT     OSLP_1.U_RepStatus AS RepStatus, OSLP_1.SlpCode, dbo.ORPC.CardCode, dbo.ORPC.CardName, 'CM' AS DocTypeDesc, dbo.ORPC.DocNum, dbo.ORPC.DocEntry, 
                      dbo.ORPC.DocDate, dbo.ORPC.NumAtCard, dbo.ORPC.DocStatus, - 1 * dbo.ORPC.DocTotal AS DocTotal, dbo.ORPC.U_CommInvNo AS ARInvNo
FROM         dbo.ORPC INNER JOIN
                      dbo.OCRD AS OCRD_1 ON dbo.ORPC.CardCode = OCRD_1.CardCode INNER JOIN
                      dbo.OSLP AS OSLP_1 ON OCRD_1.U_SalesRepNumber = OSLP_1.SlpCode
WHERE     (OSLP_1.U_RepStatus IN ('Dir', 'Ind', 'Sal')) AND (dbo.ORPC.U_CommInvNo IS NOT NULL)
UNION
SELECT     T3.U_RepStatus AS RepStatus, T3.SlpCode, T0.ContraAct AS CardCode, T2.CardName, 'JE' AS DocTypeDesc, T1.TransId AS DocNum, T1.TransId AS DocEntry, 
                      T1.RefDate AS DocDate, T0.LineMemo AS NumAtCard, T1.BtfStatus AS DocStatus, T0.Debit AS DocTotal, CAST(LEFT(T0.LineMemo, 6) AS INT) AS ARInvNo
FROM         dbo.JDT1 AS T0 INNER JOIN
                      dbo.OJDT AS T1 ON T0.TransId = T1.TransId INNER JOIN
                      dbo.OCRD AS T2 ON T0.ContraAct = T2.CardCode INNER JOIN
                      dbo.OSLP AS T3 ON T2.U_SalesRepNumber = T3.SlpCode
WHERE     (T0.ShortName = 'CompanyName') AND (T1.TransType = 30) AND (T0.Account = '_SYS00000000624') AND (T3.U_RepStatus IN ('Dir', 'Ind', 'Sal')) AND (T0.Debit > 0)
UNION
SELECT     T7.U_RepStatus AS RepStatus, T7.SlpCode, T4.ContraAct AS CardCode, T6.CardName, 'JE' AS DocTypeDesc, T5.TransId AS DocNum, T5.TransId AS DocEntry, 
                      T5.RefDate AS DocDate, T4.LineMemo AS NumAtCard, T5.BtfStatus AS DocStatus, - (1 * T4.Credit) AS DocTotal, CAST(LEFT(T4.LineMemo, 6) AS INT) AS ARInvNo
FROM         dbo.JDT1 AS T4 INNER JOIN
                      dbo.OJDT AS T5 ON T4.TransId = T5.TransId INNER JOIN
                      dbo.OCRD AS T6 ON T4.ContraAct = T6.CardCode INNER JOIN
                      dbo.OSLP AS T7 ON T6.U_SalesRepNumber = T7.SlpCode
WHERE     (T4.ShortName = 'CompanyName') AND (T5.TransType = 30) AND (T4.Account = '_SYS00000000624') AND (T7.U_RepStatus IN ('Dir', 'Ind', 'Sal')) AND (T4.Credit > 0)

If I query this view with WHERE ARInvNo = 295696 I get this error: Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the nvarchar value 'A/P In' to data type int.

If I take out the bottom 2 unions from my view I can query with WHERE ARInvNo = 295696 without an issue. If I run the second 2 parts of the query, nothing in my results includes 'A/P In', although that does exist in rows in JDT1 outside of the WHERE criteria I'm using. Any idea why SQL is looking past my WHERE statement and trying to LEFT and CAST every field before it returns any data?

To clarify, this returns no results:

SELECT T0.LineMemo 
FROM JDT1 T0  INNER JOIN OJDT T1 ON T0.TransId = T1.TransId INNER JOIN OCRD T2 ON T0.ContraAct = T2.CardCode INNER JOIN OSLP T3 ON T2.U_SalesRepNumber = T3.SlpCode 
WHERE T0.ShortName = 'CompanyName' AND T1.TransType = 30 AND T0.Account = '_SYS00000000624' AND T3.U_RepStatus IN ('Dir', 'Ind', 'Sal') AND left(T0.LineMemo,3)='A/P In'

"Any idea why SQL is looking past my WHERE statement and trying to LEFT and CAST every field before it returns any data?" Because it thinks it'll get a faster result that way. There's no guarantee that the WHERE clauses inside your view will be evaluated before a WHERE clause applied to the view. At execution time the optimiser will base its query plan on the whole SQL query made up of the view plus the "outer" query using the view. A lot of SQL code that uses views ends up running much faster because of that strategy. — Matt Gibson, May 01 '14 at 21:03

score 0 · Accepted Answer · edited May 23 '17 at 10:32

0

I see it now.

It's seeing 'A/P in' in your values. It can't CAST the '/' character to INT

MSSQL cast( [varcharColumn] to int) in SELECT gets executed before WHERE clause filters out bad values

In short you will want to do this:

select (case when isnumeric(TO.LineMemo) = 1 then cast(TO.LineMemo as int) end)

edited May 23 '17 at 10:32

Community

1
1

answered May 01 '14 at 20:32

Hituptony

2,740
3
22
44

I'm not sure I follow. I'm casting to INT because the field dbo.OPCH.U_CommInvNo is INT already. If I take out the second half I can query with WHERE ARInvNo = 295696. I need this to be able to happen without quotes because the original docnum datatype is integer. I'm using this for a crystal report and I need to be able to link ARInvNo to the DocNum field in a different table which is an integer. – jb_bryant May 01 '14 at 20:40
If I take out CAST and try this I get the same error: WHERE T10.ARInvNo = '295696' – jb_bryant May 01 '14 at 20:42
TRY CONVERT - CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) – Hituptony May 01 '14 at 20:43
That didn't work either. Is this what you meant? Convert(INT,LEFT(T4.LineMemo, 6)) AS ARInvNo – jb_bryant May 01 '14 at 21:01
Just saw your answer. I'll try that. What's weird, though, is that 'A/P In' is not in the values unless it is reading the entire table to CAST before reading the WHERE. My WHERE clause gets rid of any LineMemo that has left 6 values that are nonnumeric. – jb_bryant May 01 '14 at 21:03

CAST to int used with LEFT on an nvarchar field not working properly?

1 Answers1