Mixing
The disappearing act of the “Invalid length parameter passed to the LEFT or SUBSTRING function†error
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
3
down vote
favorite
I'm running into the "Invalid length parameter passed to the LEFT or SUBSTRING function" error, but it goes away and the query works when I include the column I'm passing into those functions, any clue?
Obfuscated screenshot below:
This is continuously repeatable, and the only change I made to the query was included the "Email.LOCATOR" column.
This query was working for years and just randomly stopped working today. I'm pretty certain it's a data issue, but am still perplexed why selecting the Email.LOCATOR column fixes the issue.
Edit: Per a request, here's the query code in text format.
Doesn't work:
SELECT SQT.QUOTATIONID, UPPER(LEFT(Email.LOCATOR, CHARINDEX('@', Email.Locator) - 1)) AS Manager--, Email.LOCATOR
FROM SALESQUOTATIONTABLE AS SQT
INNER JOIN HCMWORKER AS H
ON SQT.WORKERSALESRESPONSIBLE = H.RECID
INNER JOIN DIRPARTYTABLE AS D
ON H.PERSON = D.RECID
INNER JOIN LOGISTICSELECTRONICADDRESS AS Email
ON D.PRIMARYCONTACTEMAIL = Email.RECID
Works:
SELECT SQT.QUOTATIONID, UPPER(LEFT(Email.LOCATOR, CHARINDEX('@', Email.Locator) - 1)) AS Manager, Email.LOCATOR
FROM SALESQUOTATIONTABLE AS SQT
INNER JOIN HCMWORKER AS H
ON SQT.WORKERSALESRESPONSIBLE = H.RECID
INNER JOIN DIRPARTYTABLE AS D
ON H.PERSON = D.RECID
INNER JOIN LOGISTICSELECTRONICADDRESS AS Email
ON D.PRIMARYCONTACTEMAIL = Email.RECID
sql-server sql-server-2008 exception
asked 2 hours ago
J.D.
41429
 |Â
show 2 more comments
up vote
3
down vote
favorite
I'm running into the "Invalid length parameter passed to the LEFT or SUBSTRING function" error, but it goes away and the query works when I include the column I'm passing into those functions, any clue?
Obfuscated screenshot below:
This is continuously repeatable, and the only change I made to the query was included the "Email.LOCATOR" column.
This query was working for years and just randomly stopped working today. I'm pretty certain it's a data issue, but am still perplexed why selecting the Email.LOCATOR column fixes the issue.
Edit: Per a request, here's the query code in text format.
Doesn't work:
SELECT SQT.QUOTATIONID, UPPER(LEFT(Email.LOCATOR, CHARINDEX('@', Email.Locator) - 1)) AS Manager--, Email.LOCATOR
FROM SALESQUOTATIONTABLE AS SQT
INNER JOIN HCMWORKER AS H
ON SQT.WORKERSALESRESPONSIBLE = H.RECID
INNER JOIN DIRPARTYTABLE AS D
ON H.PERSON = D.RECID
INNER JOIN LOGISTICSELECTRONICADDRESS AS Email
ON D.PRIMARYCONTACTEMAIL = Email.RECID
Works:
SELECT SQT.QUOTATIONID, UPPER(LEFT(Email.LOCATOR, CHARINDEX('@', Email.Locator) - 1)) AS Manager, Email.LOCATOR
FROM SALESQUOTATIONTABLE AS SQT
INNER JOIN HCMWORKER AS H
ON SQT.WORKERSALESRESPONSIBLE = H.RECID
INNER JOIN DIRPARTYTABLE AS D
ON H.PERSON = D.RECID
INNER JOIN LOGISTICSELECTRONICADDRESS AS Email
ON D.PRIMARYCONTACTEMAIL = Email.RECID
sql-server sql-server-2008 exception
asked 2 hours ago
J.D.
41429
2
Some of the values in that column do not have a@. The charindex is calculated as 0 and the LEFT() gets -1 as parameter.
– ypercubeᵀᴹ
2 hours ago
2
But please post code as text, not in images
– ypercubeᵀᴹ
2 hours ago
Check your Messages tab on the "working" query. You're probably getting the same error. You just happen to be seeing results before you hit an error (as @ypercubeᵀᴹ) alluded to. When you add the extra column to theSELECTlist, SQL Server retrieves the results in a different order, so you get some results to return first before hitting a failure.
– mathewb
1 hour ago
No error in the messages tab. Messages tab says the following: (1750 rows affected) (1 row affected) Note, I verified that 1,750 results is the correct record count too. So I'm definitely getting every record back that I should be.
– J.D.
1 hour ago
1
Problem with similar cause (I think): dba.stackexchange.com/questions/108131/… Specifically what Aaron mentions in his answer: "... because you can't always rely on SQL Server filtering rows before attempting calculations."
– ypercubeᵀᴹ
57 mins ago
 |Â
