SQL Stored Procedure Syntax Error

I'm trying to execute the following stored procedure but it's telling me there's an error. The error states "Invalid length parameter passed to the LEFT or SUBSTRING function". Here is the query code:

SELECT S1.fsource, RTRIM(A.fmeasure), A1.finvdate, A1.fcustno, A1.fbcompany, A1.fsono, S1.fprodcl, LTRIM(S1.finumber), S1.fgroup, 
RTRIM(A.fpartno), A.fmdescript, A.fshipqty, A.fprice, A.fshipqty*A.fprice As ExtPrice, Left(A.fsokey,6) As SONumber, LTrim(RTrim(Substring([fsokey],7,Len(Substring([fsokey],7,Len([fsokey])))-3))) As SOItemNo, Right(A.fsokey,3) As SORelease,
J1.fjobno As JobNo, J1.fmqty As JOQty, J1.fduedate As DueDate, RTRIM(J2.fbompart) As RMPart, J2.fbomdesc As RMDesc, J2.fqty_iss As IssuedQty,
CASE WHEN S1.fsource = 'S' THEN A.fshipqty ELSE J2.fqty_iss END As ActualQty, CASE WHEN S1.fsource = 'S' THEN RTRIM(A.fpartno) ELSE RTRIM(J2.fbompart) END As ActualPart
FROM dbo.aritem AS A
     INNER JOIN dbo.armast AS A1
            ON A1.fcinvoice = A.fcinvoice
	 INNER JOIN dbo.soitem As S1
			ON S1.fsono = LTrim(RTrim(Substring([fsokey],7,Len(Substring([fsokey],7,Len([fsokey])))-3)))
	 LEFT JOIN dbo.joitem As J1 ON Left(A.fsokey,6) = J1.fsono AND LTrim(RTrim(Substring([fsokey],7,Len(Substring([fsokey],7,Len([fsokey])))-3))) = LTRIM(J1.finumber) AND Right(A.fsokey,3) = J1.fkey
	 LEFT JOIN dbo.jodbom As J2 ON J1.fjobno = J2.fjobno 
WHERE  ((S1.fprodcl = '01') OR (S1.fprodcl = '04')) AND (S1.fgroup <> 'SCHG') AND RTRIM(CASE WHEN S1.fsource = 'S' THEN RTRIM(A.fpartno) ELSE RTRIM(J2.fbompart) END) = (select dbo.tbl_Parameters.ItemNumber FROM dbo.tbl_Parameters); 

Open in new window


The line that the error is in is line 22 which is this line:

RTRIM(A.fpartno), A.fmdescript, A.fshipqty, A.fprice, A.fshipqty*A.fprice As ExtPrice, Left(A.fsokey,6) As SONumber, LTrim(RTrim(Substring([fsokey],7,Len(Substring([fsokey],7,Len([fsokey])))-3))) As SOItemNo, Right(A.fsokey,3) As SORelease,

Open in new window

LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You may passing an invalid length. Mostly this happens when a field has a shorter value than expected. Check the length of fsokey field for all records. The ones with small length should be the one that is originating the error so you need to see how to handle those cases.
0
Scott PletcherSenior DBACommented:
You have at least one fsokey that is less than 9 bytes long.

You can avoid the error by coding for that condition:

RTRIM(A.fpartno), A.fmdescript, A.fshipqty, A.fprice, A.fshipqty*A.fprice As ExtPrice, Left(A.fsokey,6) As SONumber, LTrim(RTrim(Substring([fsokey],7,case when len([fsokey]) < 9 then 0 else Len(Substring([fsokey],7,Len([fsokey])))-3 end))) As SOItemNo, Right(A.fsokey,3) As SORelease,
0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I'm trying to breakout the fsokey field. That field is where I need to extract the SONumber which is 6 characters, the SOItemNo which is either 1 or 2 characters, and the SORelease which is always 3 characters.

The SONumber is always the first 6 characters of the fsokey.
The SOItemNo follows the SONumber but is separated by either 1 or 2 spaces. It's separated from the SONumber by 1 space IF the SOItem is 2 characters long and IF it's 1 character long then there will be 2 spaces after the SONumber. Then the final 3 characters is the SORelease. There are no spaces between the SORelease and the SOItemNo. I had this working in Access as follows:

