locked
Character Field Length RRS feed

  • Question

  • Recently SQL changed something. For over 13 years this code has worked and now suddenly is wrks differently.

    isnull(MAX(CASE WHEN ftr_opts.ftrcode = 'rpt_OutsideEdge' AND opt_opts.optCode = '1' THEN 'Keep' END),'Suppress') AS OutsideEdge

    This code has always returned "Keep" or "Suppress", now suddenly out of the blue it returns "Keep" or "Supp"

    This is a problem since i have Crystal Reports looking for "Keep" or "Suppress". Now NONE of my suppression formulas work.

    Does  anyone know why SQL recently changed in the way it processes this?

    Tuesday, June 23, 2020 4:57 PM

All replies

  • Since you did not cast it to a size, SQL Server looks at the first set of results and sets the size based on those results.  Likely your data has changed.

    You should always cast fixed values like that to a size to make sure that does not happen:

    CAST(isnull(MAX(CASE WHEN ftr_opts.ftrcode = 'rpt_OutsideEdge' AND opt_opts.optCode = '1' THEN 'Keep' END),'Suppress') AS VARCHAR(10)) AS OutsideEdge

    • Proposed as answer by Olaf HelperMVP Wednesday, June 24, 2020 5:29 AM
    Tuesday, June 23, 2020 7:20 PM