show 2 more comments
up vote
3
down vote
favorite
up vote
3
down vote
favorite
I'm running into the "Invalid length parameter passed to the LEFT or SUBSTRING function" error, but it goes away and the query works when I include the column I'm passing into those functions, any clue?
Obfuscated screenshot below:
This is continuously repeatable, and the only change I made to the query was included the "Email.LOCATOR" column.
This query was working for years and just randomly stopped working today. I'm pretty certain it's a data issue, but am still perplexed why selecting the Email.LOCATOR column fixes the issue.
Edit: Per a request, here's the query code in text format.
Doesn't work:
SELECT SQT.QUOTATIONID, UPPER(LEFT(Email.LOCATOR, CHARINDEX('@', Email.Locator) - 1)) AS Manager--, Email.LOCATOR
FROM SALESQUOTATIONTABLE AS SQT
INNER JOIN HCMWORKER AS H
ON SQT.WORKERSALESRESPONSIBLE = H.RECID
INNER JOIN DIRPARTYTABLE AS D
ON H.PERSON = D.RECID
INNER JOIN LOGISTICSELECTRONICADDRESS AS Email
ON D.PRIMARYCONTACTEMAIL = Email.RECID
Works:
SELECT SQT.QUOTATIONID, UPPER(LEFT(Email.LOCATOR, CHARINDEX('@', Email.Locator) - 1)) AS Manager, Email.LOCATOR
FROM SALESQUOTATIONTABLE AS SQT
INNER JOIN HCMWORKER AS H
ON SQT.WORKERSALESRESPONSIBLE = H.RECID
INNER JOIN DIRPARTYTABLE AS D
ON H.PERSON = D.RECID
INNER JOIN LOGISTICSELECTRONICADDRESS AS Email
ON D.PRIMARYCONTACTEMAIL = Email.RECID
sql-server sql-server-2008 exception
asked 2 hours ago
J.D.
41429
I'm running into the "Invalid length parameter passed to the LEFT or SUBSTRING function" error, but it goes away and the query works when I include the column I'm passing into those functions, any clue?
Obfuscated screenshot below:
This is continuously repeatable, and the only change I made to the query was included the "Email.LOCATOR" column.
This query was working for years and just randomly stopped working today. I'm pretty certain it's a data issue, but am still perplexed why selecting the Email.LOCATOR column fixes the issue.
Edit: Per a request, here's the query code in text format.
Doesn't work:
SELECT SQT.QUOTATIONID, UPPER(LEFT(Email.LOCATOR, CHARINDEX('@', Email.Locator) - 1)) AS Manager--, Email.LOCATOR
FROM SALESQUOTATIONTABLE AS SQT
INNER JOIN HCMWORKER AS H
ON SQT.WORKERSALESRESPONSIBLE = H.RECID
INNER JOIN DIRPARTYTABLE AS D
ON H.PERSON = D.RECID
INNER JOIN LOGISTICSELECTRONICADDRESS AS Email
ON D.PRIMARYCONTACTEMAIL = Email.RECID
Works:
SELECT SQT.QUOTATIONID, UPPER(LEFT(Email.LOCATOR, CHARINDEX('@', Email.Locator) - 1)) AS Manager, Email.LOCATOR
FROM SALESQUOTATIONTABLE AS SQT
INNER JOIN HCMWORKER AS H
ON SQT.WORKERSALESRESPONSIBLE = H.RECID
INNER JOIN DIRPARTYTABLE AS D
ON H.PERSON = D.RECID
INNER JOIN LOGISTICSELECTRONICADDRESS AS Email
ON D.PRIMARYCONTACTEMAIL = Email.RECID
sql-server sql-server-2008 exception
sql-server sql-server-2008 exception
asked 2 hours ago
J.D.
41429
asked 2 hours ago
J.D.
41429
edited 1 hour ago
asked 2 hours ago
J.D.
41429
asked 2 hours ago
J.D.
41429
asked 2 hours ago
J.D.
41429
41429
2
Some of the values in that column do not have a@. The charindex is calculated as 0 and the LEFT() gets -1 as parameter.
– ypercubeᵀᴹ
2 hours ago
2
But please post code as text, not in images
– ypercubeᵀᴹ
2 hours ago
Check your Messages tab on the "working" query. You're probably getting the same error. You just happen to be seeing results before you hit an error (as @ypercubeᵀᴹ) alluded to. When you add the extra column to theSELECTlist, SQL Server retrieves the results in a different order, so you get some results to return first before hitting a failure.
– mathewb
1 hour ago
No error in the messages tab. Messages tab says the following: (1750 rows affected) (1 row affected) Note, I verified that 1,750 results is the correct record count too. So I'm definitely getting every record back that I should be.
– J.D.
1 hour ago
1
Problem with similar cause (I think): dba.stackexchange.com/questions/108131/… Specifically what Aaron mentions in his answer: "... because you can't always rely on SQL Server filtering rows before attempting calculations."
– ypercubeᵀᴹ
57 mins ago
 |Â
