Having a dense/senior moment today...
I have an (inherited) SQL query running against a cte that now produces rows numbered from 1 to 16384. I'm expecting it to generate column letters in the range "A" to "XFC" (Previously generated "A" to "ZZ")
This should work
SELECT l, CASE WHEN l >= 703 THEN Char(65 + (l - 703) / 702 % 702) ELSE '' END
+ CASE WHEN l >=27 then Char(65 + (l - 27) / 26 % 26) ELSE '' END
+ Char(65 + (l - 1) % 26)
FROM eFinal
WHERE l < 16384
ORDER BY l;
In fact, it
does work - UNTIL we get to row 6787 - instead of returning "JAA" it reverts to "IAA" (through to "IAZ" and from there does some weird and wonderful things (including non-ascii characters))
I can get around it with
SELECT l, (CASE WHEN l >= 6787 THEN Char((65 + (l - 703) / 702 % 702) + 1)
WHEN l >= 703 THEN Char(65 + (l - 703) / 702 % 702) ELSE '' END)
+ (CASE WHEN l > 27 THEN (Char(65 + (l - 27) / 26 % 26)) ELSE '' END)
+(Char(65 + (l - 1) % 26))
FROM eFinal
WHERE l < 16384
ORDER BY l;
Which works all the way to "XFC".
What I don't understand is... why?
I'm struggling to understand the significance of 6786 as a key number - although I did get overexited when I realised it's 26 x 255 but
l
is a bigint so not relevant?
What I have tried:
Full code to reproduce the behaviour
;with
cte1 AS (select n from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1) ) AS X(n))
,cte2 AS (SELECT a.n FROM cte1 a CROSS JOIN cte1 b)
,cte3 AS (SELECT a.n FROM cte1 a CROSS JOIN cte2 b)
,cte4 AS (SELECT a.n FROM cte2 a CROSS JOIN cte3 b )
,eFinal AS (SELECT l = ROW_NUMBER() OVER (ORDER BY a.n) FROM cte2 a CROSS JOIN cte3 b )
SELECT l, CASE WHEN l >= 703 THEN Char(65 + (l - 703) / 702 % 702) ELSE '' END
+ CASE WHEN l >=27 then Char(65 + (l - 27) / 26 % 26) ELSE '' END
+ Char(65 + (l - 1) % 26)
FROM eFinal
where l BETWEEN 6780 AND 6790
ORDER BY l;