AllAccepted answers
Robert Cemper · Jun 1, 2019

IF you have seen the "Light weight Excel"  and its  Open Exchange  Repository
then all you have to do is

- open your file 

- use your file

execute OnPage method (from example or your personal  variation of it)

- close your file

BINGO!

to make it easier the referred method here:

ClassMethod OnPage() As %Status
 {
 set sqlStatement="SELECT TOP 23 ID,Name,%ODBCOUT(DOB) DOB,SSN FROM Sample.Person"
    ,query = ##class(%ResultSet).%New()
    ,sc = query.Prepare(sqlStatement)
  set:sc sc=query.Execute()
  quit:'sc sc
  set cols=query.GetColumnCount()
  write "<table border=1>",!,"<tr>"
  for col=1:1:cols {
   write "<th align=left><b>"_query.GetColumnHeader(col)_"</b></th>"
   }
  while query.Next() {
   write "</tr>",!,"<tr>"
    for col=1:1:cols {
      write "<td>"_query.GetData(col)_"</td>"
     }
   }
  write "</tr>",!,"</table>"
  quit $$$OK
 }
 
Robert Cemper · May 31, 2019

OK. I experienced that scenario quite often. sad

Suggestion to verify that indices will help you.
- Take a typical class definition, make a copy without storage definition (or remove it)
- compile it with Chaché Default storage and  run  INSERT INTO new.table SELECT * from old.table a flat table scan
- verify the new table against your needs. 
- if it fits then the problem has moved to "update frequency" which might be easier to solve.

It's, of course, limited to the critical tables and not the whole DB

Robert Cemper · May 31, 2019

Well the code you posted shows a rather ancient type of table mapping away from standards.

And the errors you got underlines this as standard methods like %BuildIndices and %PurgeiIndices are not implemented.

I see 3 possible options:
- you find in the code (not necessarily in the class definition) some method to (re) generate the indices. 
Without experience with Caché and Object Script a rather impossible task.

- you create a copy of the class definition and move all data there by INSERT INTO new.table SELECT * from old.table
but it is unclear from what you showed to us if INSERT into your table is supported at all 

- least risky. you out-comment all broken indices except [IDKEY] and just use a flat table scan.
this is a fast workaround at the price of a poor performance on bigger tables.

honestly. - not a funny situation
 

Robert Cemper · May 31, 2019

There is another way directly with SQL 

First, you CREATE a temporary table according to your needs (or have it ready)

CREATE GLOBAL TEMPORARY TABLE MyTemp.Table temp1, temp2, . . . . .

Next, you fill it by INSERT directly from SELECT

INSERT INTO  MyTemp.Table  (temp1,Temp2, . . .) 
      SELECT COL1,COL2, ...   FROM Source.Table WHERE ...... 

The select is the same as before.

Robert Cemper · May 30, 2019

Hola Francisco !

Málaga is a splendid place. 
I was there several times privately (with all related sightseeing) and on business.
Always a great experience! 
I wish my Spanish (reading) was not so rusted to assist in tranlsation of articles.

especially this one Uso de expresiones regulares en ObjectScript

Bienvenido, Robert

Robert Cemper · May 24, 2019

Francisco,

instead of FOR EACH which obviously uses a nonexisting   Next method
you could create a loop using <WHILE> and do a "manual" loop.

For the condition   source.retorno.datos.polizas.poliza.%Size() gives you the  index limit

and source.retorno.datos.polizas.poliza.%Get(idx).sucursal   .... and similar provides the content. 

You have to increment idx manually and it runs from 0 (zero!) to %Size()-1

It's not as elegant as <foreach> but you have control over your JSON input.

Robert Cemper · May 22, 2019

as you describe if you refer to a file outside Caché and Caché has no control over it
it's a clear case of data inconsistency

you have 4 options:
1a) create a fake file at that reference an delete it then

1b) add an "on before delete" trigger to check and fix in advance either in the record or  in file system 

2a) manipulate the file reference using the object behind your entry

2b) do the delete at object level, trap the error and ignore it. since the file is gone anyway 

Robert Cemper · May 14, 2019

In fact, I see not 1 but 2 tables that share the same global.

so it might be easier to have 2 separated definitions one with and the other without the  4th subscript (some $h)

for access with SQL you than can use a UNION to assemble them. like:

SELECT * FROM (
     SELECT Account, PGC, Entity, ' ' as DateTime, ..fields... FROM TAB1 WHERE <some condition >
     UNION ALL
     SELECT Account, PGC, Entity, DateTime, ..same as above... FROM TAB2 WHERE <same condition >
) order by 1,2,3,4

or  you decide to add the missing subscript in your global to have a common structure

Robert Cemper · May 8, 2019

If this is Caché standard ID it is projected to SQL as xDBC Type INTEGER
Caché SQL Reference - Data Types says:

INTEGER%Library.Integer (MAXVAL=2147483647, MINVAL=-2147483648)

so 5983658923646 is definitely out of range

xDBC Type :

BIGINT

%Library.BigInt

   represents a 64 bit integer value.

I'm not aware of any parameter to change the data type of the generated ID.
But this hack may do the trick. Just add this calculated Property to your class without affecting the storage.

Property myID As %BigInt [ Calculated, SqlComputeCode = { set {*}=%ID}, SqlComputed ];