Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

SBO Query Error: Invalid Cursor State

I have written the following query to generate the UPC number in the barcode field. It work fine in SQL 2005 Server Manager, but errors when I run the query in the Business One query environment.

Anybody know why? Thanks,

Shelly

Declare @MFG as nvarchar(6)

Declare @D1 as Numeric

Declare @D2 as Numeric

Declare @D3 as Numeric

Declare @D4 as Numeric

Declare @D5 as Numeric

Declare @D6 as Numeric

Declare @DSEQ as nvarchar(5)

Declare @D7 as nvarchar(5)

Declare @D8 as nvarchar(5)

Declare @D9 as nvarchar(5)

Declare @D10 as nvarchar(5)

Declare @D11 as nvarchar(5)

Declare @CheckDigit nvarchar(5)

Set @MFG = 751379 /Set @MFG to Manufacturers UPC Prefix/

Set @D1 = substring(@MFG,1,1)

Set @D2 = substring(@MFG,2,1)

Set @D3 = substring(@MFG,3,1)

Set @D4 = substring(@MFG,4,1)

Set @D5 = substring(@MFG,5,1)

Set @D6 = substring(@MFG,6,1)

Set @DSEQ = (SELECT max(substring(T0.[CodeBars],7,5))+1 FROM OITM T0)

Set @D7 = substring(@DSEQ,1,1)

Set @D8 = substring(@DSEQ,2,1)

Set @D9 = substring(@DSEQ,3,1)

Set @D10 =substring(@DSEQ,4,1)

Set @D11 =substring(@DSEQ,5,1)

Set @CheckDigit = right((10-(((@D2 + @D4 + @D6 + @D8 + @D10)) + 3*(@D1 + @D3 + @D5 + @D7 + @D9 + @D11))%10),1)

Select @MFG + @DSEQ + @CheckDigit

Former Member
Former Member replied

Shelly,

This is what I have for you...

DECLARE @Max NUMERIC, @NBC VARCHAR(12), @ODD INT, @EVEN INT, @CHK VARCHAR
SELECT  @Max = CAST(('751379' + MAX(SUBSTRING(ISNULL(CODEBARS,0),7,5))) AS NUMERIC) + 1 FROM OITM
SET @NBC = CAST(@Max AS VARCHAR)
SELECT @EVEN = CAST(Substring(@NBC,2,1) AS INT) + CAST(Substring(@NBC,4,1) AS INT) + CAST(Substring(@NBC,6,1) AS INT)
     + CAST(Substring(@NBC,8,1) AS INT) + CAST(Substring(@NBC,10,1) AS INT)
SELECT @ODD = (CAST(Substring(@NBC,1,1) AS INT) + CAST(Substring(@NBC,3,1) AS INT) + CAST(Substring(@NBC,5,1) AS INT)
     + CAST(Substring(@NBC,7,1) AS INT) + CAST(Substring(@NBC,9,1) AS INT) + CAST(Substring(@NBC,11,1) AS INT)) * 3
SELECT @CHK  = RIGHT(10 - ((@ODD+@EVEN) % 10),1)
SELECT @NBC + @CHK

Suda

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question