Error when updating

I get the following error when I execute the statement below. 'Invalid
length parameter passed to the substring function'
Update Tbl_Txn_UT
Set Fxd_Units =
CASE
WHEN TxnUnitsBase is Null and (TxnUnits like '%,%' or TxnUnits like
'%M%' and TxnUnitsDur like '%M%')
THEN Cast(left(TxnUnitsDur,len(TxnUnitsDur)-1)as Float)/15
WHEN TxnUnitsDur is Null and (TxnUnits like '%,%' or TxnUnits like
'%M%')
THEN TxnUnitsBase
WHEN (TxnUnits like '%,%' or TxnUnits like '%M%')
THEN Cast(left(TxnUnitsDur,len(TxnUnitsDur)-1)as Float)/15 +
TxnUnitsBase
Else TxnUnits
END
Where TxnUnits is not Null
It took a while to get this code correct with help and now evidently one of
the answers from the math causes an error. Any help is appreciated.Hi
Posting DDL an example data would help to see what you are trying to achive
and help re-create the problem. The statement is assuming that TxnUnitsDur i
s
at least one character which could is probably causing the problem.
John
"Job" wrote:

> I get the following error when I execute the statement below. 'Invalid
> length parameter passed to the substring function'
> Update Tbl_Txn_UT
> Set Fxd_Units =
> CASE
> WHEN TxnUnitsBase is Null and (TxnUnits like '%,%' or TxnUnits like
> '%M%' and TxnUnitsDur like '%M%')
> THEN Cast(left(TxnUnitsDur,len(TxnUnitsDur)-1)as Float)/15
> WHEN TxnUnitsDur is Null and (TxnUnits like '%,%' or TxnUnits like
> '%M%')
> THEN TxnUnitsBase
> WHEN (TxnUnits like '%,%' or TxnUnits like '%M%')
> THEN Cast(left(TxnUnitsDur,len(TxnUnitsDur)-1)as Float)/15 +
> TxnUnitsBase
> Else TxnUnits
> END
> Where TxnUnits is not Null
>
> It took a while to get this code correct with help and now evidently one o
f
> the answers from the math causes an error. Any help is appreciated.
>
>|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:B6369D9E-3B4A-4F9B-A443-13463C76635C@.microsoft.com...
> Hi
> Posting DDL an example data would help to see what you are trying to
> achive
> and help re-create the problem. The statement is assuming that TxnUnitsDur
> is
> at least one character which could is probably causing the problem.
> John
> "Job" wrote:
>|||Ju
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:B6369D9E-3B4A-4F9B-A443-13463C76635C@.microsoft.com...
> Hi
> Posting DDL an example data would help to see what you are trying to
> achive
> and help re-create the problem. The statement is assuming that TxnUnitsDur
> is
> at least one character which could is probably causing the problem.
> John
> "Job" wrote:
>|||Hi
You seem to have some problem posting?
John
"Job" <Job@.nomail.com> wrote in message
news:uUH$jpCoFHA.708@.TK2MSFTNGP09.phx.gbl...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:B6369D9E-3B4A-4F9B-A443-13463C76635C@.microsoft.com...
>|||John, your correct. I included the case statement if len(TxnUnitsDur )
= 0 then TxnUnitsBase and it worked perfectly.
Cheers!
Job wrote:
> Ju
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:B6369D9E-3B4A-4F9B-A443-13463C76635C@.microsoft.com...
>
>
>