none
Same code, one it's ok the other one return me Invalid lenght

    Question

  • Guys, today I'm getting crazy with my SQL. I got this code:

    SELECT subString([LINE KEY],charindex(':',[Line Key])+1,6) as period, 
    
    
    [Elements], LEFT([Elements],charindex (',',[Elements])-1) as element,
    cast(substring([Elements],charindex (',',[Elements])+1,10) as decimal(10,2)) as charge
      FROM [Control].[dbo].[ElemFin ]

    It works perfectly. Now I got this other code:

    SELECT subString([LINE KEY],charindex(':',[Line Key])+1,6) as period, 
    
    
    [Elements], LEFT([Elements],charindex (',',[Elements])-1) as element,
    cast(substring([Elements],charindex (',',[Elements])+1,10) as decimal(10,2)) as charge
      FROM [reporting].[dbo].[CorrectElements]

    That returns the error,

    Msg 537, Level 16, State 2, Line 1

    Invalid length parameter passed to the LEFT or SUBSTRING function.

    I checked the columns and are all the same varchar(50). All of them. Now, if the code is identical what can be? Because it's Friday?

     

    Friday, August 01, 2014 2:11 PM

Answers

  • Guys, I found out the mistake. If I pass row without comma it returns 0 and this is not allowed using Length.
    • Marked as answer by DIEGOCTN Sunday, August 03, 2014 1:33 PM
    Friday, August 01, 2014 4:20 PM

All replies

  • Check your data in these columns. You may have null  or other special characters in your data.
    Friday, August 01, 2014 2:16 PM
    Moderator
  • Its looks like a data issue. the row might be missing something. Check your data once.

    --Prashanth


    Friday, August 01, 2014 2:16 PM
  • I checked, the error comes from:

    LEFT([Elements],charindex (',',[Elements])-1) as element

    Friday, August 01, 2014 2:17 PM
  • There is missing ',' in the row.

    for example,

    declare @elements varchar(10)='12342344'
    select LEFT(@elements,charindex (',',@elements)) as element

    --Prashanth

    Friday, August 01, 2014 2:21 PM
  • Hi,

    The expression charindex (',',[Elements])-1 may be returning null or negative value.. Please check whether the charindex (',',[Elements]) throws any number at all.

    Regards,

    Brindha.

    Friday, August 01, 2014 2:22 PM
  • CASE WHEN charindex (',',[Elements])-1 > 0 THEN [Elements], LEFT([Elements],charindex (',',[Elements])-1) ELSE [Elements] END AS element,

    Replace your elements with this, it checks to make sure there's a comma in there.
    Friday, August 01, 2014 2:28 PM
  • Guys, I found out the mistake. If I pass row without comma it returns 0 and this is not allowed using Length.
    • Marked as answer by DIEGOCTN Sunday, August 03, 2014 1:33 PM
    Friday, August 01, 2014 4:20 PM
  • This is one reason we do not like to parse strings in SQL. A good SQL programmer has defined the encoding in the column with a "CHECK( <column name> LIKE <pattern>)" constraint to prevent bad data from getting into it. This also helps the optimizer. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, August 02, 2014 2:15 PM
  • This is one reason we do not like to parse strings in SQL. A good SQL programmer has defined the encoding in the column with a "CHECK( <column name> LIKE <pattern>)" constraint to prevent bad data from getting into it. This also helps the optimizer. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    You are right, unfortuanetely the data comes from vba code that come from csv that come from Oracle. And I'm the person supposed to clean everything, to build and use a SSIS package that run nightly using Agent and that build deploy several cube based on this (and others) database...and sometime I run into these kind of issues...but that's ok. 
    Saturday, August 02, 2014 2:46 PM
  • Saturday, August 02, 2014 5:40 PM
    Moderator
  • Hi DIEGOCTN

    If I understand correctly this issue is close and you got the the answer (ye from yourself :-) )

    You can mark your own response if this the only answer (at the same time you can vote to other usefull response that help you in the way).

    Please close the thread, by marking at least one answer, if this issue is close :-)


    [Personal Site] [Blog] [Facebook]signature

    Saturday, August 02, 2014 5:43 PM
    Moderator