· Apr 18

Direct access to a global without SQL

Hi everyone,
I have this global with 2 informations in it: Reference (ex: 1329) and Code (ex: JMMK-G1D6).

^DataTest    =     3
^DataTest(1)    =     $lb("","1329","JMMK-G1D6")
^DataTest(2)    =     $lb("","1516","AMEV-GVPF")
^DataTest(3)    =     $lb("","2333","4QC6-4HW3")

With ObjectScript, i want to test if Reference 1516 exists in the global. 
In the InterSystems portal, i can do it with SQL (SELECT count(*) FROM DataTest where Reference = '1516'), but can we do the same in ObjectScript without SQL and manipulating the global directly ?

Thanks for help.

Discussion (4)4
Log in or sign up to continue

@maxime keltsma 

You can traverse the entire global with a loop and $Order function.

A simple routine to traverse your global can be:

    Set ^DataTest = 3
    Set ^DataTest(1) = $lb("","1329","JMMK-G1D6")
    Set ^DataTest(2) = $lb("","1516","AMEV-GVPF")
    Set ^DataTest(3) = $lb("","2333","4QC6-4HW3")
    Set key = ""
    // Print the header filds
    Write "Reference", $Char(9), "Code"	, !	
        // first parameter is global and key, 
        // second parameter opredr to travers globa. 1 = ascending subscript order (the default) or –1 = descending
        // third is target that receives the value if the key is defined
        Set key = $Order(^DataTest(key),  1, data)
        If (key = "")
            Quit // Exit for loop
        Set reference = $ListGet(data, 2)
        Set code = $ListGet(data, 3)
        Write reference, $Char(9), code, !

Output o execution:

> Do Teste^Teste
> Reference Code
> 1329 JMMK-G1D6
> 1516 AMEV-GVPF
> 2333 4QC6-4HW3

See Using Globals Object Script Tutorial , $Order Function for more examples e details.

So, there's 2 ways to read this, either we want an "exists" check, or following the SQL, we want a count of all instances.   This snippet can be set to do either case based on the existscheck boolean.  Ideally though, you would have an index defined, and this could be read much more efficiently than having to scan an entire global

set count=0
set existscheck=0 //Set to 1 if we only want to find first instance
set targetValue=1329
set key = ""
for   {
    set key = $ORDER(^DataTest(key))
    if targetvalue = +$LG(^DataTest(key),2) do $INCREMENT(count)
w !,"Count value is "_count

couple of options:

1. $order the global checking the list content as others have already mentioned

2. if there is a SQLIndex defined for the field. You can check the index location directly via objectscript, this would save you ordering through possibly billions of lines of data
3. define the SQL column to for Reference to use columnar storage, also single global retrieve to get a $list of rowids.