#SQL

13

SQL is a standard language for storing, manipulating and retrieving data in relational databases.

I am trying to help another group within our organization access a SQL Table that I have created to populate Epic Department Data within our environment and came across the ability to use SQL Seach REST Interface using iKnow.

However, I am having issues trying to get it to work via POSTMAN before I hand off the solution...

the POST URL... https://<servername>/api/iKnow/latest/TESTCLIN/table/osuwmc_Epic_Clarity.DepartmentMaster/search

where osuwmc_Epic_Clarity.DepartmentMaster is the table

In the body...

2 15
1 145

I had a need for a Filter, but did not want to recreate the wheel by creating another Data Lookup Table, so instead I created a Linked Table that points to a MS SQL Table outside of IRIS.

Once I had the Linked Table, I created a Class Method Function that would query the Linked Table and return a 1 if a result came back.

0 1
0 38

Hi folks!

It is very easy to import CSV data into IRIS. But what if we want to preserve the original IDs in CSV?

Recently I came across with the situation when I needed to import two csv's into IRIS which were linked by one column referencing to another csv's col: a typical Foreign Key and Primary Key situation, where csv1 contains this column as Primary Key, and csv2 as Foreign key with id's related to csv1.

The image is generated by ChatGPT so don't blame it - it tried its best to generate countries as primary keys with countries.csv-cities.csv relationship :)

Open Exchange app
3 0
1 52

While starting with Intersystems IRIS or Cache, developers often encounter three core concepts: Dynamic Objects, Globals & Relational Table. Each has its role in building scalable and maintainable solutions. In this article, we'll walk through practical code examples, highlight best practices, and show how these concepts tie together.

1. Working with Dynamic Objects:

3 2
4 103

Trying to create a new SQL Storage map on existing cache Global in the following format - ^MYGLO("R",rec)=data where the 'data' is built using $zel. e.g. $zel(data,1)="p1", $zel(data,2)="p2" etc... and the ^MYGLO("R",123)=data.

I'm having 2 issues. First, using the SQL Storage map wizard, I cannot figure out how to convey data in $zel format in the "Delimiter" field.

0 9
0 95

The August Article Bounty on the Global Masters article caught my attention, and one of the proposed topics sounded quite interesting in regard to its future use in my teaching. So, here's what I'd like to tell my students about tables in IRIS and how they correlate with the object model.

First of all, InterSystems IRIS boasts a unified data model. This means that when you work with data, you are not locked into a single paradigm. The same data can be accessed and manipulated as a traditional SQL table, as a native object, or even as a multidimensional array (a global). It means that when you create an SQL table, IRIS automatically creates a corresponding object class. When you define an object class, IRIS automatically makes it available as an SQL table. The data itself is stored only once in IRIS's efficient multidimensional storage engine. The SQL engine and the object engine are simply different "lenses" to view and work with the same data.

First, let's look at the correlation between the relational model and the object model:

Relational Object
Table Class
Column Property
Row Object
Primary key Object Identifier

It's not always a 1:1 correlation, as you may have several tables represent one class, for example. But it's a general rule of thumb.

5 7
2 107

Over the years, I’ve noticed that certain SQL questions come up repeatedly on the InterSystems Developer Community, especially about using the LIKE predicate in different contexts. Common variations include:

and many more derivatives. So, I decided to write an article that focuses on how LIKE works in InterSystems IRIS SQL, especially when used with variables in Embedded SQL, Dynamic SQL, and Class Queries, while touching on pattern escaping and special character searches.

First of all, I'd like to mention that InterSystems IRIS SQL offers most of the capabilities available in other relational DBMS that implement a later version of the SQL standard. But at the same time, it's important to mention that apart from relational access, in IRIS you can also use other models to get the same data, for example, object or document models.

On this note, let's look at the LIKE predicate and how this tool is used in SQL for pattern matching.

14 2
4 219

I have a table with 5M rows, the table contains lab observation codes and display names, both columns have type varchar(2000) and both are indexed.

The query looks like:

select code_1_text, count(code_1_text)
  from demo.observation_lab
  group by code_1_text
  order by 2 desc

The table contains ~1000 distinct display names.

It takes 4 minutes for the query to complete on a VM with some pretty old Xeon, 4 cores, 32G RAM, NVME SSD and Linux on board.

0 4
0 80