Post reply

Invalid length parameter passed to the LEFT or SUBSTRING function.

  • July 10, 2019 at 1:07 pm

    #3661568

    Am creating a view as below:

    CREATE VIEW VW_FIN315_FEES (FORM_NM, FORM_VAR_NM, FORM_TEST_VAL, FORM_LINE1, FORM_YR) AS

    select f.form_nm,ff.form_var_nm,FF.form_test_val,f.form_line1,f.form_yr

    from s1for_formula f,S1FOR_FORM_FIELD FF

    where F.FORM_YR=FF.FORM_YR

    AND substring(f.form_line1, charindex('{', f.form_line1)+1,charindex('}', f.form_line1)-2)=ff.form_var_nm

    ;

     

    while works well:

    But when I do,   select count(*) from VW_FIN315_FEES;

     

    Msg 537, Level 16, State 2, Line 1

    Invalid length parameter passed to the LEFT or SUBSTRING function.

     

    Any pointers how to fix this error ?

     

    Many Thanks

     

     

  • July 10, 2019 at 1:21 pm

    #3661574

    You could be missing some curly brackets from column form_line1.

    Try running this to see if there are any invalid values in column form_line1

    select f.form_line1
    from s1for_formula f
    where f.form_line1 NOT LIKE '%{%}%'
    or charindex('{',f.form_line1) > charindex('}',f.form_line1)

     

  • July 10, 2019 at 1:22 pm

    #3661575

    antosamson wrote:

    AND substring(f.form_line1, charindex('{', f.form_line1)+1,charindex('}', f.form_line1)-2)=ff.form_var_nm

    Any pointers how to fix this error ?   

    obviously, there is/are records without '}'  or  charindex('}', f.form_line1)-2 < 0

    check the data or use "case" or iif

    example:

     AND substring(f.form_line1

              ,charindex('{', f.form_line1)+1

             ,iif(charindex('}', f.form_line1)-2<0,0,charindex('}', f.form_line1)-2) 
              )

            =ff.form_var_nm

    but it's for you to decide what to do with such records

     

  • July 10, 2019 at 1:39 pm

    #3661577

     

    Thanks a lot for your assistance !!

  • July 10, 2019 at 1:39 pm

    #3661578

     

    Thank you...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply