Query error

[Former Member]

If I execute this in DB A, it shows no value, but if i execute it in DB B, it shows error like

Msg 537, Level 16, State 3, Line 1

Invalid length parameter passed to the LEFT or SUBSTRING function.

Why error message is coming in DB B ?

SELECT distinct (sum(T1.Debit-T1.Credit)*-1),T0.Segment_1,substring(Acctname,1,len(acctname)-7)FROM OACT T0 INNER JOIN JDT1 T1 ON T0.AcctCode = T1.Account WHERE T1.[RefDate] >='2009-4-1 00:00:00.000' and T1.[RefDate] <='2010-3-31 00:00:00.000' and (Fathernum='5210900000'or Fathernum='5210800000'or FatherNum ='5210100000'or FatherNum = '5210200000'or FatherNum = '5210300000'or FatherNum ='5210400000'or FatherNum = '5210500000'or FatherNum = '5210600000'or FatherNum = '5210700000')and (substring(Acctname,1,len(acctname)-7)='" & objmatrix.Columns.Item("1").Cells.Item(a).Specific.value & "' or substring(Acctname,1,len(acctname)-8)='" & objmatrix.Columns.Item("1").Cells.Item(a).Specific.value & "') group by T0.Segment_1 , substring(Acctname,1,len(acctname)-7)

[Former Member]

Hi,

Instead of substring(Acctname,1,len(acctname)-7), try something like Left(T0.AcctName,10).

At least you may add T0 to your original query to try. Make sure all account names have enough length.

Thanks,

Gordon

[Former Member]

Length are more than 10 , i have checked

[Former Member]

Hello,

I was thinking in your problem, and got a following idea:

cerate an SQL stored procedure for your query, call it with parameters. The parameters are the matrix cells values.

YOu can test your procedure by exec ,[param2] command, and during testing you can use print command to check the inner steps.

Regards

János

[Former Member]

Actually even if matrix cell values are not matching, it should not give any exception, I am pasting the function code below

Private Sub Loadindirectexpenses(ByVal formuid, ByVal fathernum, ByVal linenum)

Try

Dim i, a, b As Integer

objmatrix = objform.Items.Item("3").Specific

objrs = objAddOn.objCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset) '

objrs.MoveFirst()

b = linenum + 82

Dim total, value As Decimal

total = 0

value = 0

For a = linenum To b

'Added Query ...

Dim str As String = "SELECT distinct (sum(T1.Debit-T1.Credit)*-1),T0.Segment_1,substring(Acctname,1,len(acctname)-7)FROM OACT T0 INNER JOIN JDT1 T1 ON T0.AcctCode = T1.Account WHERE T1.[RefDate] >='2010-4-1 00:00:00.000' and T1.[RefDate] <='2011-3-31 00:00:00.000' and (T0.[Fathernum]='5210900000'or T0.[Fathernum]='5210800000'or T0.[FatherNum] ='5210100000'or T0.[FatherNum] = '5210200000'or T0.[FatherNum] = '5210300000' or T0.[FatherNum] = '5210400000' or T0.[FatherNum]='5210500000' or T0.[FatherNum]='5210600000' or T0.[FatherNum]='5210700000')and (substring(Acctname,1,len(acctname)-7)='" & objmatrix.Columns.Item("1").Cells.Item(a).Specific.value & "' or substring(Acctname,1,len(acctname)-8)='" & objmatrix.Columns.Item("1").Cells.Item(a).Specific.value & "')group by T0.Segment_1 , substring(Acctname,1,len(acctname)-7) "

objrs.DoQuery(str)

total = 0

For j As Integer = 2 To objmatrix.Columns.Count - 2

objrs.MoveFirst()

For i = 0 To objrs.RecordCount - 1

If objmatrix.Columns.Item(j).Title = objrs.Fields.Item(1).Value And objmatrix.Columns.Item("1").Cells.Item(a).Specific.value = objrs.Fields.Item(2).Value Then

objmatrix.Columns.Item(j).Cells.Item(a).Specific.value = objrs.Fields.Item(0).Value

End If

