#Relational Tables

I created a Custom Search Table that extends EnsLib.HL7.Search Table. I saved, compiled, and rebuilt the index however when I go into Message Search to try to use the Search Table, within the conditions the proper fields are not showing up that was defined. It is showing the fields from EnsLib.HL7.SearchTable. I did a build index on both EnsLib.HL7.SearchTable and OSU.HL7.SearchTable.

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.

I have a need to create a specific Search Table outside of the EnsLib.HL7.SearchTable specifically for HL7 MFN messages in HealthShare Provider Directory.

Our Code is created in a different namespace then the main HSPD namespace and is mapped within the HSPD namespace mapping. 

I created a copy of EnsLib.HL7.SearchTable, named OSU.HL7.SearchTable what all needs mapped to make this new search table available in HSPD namespace?

If I go to Global within the Custom Code Namespace, I cannot see my new Search Table being created.

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:

Dynamic objects (%DynamicObject and %DynamicArray) allow developers to manipulate JSON-like structures directly in Objectscript. They are especially useful for modern applications that need to parse, transform or generate JSON.

This article describes a significant enhancement of how InterSystems IRIS deals with table statistics, a crucial element for IRIS SQL processing, in the 2025.2 release. We'll start with a brief refresher on what table statistics are, how they are used, and why we needed this enhancement. Then, we'll dive into the details of the new infrastructure for collecting and saving table statistics, after which we'll zoom in onto what the change means in practice for your applications. We'll end with a few additional notes on patterns enabled by the new model, and look forward to the follow-on phases of

Here at InterSystems, we often deal with massive datasets of structured data. It’s not uncommon to see customers with tables spanning >100 fields and >1 billion rows, each table totaling hundred of GB of data. Now imagine joining two or three of these tables together, with a schema that wasn’t optimized for this specific use case. Just for fun, let’s say you have 10 years worth of EMR data from 20 different hospitals across your state, and you’ve been tasked with finding….   every clinician within your network      who has administered a specific drug         between the years of 2017-2019

Hi, Community!

Want to improve accuracy and data integrity in your relational databases? Learn a key strategy in this video:

Setting Up SQL Foreign Key Constraints

Hello IRIS Community,

Hi Community,

I am attempting to create a new table by executing a SELECT statement that involves joining multiple tables. However, I encountered an error during execution: '( expected, AS found^Combined AS.' I would also like to create a cube based on this SQL table. However, during the cube creation process, I am prompted to specify a source class, and I'm unsure which class to use as it requires an existing class. Could you please help me identify the issue with the table creation, and provide guidance on selecting the appropriate source class for the cube creation?"

Hello Community,

Generally we could insert the stream object through SQL for stream properties and insert the list values for list of %String property. So, Is there any way to achieve this insert for the list of objects property( ex: Property CodeTable As list of Sample.CodeTable) via SQL? 

InterSystems FAQ rubric

By specifying the start and end values ​​of the IDs for which you want to rebuild indexes in the arguments of the %BuildIndices() method provided in the persistent class (=table) definition, you can rebuild only the indexes within that range.

For example, to rebuild the NameIDX index and ZipCode index in the Sample.Person class only for ID=10 to 20, execute the following code (the ID range is specified in the 5th and 6th arguments). 

 set status = ##class(Sample.Person).%BuildIndices($LB("NameIDX","ZipCode"),1,,1,10,20) 

Before we start talking about databases and different data models that exist, first we'd better talk about what a database is and how to use it.

A database is an organized collection of data stored and accessed electronically. It is used to store and retrieve structured, semi-structured, or raw data which is often related to a theme or activity.

At the heart of every database lies at least one model used to describe its data. And depending on the model it is based on, a database may have slightly different characteristics and store different types of data.

Class Contatos.Amiguinho Extends %Persistent
{
Property Moradia As Cidade;

Relationship Trabalho As Contatos.Empresa [ Cardinality = one, Inverse = Nomedaempresa ];
}

------------------------Routine-----------------------------------
Set objcontato=##class(Contatos.Amiguinho).%New()
Set IDm=3,IDt=2
Set objcontato.Moradia=##class(Contatos.Cidade).%OpenId(IDm)    ;<-- it works for "Property Moradia As Cidade"
Set objcontato.Trabalho=##class(Contatos.Empresa).%OpenId(IDt)  ;<-- it doesn't works for "Relationship Trabalho As Contatos.Empresa"
Set ret=objcontato.%Save()

InterSystems IRIS currently limits classes to 999 properties.

But what to do if you need to store more data per object?

This article would answer this question (with the additional cameo of Community Python Gateway and how you can transfer wide datasets into Python).

The answer is very simple actually - InterSystems IRIS currently limits classes to 999 properties, but not to 999 primitives. The property in InterSystems IRIS can be an object with 999 properties and so on - the limit can be easily disregarded.

Hello Everyone,

The Certification Team of InterSystems Learning Services is in the process of developing two exams focused on using SQL in InterSystems IRIS and we need input from our InterSystems IRIS SQL community. Your input will be used to evaluate and establish the contents of the exam.

How do I provide my input? We will present you with a list of job tasks, and you will rate them on their importance as well as other factors.

