ODBC to Cache with Dynamic Table Names

If you have access to Caché database you can create custom query that accepts date as an argument and based on that returns specific table. It can be used via ODBC like this:

SELECT * FROM Package.Class.MyQuery(DATE)

or

Call Package.Class.MyQuery(DATE)

Here's a sample query that returns Ids from a table or a class and has an argument - class name (or table name):

Class Utils.CustomQuery2
{

/// Return ids from a table or a class
Query GetTable(Table) As %Query(CONTAINID = 1, ROWSPEC = "Id:%String") [ SqlProc ]
{
}

ClassMethod GetTableExecute(ByRef qHandle As %Binary, Table) As %Status
{
    #Dim Status As %Status = $$$OK

    If ##class(%Dictionary.ClassDefinition).%ExistsId(Table) {
        // Got a class, we need to calculate a table name and quote it
        #define ClassSQLTable(%c)    ($$$comClassKeyGet(%c,$$$cCLASSsqlschemaname)_"."_$$$comClassKeyGet(%c,$$$cCLASSsqltablename))
        Set Table = ##class(%CSP.UI.Portal.SQL.Home).Quoter2($$$ClassSQLTable(Table))
    }
    

    Set qHandle = ##class(%SQL.Statement).%ExecDirect(,"SELECT ID FROM " _ Table)
    If qHandle.%SQLCODE'=0 {
        Set Status = $$$ERROR($$$SQLError, qHandle.%SQLCODE, qHandle.%Message)
    }
    Quit Status
}

ClassMethod GetTableFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status
{
    If qHandle.%Next() {
        // Same as in ROWSPEC
        Set Row = $Lb(qHandle.ID)
    } Else {
        /// No more data
        Set AtEnd = 1
        Set Row = ""
    }
    Quit $$$OK
}

ClassMethod GetTableClose(ByRef qHandle As %Binary) As %Status
{
    Kill qHandle
    Quit $$$OK
}

}

Call samples from ODBC:

SELECT * FROM Utils.CustomQuery2_GetTable('Cube.Cube.Fact')
Call Utils.CustomQuery2_GetTable('Cube_Cube.Fact')

Code on GitHub.