If Not objrs.EoF Then

objrs.MoveNext()

Else

Exit For

End If

Next

If j <> 19 And j <> 20 Then

'Added by Rupa 26/05/11

'If j <> objmatrix.Columns.Count - 2 And j <> objmatrix.Columns.Count - 1 Then

If objmatrix.Columns.Item(j).Cells.Item(a).Specific.value = "" Then

value = 0

Else

value = objmatrix.Columns.Item(j).Cells.Item(a).Specific.value

End If

total = total + value

End If

Next

objmatrix.Columns.Item("19").Cells.Item(a).Specific.value = total

Next

Catch ex As Exception

' MessageBox.Show(ex.Message)

End Try

End Sub

[Former Member]

Any suggetion?

[Former Member]

Actually Left can not be used, as the i want to have the account names without segment names, means say

account names are like

F.A-SECURITY ALARAM SYSTEM (GENE)

INVENTORIES (INTC )

So I want to dicard (GENE) part or (INTC) part

[Former Member]

How to change it with indexOf function, in SQL server 2008 can i use tht?

[Former Member]

SELECT distinct

(sum(T1.Debit-T1.Credit)*-1),

T0.AcctName,

T0.Segment_1,substring(Acctname,1,CHARINDEX ('(', T0.AcctName)-1)

FROM OACT T0 INNER JOIN JDT1 T1

ON T0.AcctCode = T1.Account WHERE

T1.[RefDate] >='2010-4-1 00:00:00.000'

and T1.[RefDate] <='2011-3-31 00:00:00.000'

and (T0.[Fathernum]='5210900000'or T0.[Fathernum]='5210800000'or

T0.[FatherNum] ='5210100000'or T0.[FatherNum] = '5210200000'or

T0.[FatherNum] = '5210300000' or T0.[FatherNum] = '5210400000'

or T0.[FatherNum]='5210500000' or T0.[FatherNum]='5210600000' or

T0.[FatherNum]='5210700000')

and

(substring(Acctname,1,CHARINDEX ('(', T0.AcctName)-1)='" & objmatrix.Columns.Item("1").Cells.Item(a).Specific.value & "'

or substring(Acctname,1,CHARINDEX ('(', T0.AcctName)-2)='" & objmatrix.Columns.Item("1").Cells.Item(a).Specific.value & "')

group by T0.Segment_1 , substring(Acctname,1,CHARINDEX ('(', T0.AcctName)-1),T0.AcctName

This one i also giving same error !!!

[Former Member]

What is this?

objmatrix.Columns.Item("1").Cells.Item(a).Specific.value

[Former Member]

SELECT distinct

(sum(T1.Debit-T1.Credit)*-1),T0.Segment_1,

substring(Acctname,1,len(acctname)-7)

FROM OACT T0 INNER JOIN JDT1 T1 ON T0.AcctCode = T1.Account

WHERE T1.[RefDate] >='2010-4-1 00:00:00.000'

and T1.[RefDate] <='2011-3-31 00:00:00.000'

and

(

T0.[Fathernum]='5210900000'or

T0.[Fathernum]='5210800000'or

T0.[FatherNum] ='5210100000'or

T0.[FatherNum] = '5210200000'or

T0.[FatherNum] = '5210300000'

--or T0.[FatherNum] = '5210400000'

--or T0.[FatherNum] = '5210500000'

--or T0.[FatherNum] = '5210600000'

--or T0.[FatherNum] = '5210700000'

)

and (substring(Acctname,1,len(acctname)-7)=

'" & objmatrix.Columns.Item("1").Cells.Item(a).Specific.value & "' or

substring(Acctname,1,len(acctname)-8)=

'" & objmatrix.Columns.Item("1").Cells.Item(a).Specific.value & "')

group by T0.Segment_1 ,

substring(Acctname,1,len(acctname)-7)

This is giving no error, if i comment the lat four fathernums.

But

SELECT distinct

(sum(T1.Debit-T1.Credit)*-1),T0.Segment_1,

substring(Acctname,1,len(acctname)-7)