Left([fsokey],6) AS SONumber, Trim(Mid([fsokey],7,Len(Mid([fsokey],7))-3)) AS SOItemNo, Right([fsokey],3) AS SORelease

Open in new window


I posted yesterday about converting this to SQL and I thought I had the right syntax for it but I'm guessing I don't now.

Here are some examples of the data in the fsokey field:

300542  1000     There are 2 spaces between the SONumber and the beginning of the SOItemNo & SORelease
SONumber = 300542
SOItemNo = 1
SORelease = 000

300081 12000     There is 1 space between the SONumber and the beginning of the SOItemNo & SORelease
SONumber = 300081
SOItemNo = 12
SORelease = 000

I'm still new with SQL and really don't have any idea how to extract this correctly.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Try to run only this query to verify that it returns the expected values for each item:
SELECT LEFT(fsokey,6) SONumber, SUBSTRING(fsokey,7,LEN(fsokey)-9), RIGHT(fsokey, 3) SORelease
FROM aritem 

Open in new window

0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I get the same error message, Invalid Length parameter passed to the LEFT or SUBSTRING function
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
This version trims the SOItemNo:
SELECT LEFT(fsokey,6) SONumber, LTRIM(RTRIM(SUBSTRING(fsokey,7,LEN(fsokey)-9))) SOItemNo, RIGHT(fsokey, 3) SORelease
FROM aritem

Open in new window

0
Scott PletcherSenior DBACommented:
I strongly suggest using a CROSS APPLY to parse out the relevant data columns one time while assigning them the appropriate alias name.  That alias name can then be used throughout the code.  This (1) isolates the data manipulation to a single time, which can be corrected in only one place and (2) makes the code much easier to follow as meaningful names can be used in place of a bunch of substring functions:

For example, please see if this code breaks out the data columns accurately.  If not, just correct the CROSS APPLY part and the rest of the code will not need changed at all.


SELECT S1.fsource, RTRIM(A.fmeasure), A1.finvdate, A1.fcustno, A1.fbcompany, A1.fsono, S1.fprodcl, LTRIM(S1.finumber), S1.fgroup,
RTRIM(A.fpartno), A.fmdescript, A.fshipqty, A.fprice, A.fshipqty*A.fprice As ExtPrice, aan.SONumber, aan.SOItemNo, aan.SORelease,
J1.fjobno As JobNo, J1.fmqty As JOQty, J1.fduedate As DueDate, RTRIM(J2.fbompart) As RMPart, J2.fbomdesc As RMDesc, J2.fqty_iss As IssuedQty,
CASE WHEN S1.fsource = 'S' THEN A.fshipqty ELSE J2.fqty_iss END As ActualQty, CASE WHEN S1.fsource = 'S' THEN RTRIM(A.fpartno) ELSE RTRIM(J2.fbompart) END As ActualPart
FROM dbo.aritem AS A
     INNER JOIN dbo.armast AS A1
            ON A1.fcinvoice = A.fcinvoice
       INNER JOIN dbo.soitem As S1
                  ON S1.fsono = aan.SOItemNo
       LEFT JOIN dbo.joitem As J1 ON aan.SONumber = J1.fsono AND
                                     aan.SOItemNo = LTRIM(J1.finumber) AND
                                     aan.SORelease = J1.fkey
       LEFT JOIN dbo.jodbom As J2 ON J1.fjobno = J2.fjobno
CROSS APPLY (
    SELECT
        LEFT(A.fsokey, 6) AS SONumber,
        CASE WHEN LEN(A.fsokey) >= 9 THEN RIGHT(A.fsokey, 3) ELSE '' END AS SORelease,
        CASE WHEN LEN(A.fsokey) >= 9 THEN LTRIM(RTRIM(SUBSTRING(A.fsokey, 7, LEN(A.fsokey) - 9))) ELSE '' END AS SOItemNo        
) AS aan --assign_alias_names

