Pesquisar

Question
· 4 hr ago

Arissa Seah

At Mercury Consulting, we offer specialized services to help people and companies thrive in the dynamic global environment of today. The Greece Golden Visa program is one of our main products, which leads investors to obtain Greek residency through wise financial or real estate investments. From choosing the best investment to guaranteeing complete adherence to Greece's legal and regulatory framework, our experienced staff is committed to helping you at every stage of the procedure. In addition to immigration services, we provide a wide range of solutions tailored to your particular requirements and objectives, such as company formation, accounting, tax planning, auditing, and trustee services. Mercury Consulting is here to help you every step of the way, whether your goals are to start a business, simplify your finances, or settle in Greece.
 

Discussion (0)1
Log in or sign up to continue
Discussion (0)1
Log in or sign up to continue
Announcement
· 4 hr ago

[Video] Automate Prior Authorization and Data Exchange with InterSystems Payer Services

Hi, Community!

Need a more efficient way to exchange health data? See how InterSystems Payer Services can help:

Automate Prior Authorization and Data Exchange with InterSystems Payer Services

In this video, see how the HL7® FHIR® components and APIs built into InterSystems Payer Services make it easier to:

  • Streamline electronic prior authorization.
  • Meet CMS-0057 requirements.

...leading to improved care coordination and lower administrative burden!

Discussion (0)1
Log in or sign up to continue
Article
· 5 hr ago 4m read

Table-valued function example: querying the Application Error Log

Let's start with a simple motivating question: over the past 14 days, what are my most common errors in the Application Error Log?

Answering this through the management portal or terminal is an annoying manual process - we should just be able to use SQL. Fortunately, there are a few class queries to help with this in the SYS.ApplicationError class in the %SYS namespace. You can answer the question for a single date with something like:

select "Error message",count(*)
from SYS.ApplicationError_ErrorList('CCR','12/16/2024')
group by "Error message"
order by 2 desc

Unfortunately, the structure of the class queries runs into the same overall structural limitations as the management portal pages; the ErrorList query needs a namespace and date. Surely there's a better approach than 14 union'ed calls to that class query for different dates, right? On some level, this is a real question; if there is a good way to do this through plain old SQL and I'm just missing it, please tell me!

The logical step is to write our own custom class query. This involves adding a Query class member (say <QueryName>) and implementing methods named <QueryName>Execute, <QueryName>Fetch, and <QueryName>Close. In general, Execute sets up the context for the class query and does any initial work, maintaining any state in qHandle; Fetch gets a single row and says whether we've found all the rows or not; and Close does any final cleanup. For example, if the implementation of Execute/Fetch uses a process-private global, Close might kill it.

Don't forget the magical [ SqlProc ] flag on the Query member that allows it to be called as a TVF (table-valued function) from other SQL queries!

Here's the full working example:

/// Utility queries to help access the application error log from SQL
Class AppS.Util.ApplicationErrorLog
{

/// Returns all application errors (all dates) from the application error log
Query All() As %Query(ROWSPEC = "Date:%Date,ErrorNumber:%Integer,ErrorMessage:%String,Username:%String") [ SqlProc ]
{
}

/// Gets a list of dates with errors and stashes it in qHandle
ClassMethod AllExecute(ByRef qHandle As %Binary) As %Status
{
    Set ns = $Namespace
    New $Namespace
    Set $Namespace = "%SYS"
    Set stmt = ##class(%SQL.Statement).%New()
    Set stmt.%SelectMode = 0
    Set result = ##class(%SQL.Statement).%ExecDirect(stmt,"select %DLIST(""Date"") ""Dates"" from SYS.ApplicationError_DateList(?)",ns)
    $$$ThrowSQLIfError(result.%SQLCODE,result.%Message)
    If 'result.%Next(.sc) {
        Return sc
    }
    Set qHandle("list") = result.%Get("Dates")
    Set qHandle("pointer") = 0
    Quit $$$OK
}