FROM OACT T0 INNER JOIN JDT1 T1 ON T0.AcctCode = T1.Account

WHERE T1.[RefDate] >='2010-4-1 00:00:00.000'

and T1.[RefDate] <='2011-3-31 00:00:00.000'

and

(

T0.[Fathernum]='5210900000'or

T0.[Fathernum]='5210800000'or

T0.[FatherNum] ='5210100000'or

T0.[FatherNum] = '5210200000'or

T0.[FatherNum] = '5210300000'

or T0.[FatherNum] = '5210400000'

or T0.[FatherNum] = '5210500000'

or T0.[FatherNum] = '5210600000'

or T0.[FatherNum] = '5210700000'

)

and (substring(Acctname,1,len(acctname)-7)=

'" & objmatrix.Columns.Item("1").Cells.Item(a).Specific.value & "' or

substring(Acctname,1,len(acctname)-8)=

'" & objmatrix.Columns.Item("1").Cells.Item(a).Specific.value & "')

group by T0.Segment_1 ,

substring(Acctname,1,len(acctname)-7)

This is giving error

Msg 537, Level 16, State 3, Line 1

Invalid length parameter passed to the LEFT or SUBSTRING function.

[Former Member]

Hello,

MAybe the accounts belongs to last 4 fathers has a shorter account number or description than others, so maybe it is not 10 chars long so substring fails because the name or code of the account is less than 6 chars

run the following query

select acctcode, acctname, len(acctcode), len(acctname) from oact where

T0.Fathernum='5210900000'or

T0.Fathernum='5210800000'or

T0.FatherNum ='5210100000'or

T0.FatherNum = '5210200000'or

T0.FatherNum = '5210300000'

or T0.FatherNum = '5210400000'

or T0.FatherNum = '5210500000'

or T0.FatherNum = '5210600000'

or T0.FatherNum = '5210700000'

Regards

János

[Former Member]

I have run

select

T0.[FatherNum],min(LEN(T0.AcctName))

from OACT T0 where T0.[FatherNum] = '5210400000'

group by T0.[FatherNum]

select

T0.[FatherNum],min(LEN(T0.AcctName))

from OACT T0 where T0.[FatherNum] = '5210500000'

group by T0.[FatherNum]

select

T0.[FatherNum],min(LEN(T0.AcctName))

from OACT T0 where T0.[FatherNum] = '5210600000'

group by T0.[FatherNum]

select

T0.[FatherNum],min(LEN(T0.AcctName))

from OACT T0 where T0.[FatherNum] = '5210700000'

group by T0.[FatherNum]

All four

Result is

16

26

12

21

[former_member1269712]

Hi,

SELECT distinct 
(sum(T1.Debit-T1.Credit)*-1),T0.Segment_1,
substring(Acctname,1,len(acctname)-7)
FROM OACT T0 INNER JOIN JDT1 T1 ON T0.AcctCode = T1.Account 
WHERE T1.[RefDate] >='2010-4-1 00:00:00.000' 
and T1.[RefDate] <='2011-3-31 00:00:00.000' 
and 
(
T0.[Fathernum]='5210900000'or 
T0.[Fathernum]='5210800000'or 
T0.[FatherNum] ='5210100000'or 
T0.[FatherNum] = '5210200000'or 
T0.[FatherNum] = '5210300000'
or T0.[FatherNum] = '5210400000'
or T0.[FatherNum] = '5210500000'
or T0.[FatherNum] = '5210600000'
or T0.[FatherNum] = '5210700000'
)
and (substring(Acctname,1,len(acctname)-7)=
'" & objmatrix.Columns.Item("1").Cells.Item(a).Specific.value & "' or 
substring(Acctname,1,len(acctname)-8)=
'" & objmatrix.Columns.Item("1").Cells.Item(a).Specific.value & "')
group by T0.Segment_1 , 
substring(Acctname,1,len(acctname)-7)

Here is following problem in above query

& objmatrix.Columns.Item("1").Cells.Item(a).Specific.value &

Instead of this put actual account names. This is your sdk condition for filtering.

Regards,

Sachin