With句が一度しか使えない原因を知りたい

Question

前提・実現したいこと

WITH句はサブクエリを共通テーブルとして定義することができると聞いて、早速やってみたのですが
1度目のselect利用は走るものの、2度目以降のselectではエラーになってしまいます。
原因分かる方おられたら教えてください。

発生している問題・エラーメッセージ

(1 行処理されました)
メッセージ 208、レベル 16、状態 1、行 24
オブジェクト名 'w_pklist' が無効です。

該当のソースコード

WITH w_pklist As (
	SELECT
		  tbls.name            AS table_name
		 ,key_const.name       AS constraint_name
		 ,idx_cols.key_ordinal AS key_ordinal
		 ,cols.name            AS col_name
	FROM
		sys.tables AS tbls
			 INNER JOIN sys.key_constraints AS key_const ON
					 tbls.object_id = key_const.parent_object_id
				 AND key_const.type = 'PK'
			 INNER JOIN sys.index_columns AS idx_cols ON
					 key_const.parent_object_id = idx_cols.object_id
				 AND key_const.unique_index_id  = idx_cols.index_id
			 INNER JOIN sys.columns AS cols ON
					idx_cols.object_id = cols.object_id
				 AND idx_cols.column_id = cols.column_id 
 )

select * from w_pklist
select * from w_pklist  --オブジェクト名 'w_pklist' が無効です。

補足情報（FW/ツールのバージョンなど）

SQL Server 2016 Express

Comments

[m.ts10806]

なぜ同じSELECTを連続で実行してるのでしょうか。

[yokatone]

想像では、何度/どこでselectを実行しようが同じ結果が得られるはずであるためです。。

[m.ts10806]

次からでけっこうですので、それも「想定する結果」 なので推測であっても記載いただいた方がアドバイスもしやすいです。
あとは「どこの何を読んでそう思ったのか」ですね。
結局は「問い合わせ」の一種なので、副問い合わせの外だしというとイメージしやすいと思います。
あとは変数として「１文の中で」使い回しがきくという点。
ビューのように作ってあるものではなく、その場その場で定義するオブジェクトみたいな感じです。

Answer 1

何故って、文法的にそうだとしか言いようがありません。
With句は句であって文ではありません。

上はWith句を含むSQL文を実行
下はWith句を含まないSQL文を実行

With句を含むSQL文をもう一度書けば良いだけです。
独立させたい場合は、テーブル値変数とか一時テーブルを使いましょう。

Comments

[hihijiji]

リロードしてからペーストしたのに、かぶった orz
同じ時間に3人とは…

[m.ts10806]

そして私にだけつく低評価。
雑な内容だからかなぁ。

[yokatone]

なるほど。withは句であって、whereやselectの一種であるということなんですね。
確かにテーブル変数を使う方が適切ですね。ありがとうございました！

Answer 2

;で区切られていませんけど、暗黙的に一度区切られています。

なので2回目のselectではwithはありませんから、w_pklistが参照できずにエラーです。

※SSMS使用されているんだと思いますけど、複数のSQLを連続で別々に実行できるSSMSが特殊なんです。

Answer 3

一つ目のSELECTでいったん実行されて結果が返ることになります。
要はそこでひとつ完結したということになります。
(別回答にもあるように文がそこで切れるという意味です)

PostgreSQL環境でも同じようにrelation "エイリアス" does not existになりました。

Comments

[sazi]

低評価はしたわけではありませんが、多分コミットというのが低評価の理由だと思います。
m.ts10806さんのコミットはトランザクションコミットとは別なニュアンスだと思いますが、誤解されて低評価になっているのでしょう。

[m.ts10806]

確かに。表現が不適切でしたね
調整します

[m.ts10806]

「トランザクション」とは違うなあと思って適切な表現が思い浮かばなかったので続けて「スコープ」とかそれっぽい表現並べてみたのですけど、余計に分かりにくくなったみたいですね。
「文が切れる」だけで良かった…

[hihijiji]

言っていることはたいして違わないのに低評価が付くのは怖いですね。
低評価を付ける場合は必ず理由が必要とか、評価者のscoreもマイナスにするとか何か対策がほしいですね。

[m.ts10806]

低評価したときに「理由をコメントして云々」って吹き出しが出るんですけどね。
余程の明らかなものでない限りはコメントほしいもんです。私怨とかでつけても技術的な向上まったく見込めないわけですし。
大抵そういう人って一度低評価つけると編集して通知来ても無視するので残り続けるんですよね(そのせいで回答者が減ってる感じも否めない)。
saziさんのご指摘は本当に助かりました。ちょっと表現に自身なかったのは確かなので。

[m.ts10806]

なんか増えてる。熱心なアンチに狙われてる気がする。