While SQL Anywhere has supported temporary procedures and functions for a long time (at least with ASA 9 and above), views can only be created either permanently or on a per-statement level via common table expressions.

I would appreciate a CREATE TEMPORARY VIEW statement enhancement with all the reasons why temporary procedures and local temporary tables are useful:

  • They would be only visible to the current connection,
  • would not appear in the system catalog and transaction log,
  • would be dropped automatically when the connection is dropped but could be explicitly dropped beforehand.

My current use case is a complex analysis of some data which requires several complex views, so it would be quite difficult to use CTE for those, and on the other hand those views are focussed on that particular use case and won't be generally usable, so I would like to restrict them to the current connection and avoid them as permanent views.


Of course, please tell me if there is already a way to use temporary views:)

asked 24 May, 05:17

Volker%20Barth's gravatar image

Volker Barth
33.6k330485713
accept rate: 32%

edited 24 May, 05:19


I agree 100%... the WITH clause is very useful, and I use it quite often to create multiple "cascading" view definitions to divide-and-conquer a difficult query... they are easier to code and debug than derived tables embedded in the final FROM.

...BUT a separate CREATE TEMPORARY VIEW statement would be even better because it would allow you to code it once and then use it in multiple separate queries.

In particular, it would be useful for building complex adhoc queries in Foxhound, especially if it had the same "Privileges" requirement as CREATE TEMPORARY PROCEDURE: "You do not need any privilege to create temporary procedures."

In other words, any user with SELECT privileges on the underlying tables would be able to use CREATE TEMPORARY VIEW with no extra privileges.

permanent link

answered 24 May, 07:36

Breck%20Carter's gravatar image

Breck Carter
29.0k482646946
accept rate: 21%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×108
×29

question asked: 24 May, 05:17

question was seen: 67 times

last updated: 24 May, 07:36