How to add a required predicate in a DB view (not model)

[justin_molenaur2]

Someone recently asked how a HANA DATABASE view (not calculation/analytic/attribute view) can have a mandatory input parameter/variable/where clause attached to it. From this, I remembered that there is a system view (“SYS”.”EFFECTIVE_PRIVILEGES”) that requires you to pass a USER_NAME into it when you want to run a query on it. When you query without this (SELECT *), you see this error.”



Now my question is – how can I generate a database view with a mandatory predicate in the same way? This must be a super secret view (SYS.EFFECTIVE_PRIVILEGES), since no one (even SYSTEM) can see the definition of this view and I can't seem to find any documentation on how to accomplish this.

Regards,

Justin

[lbreddemann]

Ok, that's an easy answer to give but maybe a difficult one to accept:

System views are special.

They can be created via SQL, L, C++ code - all compiled right into the SAP HANA software executable. That's the reason that typically the "source code" is not visible: they are not created like this and are defined "outside the matrix of the SQL catalog".

So that way, the possible semantics can be richer, e.g. by demanding a specific WHERE condition or parameter. Other examples would be the check for a specific system privilege within the view and changing data output accordingly.

So, in short: SAP HANA core developers can practically create whatever they want.

But there is no (straightforward) way to force a SQL view to check the existence of the WHERE condition it was invoked with.

[justin_molenaur2]

Ok, I got it. So you're saying there is still magic inside the box

Regards,

Justin

[lbreddemann]

Precisely

Anything "*SYS*" can safely be considered to be magic in some way or the other.