show 2 more comments
2
Some of the values in that column do not have a@. The charindex is calculated as 0 and the LEFT() gets -1 as parameter.
– ypercubeᵀᴹ
2 hours ago
2
But please post code as text, not in images
– ypercubeᵀᴹ
2 hours ago
Check your Messages tab on the "working" query. You're probably getting the same error. You just happen to be seeing results before you hit an error (as @ypercubeᵀᴹ) alluded to. When you add the extra column to theSELECTlist, SQL Server retrieves the results in a different order, so you get some results to return first before hitting a failure.
– mathewb
1 hour ago
No error in the messages tab. Messages tab says the following: (1750 rows affected) (1 row affected) Note, I verified that 1,750 results is the correct record count too. So I'm definitely getting every record back that I should be.
– J.D.
1 hour ago
1
Problem with similar cause (I think): dba.stackexchange.com/questions/108131/… Specifically what Aaron mentions in his answer: "... because you can't always rely on SQL Server filtering rows before attempting calculations."
– ypercubeᵀᴹ
57 mins ago
2
2
Some of the values in that column do not have a
@. The charindex is calculated as 0 and the LEFT() gets -1 as parameter.– ypercubeᵀᴹ
2 hours ago
Some of the values in that column do not have a
@. The charindex is calculated as 0 and the LEFT() gets -1 as parameter.– ypercubeᵀᴹ
2 hours ago
2
2
But please post code as text, not in images
– ypercubeᵀᴹ
2 hours ago
But please post code as text, not in images
– ypercubeᵀᴹ
2 hours ago
Check your Messages tab on the "working" query. You're probably getting the same error. You just happen to be seeing results before you hit an error (as @ypercubeᵀᴹ) alluded to. When you add the extra column to the
SELECT list, SQL Server retrieves the results in a different order, so you get some results to return first before hitting a failure.– mathewb
1 hour ago
Check your Messages tab on the "working" query. You're probably getting the same error. You just happen to be seeing results before you hit an error (as @ypercubeᵀᴹ) alluded to. When you add the extra column to the
SELECT list, SQL Server retrieves the results in a different order, so you get some results to return first before hitting a failure.– mathewb
1 hour ago
No error in the messages tab. Messages tab says the following: (1750 rows affected) (1 row affected) Note, I verified that 1,750 results is the correct record count too. So I'm definitely getting every record back that I should be.
– J.D.
1 hour ago
No error in the messages tab. Messages tab says the following: (1750 rows affected) (1 row affected) Note, I verified that 1,750 results is the correct record count too. So I'm definitely getting every record back that I should be.
– J.D.
1 hour ago
1
1
Problem with similar cause (I think): dba.stackexchange.com/questions/108131/… Specifically what Aaron mentions in his answer: "... because you can't always rely on SQL Server filtering rows before attempting calculations."
– ypercubeᵀᴹ
57 mins ago
Problem with similar cause (I think): dba.stackexchange.com/questions/108131/… Specifically what Aaron mentions in his answer: "... because you can't always rely on SQL Server filtering rows before attempting calculations."
– ypercubeᵀᴹ
57 mins ago
 |Â
