0

I have a query that selects values from two joined tables, one table has the value to join and another table has the "format" configuration.

        SELECT @SegmentValue = COALESCE ( @SegmentValue + 
                CASE  
                    WHEN el.FillFormat = 'RIGHT' 
                    THEN LEFT ( val.CalculatedValue + REPLICATE(el.fillcharacter, el.Length) , el.Length)
                    ELSE LEFT ( val.CalculatedValue + REPLICATE(el.fillcharacter, el.Length) , el.Length)
                END 
                ,''
                )
        FROM EDI_Element  el
        INNER JOIN #EDI_Element_Values val ON

This query is having a very interesting behavior; as it is the variable that only has the very last value, so if I return 4 records like r1 , r2 r3 , r4 (I tested this by running the exact same query but with select * instead of the coalesce as I first suspected the joins) the variable will have a value of r4.

An interesting thing I found is that if I hardcode a value inside the replicate for example 100, the query works as expected.

edgar aguilar
  • 1
  • It's undefined behaviour, [as documented](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/select-local-variable-transact-sql?view=sql-server-ver15#c-antipattern-use-of-recursive-variable-assignment) and you may get different results each time, as it is dependent on the exact execution plan created by the compiler. See also https://dba.stackexchange.com/a/132709/220697 – Charlieface Feb 17 '22 at 22:38
  • Instead you should use normal aggregation techniques. In this case you want a window function such as `STRING_AGG(CASE WHEN el.FillFormat = 'RIGHT' THEN LEFT ( val.CalculatedValue + REPLICATE(el.fillcharacter, el.Length) , el.Length) ELSE RIGHT ( REPLICATE(el.fillcharacter, el.Length) + val.CalculatedValue , el.Length) END, ' ')` and I'm not sure why your existing code has the same value on both sides of the `CASE` – Charlieface Feb 17 '22 at 22:47

0 Answers0