%VID Question

Caché

I'm Trying to create a "Windowing Function" In Cache similar to SQL Server. I'm trying to create a Row Number for every account number (Example Below). I know I can use the %Vid function to create the Row Number but I need the row number to reset with every new account number. In SQL Server it would be Row_Number over (Partition by account number order by account number). and Example of what I am trying to create is below.

Row Number          Account Number

1                                             1

2                                             1

3                                             1

1                                             2

2                                             2

1                                             3

1                                             4

  • 0
  • 0
  • 217
  • 0
  • 1

Answers

You may create your private row count by group. 

Class DC.any [ Abstract ]
{
ClassMethod MyVid(group = "") As %String [ SqlName = MyVid, SqlProc ]
{
 if group="" kill %myVid quit 0  ;;initialize it
  quit $I(%myVid(group))
}

 .

how to use it:

SELECT DC.MyVid(company) RowNumber, Company,Company->Name, Name
FROM Sample.Employee where DC.MyVid()=0 /* static condition for init */
order by 2

 .

looks like this:

RowNumber

Company

Name

Name

1

1

O' KwalLateral Group Ltd.

Moon,Mary Q.

2

1

O' KwalLateral Group Ltd.

Ximines,Alice Z.

3

1

O' KwalLateral Group Ltd.

Malynko,Greta H.

4

1

O' KwalLateral Group Ltd.

Lubbar,Emily Q.

1

2

InterPlex Holdings Inc.

Tesla,Kenny W.

2

2

InterPlex Holdings Inc.

Jones,Valery N.

3

2

InterPlex Holdings Inc.

Baker,Samantha D.

1

3

GlobaSys LLC.

Quixote,Marvin C.

2

3

GlobaSys LLC.

Xerxes,Violet Y.

3

3

GlobaSys LLC.

Adams,Kim W.

4

3

GlobaSys LLC.

Ubertini,Roberta N.

5

3

GlobaSys LLC.

Jackson,Buzz V.

6

3

GlobaSys LLC.

Clinton,Keith C.

7

3

GlobaSys LLC.

Isaksen,Juanita T.

8

3

GlobaSys LLC.

Solomon,Imelda Z.

1

4

SynerMatix Associates

Humby,Olga A.

2

4

SynerMatix Associates

Orwell,Maureen R.

3

4

SynerMatix Associates

O'Donnell,Ed Q.

4

4

SynerMatix Associates

Orlin,Mary D.

5

4

SynerMatix Associates

Vanzetti,Pam V.

6

4

SynerMatix Associates

Noodleman,Chris O.