WHERE  ((S1.fprodcl = '01') OR (S1.fprodcl = '04')) AND (S1.fgroup <> 'SCHG') AND RTRIM(CASE WHEN S1.fsource = 'S' THEN RTRIM(A.fpartno) ELSE RTRIM(J2.fbompart) END) = (select dbo.tbl_Parameters.It
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Then you need to find which records has less than 10 characters (6+1+3 should be the minimum expected):
SELECT *
FROM aritem
WHERE LEN(fsokey)<10

Open in new window

0
Scott PletcherSenior DBACommented:
Note that in my previous post of code I accidentally left off part of the WHERE.  To clarify, the full WHERE clause as originally written should be used:

...
CROSS APPLY (
...
WHERE  ((S1.fprodcl = '01') OR (S1.fprodcl = '04')) AND (S1.fgroup <> 'SCHG') AND RTRIM(CASE WHEN S1.fsource = 'S' THEN RTRIM(A.fpartno) ELSE RTRIM(J2.fbompart) END) = (select dbo.tbl_Parameters.ItemNumber FROM dbo.tbl_Parameters);
0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Scott, I added the cross apply code but I'm still getting the same error message when I try to execute that query.
0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I got it working by changing the syntax to this. This was from the sample run to just show how these would look after executing the query

SELECT LEFT(fsokey,6) SONumber, LTRIM(RTRIM(SUBSTRING(fsokey,7,3))) SOItemNo, RIGHT(fsokey, 3) SORelease, fsokey
FROM aritem   

Open in new window

0
Scott PletcherSenior DBACommented:
You "added the CROSS APPLY", but did you change the references in the main SELECT code to the alias names, as in the code above?  All the functions against fsokey should be isolated to just the CROSS APPLY.
0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Yes I did. I put the alias names in where the original code was. I did change the code in the CROSS APPLY as referenced in my last post and now it's working ok.
0
Scott PletcherSenior DBACommented:
Great.  That's one of the big advantages of the APPLY method: the functions to derive the column values are in only one place, vs. before where several places in the SELECT were pulling out the SONumber and SOItemNo.
0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Can you use the CROSS APPLY functions in the FROM clause as well?
0
Scott PletcherSenior DBACommented:
Hmm, not really.  CA's should appear after the FROM clause, like a JOIN.  You can even interleave JOINs and CROSS APPLYs if you want.

FROM tableA
INNER JOIN tableB
CROSS APPLY ... AS ca1
INNER JOIN tableC
CROSS APPLY ... AS ca2
0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I mean use the alias names in the FROM clause?
0
Scott PletcherSenior DBACommented:
Hmm, not sure I'm following you.  The FROM is table names, not column names, and the alias of the CA itself -- for example aan or assign_alias_names -- doesn't exist until the CA has been encountered in the SQL and processed by the parser/compiler.

Could you give me an example of what your q is?
0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
For example. The SOItemNo is one of the fields that I want to use in the JOIN statement of the FROM clause. So could I use aan.SOItemNo = [Someotherfield] in another table. Instead of writing out the LTRIM,RTRIM statement could I just use the aan.SOItemNo alias in the JOIN statement?
0
Scott PletcherSenior DBACommented:
Yes, if the CROSS APPLY appears before the JOIN.  And that's an excellent point -- my code was wrong because I didn't put the CROSS APPLY immediately after the main table to make the aliases available asap.

SELECT ...
FROM dbo.aritem AS A
CROSS APPLY (
     SELECT
         LEFT(A.fsokey, 6) AS SONumber,
         CASE WHEN LEN(A.fsokey) >= 9 THEN RIGHT(A.fsokey, 3) ELSE '' END AS SORelease,
         CASE WHEN LEN(A.fsokey) >= 9 THEN LTRIM(RTRIM(SUBSTRING(A.fsokey, 7, LEN(A.fsokey) - 9))) ELSE '' END AS SOItemNo        
 ) AS aan --assign_alias_names

--from this point on the alias names can be used, as the CROSS APPLY has already been interpreted
        INNER JOIN dbo.armast AS A1
             ON A1.fcinvoice = A.fcinvoice
        INNER JOIN dbo.soitem As S1
                   ON S1.fsono = aan.SOItemNo
        LEFT JOIN dbo.joitem As J1 ON aan.SONumber = J1.fsono AND
                                      --now this should work but didn't before because of my faulty placement of the CROSS APPLY after the JOINs in the initial code: I had meant to make the aliases available for this JOIN, I just mistakenly didn't :(
                                      aan.SOItemNo = LTRIM(J1.finumber) AND
                                      aan.SORelease = J1.fkey
        LEFT JOIN dbo.jodbom As J2 ON J1.fjobno = J2.fjobno
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.