|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Error when updatinglength 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 is at least one character which could is probably causing the problem. John Show quote "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 of > the answers from the math causes an error. Any help is appreciated. > > >
Show quote
"John Bell" <jbellnewspo***@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: > >> 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
You seem to have some problem posting? John Show quote "Job" <J**@nomail.com> wrote in message news:uUH$jpCoFHA.708@TK2MSFTNGP09.phx.gbl... > > "John Bell" <jbellnewspo***@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: >> >>> 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. >>> >>> >>> > > Ju
Show quote "John Bell" <jbellnewspo***@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: > >> 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. >> >> >> John, your correct. I included the case statement if len(TxnUnitsDur )
= 0 then TxnUnitsBase and it worked perfectly. Cheers! Job wrote: Show quote > Ju > "John Bell" <jbellnewspo***@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: >> >> >>>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. >>> >>> >>> > > >  |
|||||||||||||||||||||||
Other interesting topics