Last week at the InterSystems Global Summit, we announced our new Foreign Tables capability, which was introduced as an experimental feature with the 2023.1 release earlier this year. We're now inviting you to join the Early Access Program for Foreign Tables and kick the tires on this new capability, so you can let us know whether it suits your needs and what capabilities we should prioritize next.

This is the second piece in our series on 2021.2 SQL enhancements delivering an adaptive, high-performance SQL experience. In this article, we'll zoom in on the innovations in gathering Table Statistics, which are of course the primary input for the Run Time Plan Choice capability we described in the previous article.

I have this code:

SELECTid,room,client->name,functionary->nameFROM rooms ORDERBYid

What i do to access the "client" name?

set rs = $SYSTEM.SQL.Execute("SELECT id,room,client->name,functionary->name FROM rooms ORDER BY id")

zw rs.%Get("name")
"return functionary name"

The 2021.2 release of the InterSystems IRIS Data Platform includes many exciting new features for fast, flexible and secure development of your mission-critical applications. Embedded Python definitely takes the limelight (and for good reason!), but in SQL we've also made a massive step forward towards a more adaptive engine that gathers detailed statistical information about your table data and exploits it to deliver the best query plans. In this brief series of articles, we'll take a closer at three elements that are new in 2021.2 and work together towards this goal, starting with Run Time

This is the third article in our short series around innovations in IRIS SQL that deliver a more adaptive, high-performance experience for analysts and applications querying relational data on IRIS. It may be the last article in this series for 2021.2, but we have several more enhancements lined up in this area. In this article, we'll dig a little deeper into additional table statistics we're starting to gather in this release: Histograms

I want to create simple CSP page with some fields to filter and a table in the page that shows results from a table. Where to start any tips

In Cache WIndows environment:

Trying to use the $SYSTEM.SQL.DDLImport to import XML File that has ClassMethods, no SQL Table, but it doesn't appear to be working. I can use this ClassMethod to create SQL Tables. The manual method I have been using is to go into Cache Management Portal, Classes, Import.

I create an XML file first, then run the following to import but get no errors. Any ideas?

 Do $SYSTEM.SQL.DDLImport("Oracle",%ID,dlxml,logfl,0,"",";",2)

Beginning of XML file looks like...

Hi Cache team, I am in the need of listing all the user defined schemas that are present my Cache db and also the user defined tables and views and Columns of those tables and views through Queries. So that I can write some JDBC code to run the queries and fetch the above metadata. Any help is appreciated.

Thanks in Advance,

Kranthi kiran.

I noticed when creating Record Map's within Ensemble that it is creating a Persistent cache table as it translates the file and puts it into the Record Map data structure.

I was wondering if there was a way to add a column to the cache table that is Hidden from the record map, but is a calculated date value on when that record was inserted? 

From time to time I have gotten questions about what was valued in the incoming data stream, and I thought it would be helpful to add a date so I can sort on by which date that record came in on.

Thanks

Scott

Edit:

May have found the issue but not the solution.

"SELECT * FROM wmhISTORYdETAIL" runs as a passthrough without asking for the DNS.

but

'SELECT Count([wmhISTORYdETAIL].[HistHMNumber] AS CountOfHistHMNumber FROM [wmhISTORYdETAIL] WHERE ((([wmhISTORYdETAIL].[HistMovType])='Receipt') AND (([wmhISTORYdETAIL].[HistMovDate])>=Date()-1) AND (([wmhISTORYdETAIL].[HistMovDate])<Date()));'

asks for the DNS but both are linked to a table that has the password saved.

Any Ideas please?

Rob

Hi

I know that 1-1 relationships are not officialy supported by intersystems cache/iris so I want to know the best way to store data with this kind of data model.

Currently I have two classes that where implemented some time ago:

Table A with a relationship type one on table B

Table B with a relationship type one on table A

To compile I have a double compile with qualifyer U.

What is the best way to implement a data model with 1-1 relationships?

Thank you

Hi:

We added a column to a table as follows 

Property SentTime As Ens.DataType.UTC;

And the code to populate this

if pStatus = "SENT" //set SentTime which is used in the Tableau Report
{
Set doc.SentTime = $$$timeUTC
}

For Historical records of type "Rejected" the Sent time is appearing though as 1840-12-31 00:00:00, we don't know why. Current docs work fine with this column just historical seems to display the default instead of blank/null. 

A More Industrial-Looking Global Storage Scheme

In the first article in this series, we looked at the entity–attribute–value (EAV) model in relational databases, and took a look at the pros and cons of storing those entities, attributes and values in tables. We learned that, despite the benefits of this approach in terms of flexibility, there are some real disadvantages, in particular a basic mismatch between the logical structure of the data and its physical storage, which causes various difficulties.

Introduction

In the first article in this series, we’ll take a look at the entity–attribute–value (EAV) model in relational databases to see how it’s used and what it’s good for. Then we'll compare the EAV model concepts to globals.

Sometimes you have objects with an unknown number of fields, or perhaps hierarchically nested fields, for which, as a rule, you need to search.

When I describe InterSystems IRIS to more technically-minded people, I always start with how it is a multimodel DBMS at its core.

In my opinion that is its main advantage (on the DBMS side). And the data is stored only once. You just choose the access API you want to use.

• You want some sort of summary for your data? Use SQL!
• Do you want to work extensively with one record? Use objects!
• Want to access or set one value and you know the key? Use globals!