New post
Question
· Jun 21, 2016

Use of %vid for row selection - syntax?

#SQL #Caché

Customer is experimenting with %vid variable for selecting row numbering for sql queries. Basically he's trying to implement paging functionality.

He is confused that select %vid from (select ...) returns dummy number whilst select *,%vid from (select...) returns correct data.

I believe, according to the documentation...

The phrase “SELECT * ...” does not include %vid; it must be selected explicitly: “SELECT *, %vid ...”. Also, while this feature is very convenient, especially for porting Oracle queries (this maps easily to Oracle ROWNUM), performance of queries may change as compared to TOP.

... that the syntax select *,%vid... is mandatory, but want to double check. Can anyone confirm, please?

 

Thank you!

Discussion (4)1
Log in or sign up to continue
Thomas Carroll · Jun 21, 2016

The %vid is something that you're going to need to specifically select even when using *. This is documented here - but it seems that this is the documentation you're quoting already :)

http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

I'm a little confused about what you mean when you say,

"He is confused that select %vid from (select ...) returns dummy number whilst select *,%vid from (select...) returns correct data."

Running this query might help clarify,

select Id, name, %vid From (select top 10 * from Sample.Person order by name) where %vid BETWEEN 5 and 10

The %vid is not a dummy number - in this case it is essentially to the row ID of the result that we're selecting from. In this case, it represents the integer ranking of the records alphabetized by name, but it really depends on the query. This is not the ID of the person records themselves.  

You don't necessarily need to do a "select *,%vid...", but most likely you want to select some other columns from the view besides %vid.

Does this help?

0 0
Gerd Nachtsheim · Jul 1, 2016

I also would like to understand the dummy number - what does that mean?

If you want to use %VID for pagination of large sets you might want to consider working with keysets in a snapshot rather than doing pseudo-windowing with %VID. This can become quite costly if you are enforcing an ordered output,

e.g.

SELECT *
  FROM
  (SELECT ID,Name
     FROM Sample.Person
    WHERE  Name LIKE ? )
WHERE %VID BETWEEN ? AND ?
ORDER BY Name

if your dataset is very large  this can be quite expensive if you plan to step through this pagewise.

0 0