show 2 more comments
2 Answers
2
active
oldest
votes
up vote
2
down vote
I think the issue is similar to this one: Strange behaviour in TSQL function (parameter with int variable or NULL behaves differently)?
Specifically what Aaron Bertrand mentions in his answer:
... because you can't always rely on SQL Server filtering rows before attempting calculations
What I think happens is that Email.Locator has some values that do not contain a @. When these values are processed, the CHARINDEX() is 0 and the LEFT() is called with parameter -1, so the error is thrown.
Bu why the error is thrown in one query and not the other? It's likely because the two queries are executed with different plans. The optimizer chooses a different plan (due to the extra column or due different statistics than last month or for whatever reason) and all the values of the column are read (and the calculations are done) before the joins to the other tables.
To avoid the issue, I suggest you use CASE, replacing
LEFT(Email.Locator, CHARINDEX('@', Email.Locator) - 1)
with:
LEFT(Email.Locator, CASE WHEN CHARINDEX('@', Email.Locator) > 0
THEN CHARINDEX('@', Email.Locator) - 1
ELSE 0
END)
answered 49 mins ago
ypercubeᵀᴹ
71.7k11119194
add a comment |Â
up vote
1
down vote
In all likelihood, the issue is indeed a data issue. A new row has been added to the LOGISTICSELECTRONICADDRESS table, with a LOCATOR that has no "@" in it.
Changing the query means that the full value of LOCATOR has to be carried through to the final results. In that case, there's no particular advantage to performing the calculation until the final result set has been determined, so SQL Server is waiting until the end of the selection process to calculate the value of Manager.
Based on the results you're getting, without LOCATOR in the SELECT list, SQL Server is choosing to compute the value of Manager before deciding what the final result rows are. It's possible that value of Manager will be much smaller than the full value of LOCATOR, so calculating instead of carrying the full LOCATOR value forward would save memory. If the data from LOGISTICSELECTRONICADDRESS is joined into the result set data before some of the other tables are joined in, then the calculation could be performed on rows that won't be returned in the final result set.
You didn't ask how to fix this, but (for the sake of completeness), you should check the value returned by CHARINDEX. If you want rows where LOCATOR has no "@", you can use a CASE statement:
SELECT SQT.QUOTATIONID,
CASE WHEN CHARINDEX('@', Email.Locator) > 0
THEN UPPER(LEFT(Email.LOCATOR, CHARINDEX('@', Email.Locator) - 1))
ELSE ''
END AS Manager
FROM SALESQUOTATIONTABLE AS SQT
INNER JOIN HCMWORKER AS H
ON SQT.WORKERSALESRESPONSIBLE = H.RECID
INNER JOIN DIRPARTYTABLE AS D
ON H.PERSON = D.RECID
INNER JOIN LOGISTICSELECTRONICADDRESS AS Email
ON D.PRIMARYCONTACTEMAIL = Email.RECID
(Of course, you could return the full LOCATOR value instead of an empty string, that's your call)
If you don't want to see rows where the "@" is missing, you can just check in the WHERE clause:
SELECT SQT.QUOTATIONID,
UPPER(LEFT(Email.LOCATOR, CHARINDEX('@', Email.Locator) - 1)) AS Manager
FROM SALESQUOTATIONTABLE AS SQT
INNER JOIN HCMWORKER AS H
ON SQT.WORKERSALESRESPONSIBLE = H.RECID
INNER JOIN DIRPARTYTABLE AS D
ON H.PERSON = D.RECID
INNER JOIN LOGISTICSELECTRONICADDRESS AS Email
ON D.PRIMARYCONTACTEMAIL = Email.RECID
WHERE CHARINDEX('@', Email.Locator) > 1
Tested on one of my own tables; it worked fine there.
answered 48 mins ago
RDFozz
9,53531130
First character is not a problem.LEFT('@abcd', 0)doesn't throw any error.
– ypercubeᵀᴹ
35 mins ago
Thanks, I thought I left that in there somewhere. I confirmed thatLEFT('@abcd',0)causes no problems for myself, while adding the info on how to solve.
– RDFozz
20 mins ago
add a comment |Â
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
I think the issue is similar to this one: Strange behaviour in TSQL function (parameter with int variable or NULL behaves differently)?
Specifically what Aaron Bertrand mentions in his answer:
... because you can't always rely on SQL Server filtering rows before attempting calculations
What I think happens is that Email.Locator has some values that do not contain a @. When these values are processed, the CHARINDEX() is 0 and the LEFT() is called with parameter -1, so the error is thrown.
Bu why the error is thrown in one query and not the other? It's likely because the two queries are executed with different plans. The optimizer chooses a different plan (due to the extra column or due different statistics than last month or for whatever reason) and all the values of the column are read (and the calculations are done) before the joins to the other tables.
To avoid the issue, I suggest you use CASE, replacing
LEFT(Email.Locator, CHARINDEX('@', Email.Locator) - 1)
with:
LEFT(Email.Locator, CASE WHEN CHARINDEX('@', Email.Locator) > 0
THEN CHARINDEX('@', Email.Locator) - 1
ELSE 0
END)
answered 49 mins ago
ypercubeᵀᴹ
71.7k11119194
add a comment |Â
up vote
2
down vote
I think the issue is similar to this one: Strange behaviour in TSQL function (parameter with int variable or NULL behaves differently)?
Specifically what Aaron Bertrand mentions in his answer:
... because you can't always rely on SQL Server filtering rows before attempting calculations
What I think happens is that Email.Locator has some values that do not contain a @. When these values are processed, the CHARINDEX() is 0 and the LEFT() is called with parameter -1, so the error is thrown.
Bu why the error is thrown in one query and not the other? It's likely because the two queries are executed with different plans. The optimizer chooses a different plan (due to the extra column or due different statistics than last month or for whatever reason) and all the values of the column are read (and the calculations are done) before the joins to the other tables.
To avoid the issue, I suggest you use CASE, replacing
LEFT(Email.Locator, CHARINDEX('@', Email.Locator) - 1)
with:
LEFT(Email.Locator, CASE WHEN CHARINDEX('@', Email.Locator) > 0
THEN CHARINDEX('@', Email.Locator) - 1
ELSE 0
END)
answered 49 mins ago
ypercubeᵀᴹ
71.7k11119194
add a comment |Â
up vote
2
down vote
up vote
2
down vote
I think the issue is similar to this one: Strange behaviour in TSQL function (parameter with int variable or NULL behaves differently)?
Specifically what Aaron Bertrand mentions in his answer:
... because you can't always rely on SQL Server filtering rows before attempting calculations
What I think happens is that Email.Locator has some values that do not contain a @. When these values are processed, the CHARINDEX() is 0 and the LEFT() is called with parameter -1, so the error is thrown.
Bu why the error is thrown in one query and not the other? It's likely because the two queries are executed with different plans. The optimizer chooses a different plan (due to the extra column or due different statistics than last month or for whatever reason) and all the values of the column are read (and the calculations are done) before the joins to the other tables.
To avoid the issue, I suggest you use CASE, replacing
LEFT(Email.Locator, CHARINDEX('@', Email.Locator) - 1)
with:
LEFT(Email.Locator, CASE WHEN CHARINDEX('@', Email.Locator) > 0
THEN CHARINDEX('@', Email.Locator) - 1
ELSE 0
END)
answered 49 mins ago
ypercubeᵀᴹ
71.7k11119194
I think the issue is similar to this one: Strange behaviour in TSQL function (parameter with int variable or NULL behaves differently)?
Specifically what Aaron Bertrand mentions in his answer:
... because you can't always rely on SQL Server filtering rows before attempting calculations
What I think happens is that Email.Locator has some values that do not contain a @. When these values are processed, the CHARINDEX() is 0 and the LEFT() is called with parameter -1, so the error is thrown.
Bu why the error is thrown in one query and not the other? It's likely because the two queries are executed with different plans. The optimizer chooses a different plan (due to the extra column or due different statistics than last month or for whatever reason) and all the values of the column are read (and the calculations are done) before the joins to the other tables.
To avoid the issue, I suggest you use CASE, replacing
LEFT(Email.Locator, CHARINDEX('@', Email.Locator) - 1)
with:
LEFT(Email.Locator, CASE WHEN CHARINDEX('@', Email.Locator) > 0
THEN CHARINDEX('@', Email.Locator) - 1
ELSE 0
END)
answered 49 mins ago
ypercubeᵀᴹ
71.7k11119194
edited 44 mins ago
answered 49 mins ago
ypercubeᵀᴹ
71.7k11119194
answered 49 mins ago
ypercubeᵀᴹ
71.7k11119194
answered 49 mins ago
ypercubeᵀᴹ
71.7k11119194
71.7k11119194
add a comment |Â
add a comment |Â
up vote
1
down vote
In all likelihood, the issue is indeed a data issue. A new row has been added to the LOGISTICSELECTRONICADDRESS table, with a LOCATOR that has no "@" in it.
Changing the query means that the full value of LOCATOR has to be carried through to the final results. In that case, there's no particular advantage to performing the calculation until the final result set has been determined, so SQL Server is waiting until the end of the selection process to calculate the value of Manager.
Based on the results you're getting, without LOCATOR in the SELECT list, SQL Server is choosing to compute the value of Manager before deciding what the final result rows are. It's possible that value of Manager will be much smaller than the full value of LOCATOR, so calculating instead of carrying the full LOCATOR value forward would save memory. If the data from LOGISTICSELECTRONICADDRESS is joined into the result set data before some of the other tables are joined in, then the calculation could be performed on rows that won't be returned in the final result set.
You didn't ask how to fix this, but (for the sake of completeness), you should check the value returned by CHARINDEX. If you want rows where LOCATOR has no "@", you can use a CASE statement:
SELECT SQT.QUOTATIONID,
CASE WHEN CHARINDEX('@', Email.Locator) > 0
THEN UPPER(LEFT(Email.LOCATOR, CHARINDEX('@', Email.Locator) - 1))
ELSE ''
END AS Manager
FROM SALESQUOTATIONTABLE AS SQT
INNER JOIN HCMWORKER AS H
ON SQT.WORKERSALESRESPONSIBLE = H.RECID
INNER JOIN DIRPARTYTABLE AS D
ON H.PERSON = D.RECID
INNER JOIN LOGISTICSELECTRONICADDRESS AS Email
ON D.PRIMARYCONTACTEMAIL = Email.RECID
(Of course, you could return the full LOCATOR value instead of an empty string, that's your call)
If you don't want to see rows where the "@" is missing, you can just check in the WHERE clause:
SELECT SQT.QUOTATIONID,
UPPER(LEFT(Email.LOCATOR, CHARINDEX('@', Email.Locator) - 1)) AS Manager
FROM SALESQUOTATIONTABLE AS SQT
INNER JOIN HCMWORKER AS H
ON SQT.WORKERSALESRESPONSIBLE = H.RECID
INNER JOIN DIRPARTYTABLE AS D
ON H.PERSON = D.RECID
INNER JOIN LOGISTICSELECTRONICADDRESS AS Email
ON D.PRIMARYCONTACTEMAIL = Email.RECID
WHERE CHARINDEX('@', Email.Locator) > 1
Tested on one of my own tables; it worked fine there.
answered 48 mins ago
RDFozz
9,53531130
First character is not a problem.LEFT('@abcd', 0)doesn't throw any error.
– ypercubeᵀᴹ
35 mins ago
Thanks, I thought I left that in there somewhere. I confirmed thatLEFT('@abcd',0)causes no problems for myself, while adding the info on how to solve.
– RDFozz
20 mins ago
add a comment |Â
up vote
1
down vote
In all likelihood, the issue is indeed a data issue. A new row has been added to the LOGISTICSELECTRONICADDRESS table, with a LOCATOR that has no "@" in it.
Changing the query means that the full value of LOCATOR has to be carried through to the final results. In that case, there's no particular advantage to performing the calculation until the final result set has been determined, so SQL Server is waiting until the end of the selection process to calculate the value of Manager.
Based on the results you're getting, without LOCATOR in the SELECT list, SQL Server is choosing to compute the value of Manager before deciding what the final result rows are. It's possible that value of Manager will be much smaller than the full value of LOCATOR, so calculating instead of carrying the full LOCATOR value forward would save memory. If the data from LOGISTICSELECTRONICADDRESS is joined into the result set data before some of the other tables are joined in, then the calculation could be performed on rows that won't be returned in the final result set.
You didn't ask how to fix this, but (for the sake of completeness), you should check the value returned by CHARINDEX. If you want rows where LOCATOR has no "@", you can use a CASE statement:
SELECT SQT.QUOTATIONID,
CASE WHEN CHARINDEX('@', Email.Locator) > 0
THEN UPPER(LEFT(Email.LOCATOR, CHARINDEX('@', Email.Locator) - 1))
ELSE ''
END AS Manager
FROM SALESQUOTATIONTABLE AS SQT
INNER JOIN HCMWORKER AS H
ON SQT.WORKERSALESRESPONSIBLE = H.RECID
INNER JOIN DIRPARTYTABLE AS D
ON H.PERSON = D.RECID
INNER JOIN LOGISTICSELECTRONICADDRESS AS Email
ON D.PRIMARYCONTACTEMAIL = Email.RECID
(Of course, you could return the full LOCATOR value instead of an empty string, that's your call)
If you don't want to see rows where the "@" is missing, you can just check in the WHERE clause:
SELECT SQT.QUOTATIONID,
UPPER(LEFT(Email.LOCATOR, CHARINDEX('@', Email.Locator) - 1)) AS Manager
FROM SALESQUOTATIONTABLE AS SQT
INNER JOIN HCMWORKER AS H
ON SQT.WORKERSALESRESPONSIBLE = H.RECID
INNER JOIN DIRPARTYTABLE AS D
ON H.PERSON = D.RECID
INNER JOIN LOGISTICSELECTRONICADDRESS AS Email
ON D.PRIMARYCONTACTEMAIL = Email.RECID
WHERE CHARINDEX('@', Email.Locator) > 1
Tested on one of my own tables; it worked fine there.
answered 48 mins ago
RDFozz
9,53531130
First character is not a problem.LEFT('@abcd', 0)doesn't throw any error.
– ypercubeᵀᴹ
35 mins ago
Thanks, I thought I left that in there somewhere. I confirmed thatLEFT('@abcd',0)causes no problems for myself, while adding the info on how to solve.
– RDFozz
20 mins ago
add a comment |Â
up vote
1
down vote
up vote
1
down vote
In all likelihood, the issue is indeed a data issue. A new row has been added to the LOGISTICSELECTRONICADDRESS table, with a LOCATOR that has no "@" in it.
Changing the query means that the full value of LOCATOR has to be carried through to the final results. In that case, there's no particular advantage to performing the calculation until the final result set has been determined, so SQL Server is waiting until the end of the selection process to calculate the value of Manager.
Based on the results you're getting, without LOCATOR in the SELECT list, SQL Server is choosing to compute the value of Manager before deciding what the final result rows are. It's possible that value of Manager will be much smaller than the full value of LOCATOR, so calculating instead of carrying the full LOCATOR value forward would save memory. If the data from LOGISTICSELECTRONICADDRESS is joined into the result set data before some of the other tables are joined in, then the calculation could be performed on rows that won't be returned in the final result set.
You didn't ask how to fix this, but (for the sake of completeness), you should check the value returned by CHARINDEX. If you want rows where LOCATOR has no "@", you can use a CASE statement:
SELECT SQT.QUOTATIONID,
CASE WHEN CHARINDEX('@', Email.Locator) > 0
THEN UPPER(LEFT(Email.LOCATOR, CHARINDEX('@', Email.Locator) - 1))
ELSE ''
END AS Manager
FROM SALESQUOTATIONTABLE AS SQT
INNER JOIN HCMWORKER AS H
ON SQT.WORKERSALESRESPONSIBLE = H.RECID
INNER JOIN DIRPARTYTABLE AS D
ON H.PERSON = D.RECID
INNER JOIN LOGISTICSELECTRONICADDRESS AS Email
ON D.PRIMARYCONTACTEMAIL = Email.RECID
(Of course, you could return the full LOCATOR value instead of an empty string, that's your call)
If you don't want to see rows where the "@" is missing, you can just check in the WHERE clause:
SELECT SQT.QUOTATIONID,
UPPER(LEFT(Email.LOCATOR, CHARINDEX('@', Email.Locator) - 1)) AS Manager
FROM SALESQUOTATIONTABLE AS SQT
INNER JOIN HCMWORKER AS H
ON SQT.WORKERSALESRESPONSIBLE = H.RECID
INNER JOIN DIRPARTYTABLE AS D
ON H.PERSON = D.RECID
INNER JOIN LOGISTICSELECTRONICADDRESS AS Email
ON D.PRIMARYCONTACTEMAIL = Email.RECID
WHERE CHARINDEX('@', Email.Locator) > 1
Tested on one of my own tables; it worked fine there.
answered 48 mins ago
RDFozz
9,53531130
In all likelihood, the issue is indeed a data issue. A new row has been added to the LOGISTICSELECTRONICADDRESS table, with a LOCATOR that has no "@" in it.
Changing the query means that the full value of LOCATOR has to be carried through to the final results. In that case, there's no particular advantage to performing the calculation until the final result set has been determined, so SQL Server is waiting until the end of the selection process to calculate the value of Manager.
Based on the results you're getting, without LOCATOR in the SELECT list, SQL Server is choosing to compute the value of Manager before deciding what the final result rows are. It's possible that value of Manager will be much smaller than the full value of LOCATOR, so calculating instead of carrying the full LOCATOR value forward would save memory. If the data from LOGISTICSELECTRONICADDRESS is joined into the result set data before some of the other tables are joined in, then the calculation could be performed on rows that won't be returned in the final result set.
You didn't ask how to fix this, but (for the sake of completeness), you should check the value returned by CHARINDEX. If you want rows where LOCATOR has no "@", you can use a CASE statement:
SELECT SQT.QUOTATIONID,
CASE WHEN CHARINDEX('@', Email.Locator) > 0
THEN UPPER(LEFT(Email.LOCATOR, CHARINDEX('@', Email.Locator) - 1))
ELSE ''
END AS Manager
FROM SALESQUOTATIONTABLE AS SQT
INNER JOIN HCMWORKER AS H
ON SQT.WORKERSALESRESPONSIBLE = H.RECID
INNER JOIN DIRPARTYTABLE AS D
ON H.PERSON = D.RECID
INNER JOIN LOGISTICSELECTRONICADDRESS AS Email
ON D.PRIMARYCONTACTEMAIL = Email.RECID
(Of course, you could return the full LOCATOR value instead of an empty string, that's your call)
If you don't want to see rows where the "@" is missing, you can just check in the WHERE clause:
SELECT SQT.QUOTATIONID,
UPPER(LEFT(Email.LOCATOR, CHARINDEX('@', Email.Locator) - 1)) AS Manager
FROM SALESQUOTATIONTABLE AS SQT
INNER JOIN HCMWORKER AS H
ON SQT.WORKERSALESRESPONSIBLE = H.RECID
INNER JOIN DIRPARTYTABLE AS D
ON H.PERSON = D.RECID
INNER JOIN LOGISTICSELECTRONICADDRESS AS Email
ON D.PRIMARYCONTACTEMAIL = Email.RECID
WHERE CHARINDEX('@', Email.Locator) > 1
Tested on one of my own tables; it worked fine there.
answered 48 mins ago
RDFozz
9,53531130
edited 23 mins ago
answered 48 mins ago
RDFozz
9,53531130
answered 48 mins ago
RDFozz
9,53531130
answered 48 mins ago
RDFozz
9,53531130
9,53531130
First character is not a problem.LEFT('@abcd', 0)doesn't throw any error.
– ypercubeᵀᴹ
35 mins ago
Thanks, I thought I left that in there somewhere. I confirmed thatLEFT('@abcd',0)causes no problems for myself, while adding the info on how to solve.
– RDFozz
20 mins ago
add a comment |Â
First character is not a problem.LEFT('@abcd', 0)doesn't throw any error.
– ypercubeᵀᴹ
35 mins ago
Thanks, I thought I left that in there somewhere. I confirmed thatLEFT('@abcd',0)causes no problems for myself, while adding the info on how to solve.
– RDFozz
20 mins ago
First character is not a problem.
LEFT('@abcd', 0) doesn't throw any error.– ypercubeᵀᴹ
35 mins ago
First character is not a problem.
LEFT('@abcd', 0) doesn't throw any error.– ypercubeᵀᴹ
35 mins ago
Thanks, I thought I left that in there somewhere. I confirmed that
LEFT('@abcd',0) causes no problems for myself, while adding the info on how to solve.– RDFozz
20 mins ago
Thanks, I thought I left that in there somewhere. I confirmed that
LEFT('@abcd',0) causes no problems for myself, while adding the info on how to solve.– RDFozz
20 mins ago
add a comment |Â
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f218073%2fthe-disappearing-act-of-the-invalid-length-parameter-passed-to-the-left-or-subs%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
2
Some of the values in that column do not have a
@. The charindex is calculated as 0 and the LEFT() gets -1 as parameter.– ypercubeᵀᴹ
2 hours ago
2
But please post code as text, not in images
– ypercubeᵀᴹ
2 hours ago
Check your Messages tab on the "working" query. You're probably getting the same error. You just happen to be seeing results before you hit an error (as @ypercubeᵀᴹ) alluded to. When you add the extra column to the
SELECTlist, SQL Server retrieves the results in a different order, so you get some results to return first before hitting a failure.– mathewb
1 hour ago
No error in the messages tab. Messages tab says the following: (1750 rows affected) (1 row affected) Note, I verified that 1,750 results is the correct record count too. So I'm definitely getting every record back that I should be.
– J.D.
1 hour ago
1
Problem with similar cause (I think): dba.stackexchange.com/questions/108131/… Specifically what Aaron mentions in his answer: "... because you can't always rely on SQL Server filtering rows before attempting calculations."
– ypercubeᵀᴹ
57 mins ago