November 13, 2009 at 1:56 pm
Hi Guys and Gals (my apologese to Flo), I am receiving the following msg:
Invalid length parameter passed to the SUBSTRING function.
I have bolded the offending line of code but cannot figure out how to fix it. The developer stores all of their amount fields as Varchar so conversion is necessary.
SELECT Right(Plastic_No,19) as Plastic_No, Last_Name,
First_Name, ISNULL(Middle_Ini,' ') as Middle_Ini, Address1, Address2,
City, [State], Postal_Code,
left('00000000000', (11-len(ISNULL(Cast(Replace(O_Bal,'.','') as varchar(11)),'000'))))+ Cast(ISNULL(Replace(Outstanding_Bal,'.',''),'000') as varchar(11)) as Outstanding_Bal,
Cast(Left('00000', (5-Len(IsNull(P_Amount, '000')))) as Varchar(5)) +
Cast(IsNull(P_Amount,'000') as varchar (5)) as P_Amount,
Cycle_Date
FROM AMEX.TI_Balboa_Monthly_Fees
Any ideas?
Thank you,
Trudye
November 13, 2009 at 2:08 pm
Can you post sample data along with...
For testing, I have tried with following variables and works fine.
declare @o_bal decimal(10,3)
declare @Outstanding_Bal decimal(10,3)
declare @P_Amount decimal(10,3)
set @o_bal=10
set @Outstanding_Bal = 5
set @P_Amount = 5.5
SELECT
left('00000000000', (11-len(ISNULL(Cast(Replace(@O_Bal,'.','') as varchar(11)),'000'))))+ Cast(ISNULL(Replace(@Outstanding_Bal,'.',''),'000') as varchar(11)) as Outstanding_Bal,
Cast(Left('00000', (5-Len(IsNull(@P_Amount, '000')))) as Varchar(5)) + Cast(IsNull(@P_Amount,'000') as varchar (5)) as P_Amount
November 14, 2009 at 10:03 am
Hey Jus, thank you so much for responding. I used your code and I pasted a value from the first record in the file and it bombed.
Please try: set @P_Amount = 000000861
set @Outstanding_Bal = 000001037.11
O_bal and Oustanding_Bal are the same field, I was trying to shorten the fieldnames.
Please let me know if you get the same result?
Thanks again
November 14, 2009 at 10:27 pm
I have to ask. Why does the developer store numeric values as characters? That is simply rediculous. Right there with storing dates as characters. Almost every mondern database has the proper data types to store numeric and datetime values appropriately.
November 16, 2009 at 12:22 pm
If the length of the value of the P_Amount column is > 5, you will get this error because you are passing a negative number into the LEFT function.
SELECT Cast(Left('00000', (5-Len(IsNull('000000', '000')))) as Varchar(5))
gives you the error because LEN( '000000' ) = 6 and 5 - 6 = -1. LEFT only takes positive integers.
November 17, 2009 at 5:12 am
The above error occurs when ever you pass a negative value to the substring function.
EX:-
select substring('asdf',1,-1)
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
[font="Times New Roman"]Anil Kubireddi[/font]
November 19, 2009 at 4:44 pm
Thanx so much everyone. Sorry it took me soooo long to get back to you but they have me busier than a one legged man in a hop scotch contest.
Be well,
Trudye
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply