Qualify object names and CTE

michanne

Refactor takes the cte name instead of the name in the from clause:

CREATE TABLE #log_insert(
          registrant_logPk BIGINT,
          registrantPK BIGINT
        );
insert #log_insert(registrantPK,registrant_logPK) VALUES (25,20);
insert #log_insert(registrantPK,registrant_logPK) VALUES (25,33);
insert #log_insert(registrantPK,registrant_logPK) VALUES (26,21);
insert #log_insert(registrantPK,registrant_logPK) VALUES (26,36);

WITH rownum AS (
	SELECT rownum.RegistrantPK,rownum.registrant_logPk, 
		ROW_NUMBER() OVER(PARTITION BY rownum.registrantPK ORDER BY rownum.RegistrantPK,rownum.registrant_logPk) AS rw
	FROM #log_insert
	)
SELECT    
	b.registrant_logPk AS last_log,
	a.registrant_logPk AS pk,
	c.registrant_logPk AS next_log
FROM rownum a
JOIN rownum b ON a.rw = b.rw + 1
	AND a.registrantPK = b.registrantPK
JOIN rownum c ON a.rw + 1 = c.rw 
	AND a.registrantPK = c.registrantPK

Thanks

Chris Auckland

Thanks for your post.

Unfortunately the problem here is that 'Qualify Object Names' does not actually support temporary tables. The current behaviour is to simply ignore the temp table.

We have an open request to support tempoary tables in a future version. For your reference the bug tracking number for this request is SR-534.