/// Gets the next row, advancing to the next date if needed
ClassMethod AllFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = AllExecute ]
{
    Set sc = $$$OK
    Set ns = $Namespace
    New $Namespace
    Set $Namespace = "%SYS"
    If $Get(qHandle("dateResult")) = "" {
        // Advance to the next date
        Set pointer = qHandle("pointer")
        If '$ListNext(qHandle("list"),pointer,oneDate) {
            Set AtEnd = 1
            Quit $$$OK
        }
        Set qHandle("pointer") = pointer
        Set qHandle("currentDate") = oneDate
        Set qHandle("dateResult") = ##class(%SQL.Statement).%ExecDirect(,"select * from SYS.ApplicationError_ErrorList(?,?)",ns,oneDate)
        $$$ThrowSQLIfError(qHandle("dateResult").%SQLCODE,qHandle("dateResult").%Message)
    }
    If qHandle("dateResult").%Next(.sc) {
        // If we have a row for the current date, add it
        Set Row = $ListBuild(qHandle("currentDate"),qHandle("dateResult").%GetData(1),qHandle("dateResult").%GetData(2),qHandle("dateResult").%GetData(6))
    } ElseIf $$$ISOK(sc) {
        // Otherwise, clear out the result set and call AllFetch to advance
        Set qHandle("dateResult") = ""
        Set $Namespace = ns
        Set sc = ..AllFetch(.qHandle,.Row,.AtEnd)
    }
    Quit sc
}

ClassMethod AllClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = AllExecute ]
{
    New $Namespace
    Set $Namespace = "%SYS"
    // This seems to be necessary sometimes to have %OnClose run properly
    Kill qHandle("dateResult")
    Quit $$$OK
}

}

In this example, we start out in a user namespace but all the queries really run in %SYS. Execute gets a list of error dates for the current namespace and stashes it in qHandle. Fetch advances to the next date when appropriate, then returns the next error for the current date. And Close makes sure that the class query goes out of scope in %SYS, because I sometimes got errors if it didn't. This was a little surprising but kind of makes sense because the class query we're calling only exists in %SYS.

There's a lot of power in reusability of table-valued functions. For example, we can add another one in the same class:

/// Gets a count by error message over the last <var>Days</var> days
Query ErrorCounts(Days As %Integer) As %SQLQuery(ROWSPEC = "Occurrences:%Integer,ErrorMessage:%String") [ SqlProc ]
{
    SELECT COUNT(*) AS Occurrences, ErrorMessage
    FROM AppS_Util.ApplicationErrorLog_All()
    WHERE DATEDIFF(D,"Date",$h) <= :Days
    GROUP BY ErrorMessage
    ORDER BY Occurrences DESC
}

And now getting our most common application errors over the last 14 days is as simple as:

call AppS_Util.ApplicationErrorLog_ErrorCounts(14)

Now we just need to fix them all! 😅

Discussion (0)1
Log in or sign up to continue
Article
· 6 hr ago 2m read

Como usar o Global - Inspector

Se você está investigando Globals estruturadas complexas, isso pode facilmente se tornar um exercício cansativo de digitação.
Diferente do Global Explorer no System Management Portal, o Global-Inspector permite um tipo de navegação aprofundada (drill-down), permitindo explorar nível por nível dos subscritos. Você também tem a opção de visualizar o conteúdo armazenado ou mostrar apenas a estrutura de subscritos. Globals que armazenam tabelas SQL podem não ser tão interessantes, mas no espaço SYSTEM, você encontrará verdadeiras árvores com ramos e ramificações completamente diferentes.

Global-Inspector pode ser executado no navegador ou via linha de comando no terminal.

Entradas necessárias:

  • Nome da Global: com ou sem o caractere ^ inicial.
  • Número máximo de níveis de subscrito que você deseja visualizar.
  • Exibir conteúdo do nó da Global mostrado.
  • Subscrito inicial: pode ser exato ou anterior ao primeiro nó exibido.
  • Subscrito final: pode ser exato ou anterior ao primeiro nó excluído.
  • Importante: os subscritos devem ser exatamente entre aspas, por exemplo: "JOURNAL" e não apenas JOURNAL.

Global-Inspector no Navegador

 

Global-Inspector no Terminal

USER>do ^rcc.ginspect
Global Name : %SYS
Maximum Subscripts : 2
Show content ? (0,1) [1] : 0
Start Subscript :"JOU"
Stop Subscript : "K"
^%SYS("JOURNAL")
^%SYS("JOURNAL","ALTDIR")
^%SYS("JOURNAL","CURDIR")
^%SYS("JOURNAL","CURRENT")
^%SYS("JOURNAL","EXPSIZE")
^%SYS("JOURNAL","LAST")
^%SYS("JOURNAL","MAXSIZE")
^%SYS("JOURNAL","PREFIX")
>>> stop <<<
USER>

GitHub
Video

1 new Comment
Discussion (1)1
Log in or sign up to continue