How do you count "Count"?
Why is "Northwest" 1 instead of 2 for 2021?


For now so:

select v.Branchnvl(sum(%FOREACH(v.Branch)),0) "Count" from 
  (
  select 'Northwest' Branch,$listbuild('Northern','Western'Branches union
  select 'Oriental',$listbuild('Eastern'union
  select 'Southern',$listbuild('Southern')
  ) v
left join
  (select replace(%exact(Branch),' branch',''Branch,count(%FOREACH(Branch)) from yourtable where year("Date")=2021 group by Branchm
on m.Branch %inlist v.Branches
group by v.Branch

You are implicitly using %Library.SqlQuery:Func() method, in which, as @Robert Cemper rightly pointed out, $get() is used.

You can do it differently:

Query GetInfo(pObject AS Kurro.MyClassAs %SQLQuery(CONTAINID 1ROWSPEC "IdList:%String,IdProcess:%String,Duration:%String") [ SqlProc ]
{
    SELECT IdListIdProcessDuration
    FROM Kurro.MyClass
    WHERE KeyProcess :pObject.KeyProcess
    AND CodeSpecialist :pObject.CodeSpecialist
    AND CodeProvider :pObject.CodeProvider
    AND CodeCenter :pObject.CodeCenter
    AND "Date" :pObject.Date
}


set obj=##class(Kurro.MyClass).%New()
set obj.KeyProcess="1033004-1#"
set obj.CodeSpecialist "surgery"
set obj.CodeProvider "PR002"
set obj.CodeCenter "CENTER-01"
set obj.Date $ZDATETIME($ZDATETIMEH("2021-04-30 15:45:00",3,1),3,1)

set st=##class(%SQL.Statement).%New()
set sc=st.%PrepareClassQuery("Kurro.MyClass","GetInfo")
  if $$$ISERR(sc{write "%PrepareClassQuery failed:" do $System.Status.DisplayError(scquit}
set result=st.%Execute(obj)
do result.%Display()

Important:
Jobbed Process Permissions are Platform-dependent
Running Programs or System Commands with $ZF(-100)

So that we speak the same language, I made a simple example using Using the Work Queue Manager

Class dc.test Abstract ]
{

ClassMethod MyJob(SDIR As %String)
{
  FILE=##class(%File).NormalizeFilename("DIRLIST.TXT",SDIR)

  q:##class(%File).GetFileSize(FILE)>0

  ##class(%File).Delete(FILE)

  X=$ZF(-1,$$$FormatText("DIR %1 >> %2",$$$quote(##class(%File).NormalizeDirectory(SDIR)),$$$quote(FILE)))
}

/// d ##class(dc.test).test()
ClassMethod test()
{
  N=4
  queue=$system.WorkMgr.Initialize(,.sc,N)
  i=1:1:queue.Queue("##class(dc.test).MyJob","C:\Temp\test "_i)
  queue.WaitForComplete()
}

}

I copied different files to the following directories:

C:\Temp\test 1
C:\Temp\test 2
C:\Temp\test 3
C:\Temp\test 4

After calling ##class(dc.test).test() , everything worked out as expected: DIRLIST.TXT were created in the corresponding directories each with its own content.

See CREATE FUNCTION

  • Create your own counting function:
    CREATE FUNCTION my.GetCalcTableExtentSize(IN SchemaName SYSNAMEIN TableName SYSNAMERETURNS BIGINT(''PROCEDURE LANGUAGE OBJECTSCRIPT
    {
     quit ##class(%SQL.Manager.Catalog).GetCalcTableExtentSize(SchemaNameTableName)
    }
  • Now you can use it in queries:
    select table_schema "Schema"table_name TableName,my.GetCalcTableExtentSize(table_schema,table_nameRowCount from information_schema.tables where table_type in ('BASE TABLE','VIEW')

See %Regex.Matcher

Example:

text "This is a sample blob of text",
  keywords="This,blob,text"

matcher=##class(%Regex.Matcher).%New($tr(keywords,",","|")),
  matcher.Text=text
w:matcher.Locate() "hit",!

matcher.ResetPosition()

while matcher.Locate() {write "Found ",matcher.Group," at position ",matcher.Start,!}

USER>^test
hit
Found This at position 1
Found blob at position 18
Found text at position 26

Or see $locate: Using Regular Expressions in Caché

Example:

USER>w $locate(text,$tr(keywords,",","|"),1,e,x)
1

I keep my promise (yes, it wasn't an April Fool's joke ;)

  • The first way is associated with a dummy field for the sake of being able to override the final BuildValueArray method and avoid the following error
    ERROR #5272: Can't change final 'Method' : 'BuildValueArray')
    Class rcc.IC.ItemList Extends (%Persistent%Populate) [ Final ]
    {
    
    Index xitmp On (ItemsP(KEYS), ItemsP(ELEMENTS));
    
    Property Company As %String Required ];
    
    Property Region As list Of %String(COLLATION "EXACT"POPSPEC ":4"VALUELIST ",US,CD,MX,EU,JP,AU,ZA") [ Required ];
    
    Property Items As list Of rcc.IC.serItem(POPSPEC ":4") [ Required ];
    
    Property ItemsP As %String(COLLATION "EXACT") [ CalculatedPrivateReadOnlyRequiredSqlComputeCode = {{*} {Items}}, SqlComputed ];
    
    ClassMethod ItemsPBuildValueArray(
      value,
      ByRef arrayAs %Status
    {
      ptr=0
      while $listnext(value,ptr,val){
        v=$li(val,1),
          array($li(v,1))="Subject",
          array($li(v,2))="Change",
          array($li(v,3))="Color"
      }
      q $$$OK
    }
    
    }
    select count(IDfrom rcc_IC.ItemList where FOR SOME %ELEMENT(ItemsP) (%key in ('blue','yellow') and %value='Color')

    or if need to find values in any fields

    select count(IDfrom rcc_IC.ItemList where FOR SOME %ELEMENT(ItemsP) (%key in ('blue','yellow'))

    For the sake of speed, you can store in the index not all the fields of the serial class, i.e.:

    Index xitmp On ItemsP(KEYS);
    
    ClassMethod ItemsPBuildValueArray(
      value,
      ByRef arrayAs %Status
    {
      ptr=0
      while $listnext(value,ptr,val){
        v=$li(val,1),
          array($li(v,3))="" ; only Color
      }
      q $$$OK
    }
    select count(IDfrom rcc_IC.ItemList where FOR SOME %ELEMENT(ItemsP) (%key in ('blue','yellow'))

    You can also add more dummy fields and accordingly indexes to cover all possible queries.


  • The second way involves changing the storage and creating a virtual table
    Class rcc.IC.ItemList Extends (%Persistent%Populate) [ Final ]
    {
    
    Index xitm On Items(ELEMENTS).Color;
    
    Index xitm1 On (Items(ELEMENTS).Color, Items(KEYS));
    
    Property Company As %String Required ];
    
    Property Region As list Of %String(COLLATION "EXACT"POPSPEC ":4"VALUELIST ",US,CD,MX,EU,JP,AU,ZA") [ Required ];
    
    Property Items As list Of rcc.IC.serItem(POPSPEC ":4"STORAGEDEFAULT "array") [ Required ];
    
    }
    select count(distinct IDfrom rcc_IC.ItemList where ItemList_Items->Items_Color in ('blue','yellow') -- index "xitm1" is used

    or even faster

    select count(IDfrom rcc_IC.ItemList where FOR SOME %ELEMENT(Items) (%Value in ('blue','yellow')) -- index "xitm" is used

    Of course, the data can be accessed from both tables, just do not forget about SetCollectionProjection GetCollectionProjection (for more information, see my article SQL index for array property elements)

The %SYS namespace sources are open to study and often (but not always) serve as a coding etalon reference for application developers. This is a whole storehouse of knowledge for those who want to better understand certain mechanisms work of system classes.
If some of the commands found there are not documented, but effectively do useful work, then why can't they be used by application developers?
In addition, I do not rule out the fact that some of them are simply forgot to document.

What really needs to be hidden or potentially dangerous is already hidden in the deployed classes.

Hi Robert.

I also got carried away with this question and found two more ways to use indexes for a list of serial objects, and you can explicitly specify specific fields in the query, rather than $list (%Value,3).

The speed may be not always the best, but I did the best I could. I tested on Caché (perhaps something has been improved in IRIS in this regard?)

If you're interested, I can share it.