#SQL

Hello my friends,

I have a problem with logi report, 

in my store procedure,  I create code like this?

$ListToString(%DLIST(DISTINCT (ARCIM_Desc)),'<br/>') as "ARCIM_Desc", -> this is the problem

$ListToString(%DLIST(DISTINCT (MRDIA_ICDCode_DR)),'<br/>') as "MRDIA_ICDCode_DR", -> it's works fine

as you see there's a <br/> in the display of the report

I have no idea about this, maybe someone can help me fix this problem ?

Thank You

Best Regards,

Steven Henry

Hello my friends,

I have a problem with Objectscript, why the value of address become like this ?

everything works fine except the Address,

this is my code, do I need something to make this into real address ? should I put something in my code ? 

 set paper=obj.PAADMPAPMIDR.PAPMIPAPERDR

            if '$isobject(paper) continue

            set Address=paper.PAPERStName

thank you for your help

Best Regards,

Steven Henry

Hello my friends,

I have a bit problem with date format, I need to display like this DD-MM-YYYY

I've put the objectscript like this : set DOB=$zd(paper.PAPERDob,15)  

and the result is 

Why It cannot show the full year, I mean why 99 or 95 why not 1999 or 1995 ? 

Thank You

Best Regards,

Steven Henry

Do not let the title of this article confuse you; we are not planning to take the InterSystems staff out to a fine Italian restaurant. Instead, this article will cover the principles of working with date and time data types in IRIS. When we use these data types, we should be aware of three different conversion issues:

1. Converting between internal and ODBC formats.
2. Converting between local time, UTC, and Posix time.
3. Converting to and from various date display formats.

Maybe this is well known but wanted to help share.

Consider that you have the following persistent class defintions

An Invoice Class with a property reference to Provider

Class Sample.Invoice Extends (%Persistent, %Populate)
{
Parameter DSTIME = "AUTO";
Property InvoiceNumber As %Integer(MINVAL = 100000) [ Required ];
Property ServiceDate As %Date(MINVAL = "+$h-730") [ Required ];
Index InvoiceNumber On InvoiceNumber;
Property Provider As Sample.

Hi Community,

Enjoy the new video on InterSystems Developers YouTube from our Tech Video Challenge:

⏯ Using Character Slice Index in InterSystems IRIS

InterSystems FAQ rubric

This can be achieved by using the CSV() procedure of the %SQL.Util.Procedures class.
Below is an example of usage code. (Assuming that the file test.csv is in c:\temp.)

Hello IRIS community,

InterSystems Certification is currently developing a certification exam for InterSystems IRIS SQL professionals, and if you match the exam candidate description given below, we would like you to beta test the exam! The exam will be available for beta testing starting May 19, 2025.

Hello, good morning, thank you so much for reading this question. ☺️🙂👍

We are developing a code to get information about our Production's items: services, processes and operations.

We know we can get various configurations of a given item: Category, Port, Enabled...

But we wonder how we could get the date time of the last mesage (most recent) received in an item.

To give a code snippet a small section of the code we have developed (and tested), it looks like:

[...]

For i=1:1:tProduction.Items.Count() {
    #dim item as Ens.Config.Item
    set item = tProduction.

How do I write DDL script for collection properties?

For example I want to create the following class:

Class SQLUser.Person {

Property Name As %String;

Property FavoriteColors As list Of %String;

}

My DDL script looks like this:

CREATE TABLE Person (Name varchar(50), FavoriteColors ???)

Description

This is a template for a Flask application that can be deployed in IRIS as an native Web Application.

Installation

1. Clone the repository
2. Create a virtual environment
3. Install the requirements
4. Run the docker-compose file

git clone
cd iris-flask-template
python3 -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt
docker-compose up

Usage

The base URL is http://localhost:53795/flask/.

Endpoints

• /iris - Returns a JSON object with the top 10 classes present in the IRISAPP namespace.
• /interop - A ping endpoint to test the interoperability framework of IRIS.

We are glad to announce that DBeaver has supported InterSystems IRIS out-of-the-box since version 7.2.4. You don't need to configure it manually anymore, just find the IRIS icon in the Connections list. 

If you're migrating from Oracle to InterSystems IRIS—like many of my customers—you may run into Oracle-specific SQL patterns that need translation.

Take this example:

SELECT (TO_DATE('2023-05-12','YYYY-MM-DD') - LEVEL + 1) AS gap_date
FROM dual
CONNECT BY LEVEL <= (TO_DATE('2023-05-12','YYYY-MM-DD') - TO_DATE('2023-05-02','YYYY-MM-DD') + 1);

In Oracle:

• LEVEL is a pseudo-column used in hierarchical queries (CONNECT BY). It starts at 1 and increments by 1.
• CONNECT BY LEVEL <= (...) determines how many rows to

Hi,

     Can anyone please tell me how to append a string using a update query.

For eg :  "Hello" is the string . I need to append the string world and it needs to be "Hello world".

Thanks

Jude

How would you go about creating an SQL Stored Procedure that would result in the same output as "IN LIKE"?
For example...
CustomersTable

select *
from CustomersTable
where inLike('%Mark%', '%John%')

Would return:

Hello,

I have a class with a "Unique" index (pxfactidIndex) on a %Numeric property (pxfactid) (partially-edit code snippet below):

Property pxfactid As %Library.Numeric(MAXVAL = 9223372036854775807, MINVAL = -9223372036854775808, SCALE = 0) [ SqlColumnNumber = 7 ];
Index pxfactidIndex On pxfactid [ Unique ];
Storage Default
{
<Data name="FactDefaultData">
<Value name="1">
<Value>pysubjectid</Value>
</Value>
...
<Value name="6">
<Value>pxfactid</Value>
</Value>
...
</Data>
<DataLocation>^CRMBI.FactD</DataLocation>
<DefaultData>FactDefaultData</DefaultData>
<ExtentLocation>^CR

Is there a way in a EnsLib.SQL.InboundAdpapter that you can tell when you have reached the end of the Results of the Query?

Does anyone have a query that I could run to show a Vendor the time difference between when a message was sent out a BO, and when we received the HL7 ACK back that is associated with the message sent?

I am trying to prove to this vendor of the delay we are seeing getting the ACK back because of a Timeout

I know how to pull Ens.MessageHeader, and EnsLib.HL7.Message but not sure how to match up the Message with the HL7 Acknowledgement received.

Hi community.

I was wondering if it was possible to use something like EnsLib.SQL.InboundAdapter with tables in IRIS.

This library monitors when a record has been inserted into a table in an external database, so it requires a DSN to connect to that database.

My goal is to make a call to an external API that takes a long time, it could spend nearly an hour (or more) completing its processes, but I don't want to block the main process.

I am testing vectorsearch, while doing so I am trying to paginate my resultset for a "next page" function to give me the first, second, third 15 entries within a table. 

For this I have two embedding classes. One with a HNSW Index (vectornomicembedtextlatest) , and one without (vectornomicembedtexttest).

Calling SELECT ID,PRIMKEY FROM SQLUser.vectornomicembedtexttest LIMIT 5 OFFSET 1 works fine with the first entry having the rowID of 486448. (I deleted old entries in the beginning and reused the table)

SELECT ID,PRIMKEY FROM SQLUser.

I'm excited to announce a major update to SQL Data Lens – a powerful database client and metadata explorer – that opens up new, free possibilities for the InterSystems community.

SQL Data Lens is now completely FREE to use with InterSystems IRIS Community Edition!

No more “localhost only” restrictions
No more limits on the number of connections

No license? No problem.
You can now connect to InterSystems IRIS Community Edition—completely license-free—using the fully functional Free Edition of SQL DATA LENS. Explore all the features, no strings attached.

Hi, I am unsure how to remove this restriction; when I am performing dynamic SQL using ##class(%SQL.Statement).%ExecDirectNoPriv(, .query, args...)

It works fine, but the moment I add specific properties from the persistent class I am performing the select on into the WHERE clause, I get: ERROR #5540: SQLCODE: -99 Message: User UnknownUser is not privileged for the operation.

Hello. Currently, we are developing using Cache 2018 version.
Our team is working on improving an existing legacy program so that it can also be used on the web.

Before asking my question, here is the development environment.

• IDE: IntelliJ
• Framework: Spring Boot, MyBatis
• DB Connection: JDBC (using the library provided by InterSystems)

Currently, we are successfully mapping global data through the %PERSISTENT class and able to query it with SQL. However, the problem is that the retrieved "Korean" data is all broken.

Hello Community,

I executed the below query in the %ZLANGC00.mac routine. It returns results when using dynamic SQL, but not with embedded SQL(returns 0). 

 &SQL(SELECT count(*) INTO :Cnt FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'")
 Write "SQL Table count : "_Cnt,!
 Set tResultSet = ##class(%SQL.Statement).%ExecDirect(,"SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'")
 Do tResultSet.%Display()

Thanks!

I have a table in the development and want to erase data from certain columns. There is info in the docs but not for human
This piece of code does not work

ALTER TABLE table DROP COLUMN column %DELDATA

If you are a customer of the new InterSystems IRIS® Cloud SQL and InterSystems IRIS® Cloud IntegratedML® cloud offerings and want access to the metrics of your deployments and send them to your own Observability platform, here is a quick and dirty way to get it done by sending the metrics to Google Cloud Platform Monitoring (formerly StackDriver).

Would like to know if there is an alternative or better way to paginate through a dataset using dynamic SQL than what I am using below. The problem is that as the potential pool of data gets larger, this code slows down to the point of not being useable. In analyzing each line of code below, it appears the slow down is related to the initial rset.%Next() iteration. Is there anything available which does not require a subquery/%VID such as a simple LIMIT/OFFSET?

My code is similar to :

s sql=##class(%SQL.Statement).

Hello IRIS community,  

InterSystems Certification is developing a certification exam for InterSystems IRIS Developer professionals, and if you match the exam candidate description given below, we would like you to beta test the exam.

Migrating from Oracle, MSSQL, or other purely relational database systems to a multimodel InterSystems IRIS is a strategic decision that requires careful planning and execution. While this transition offers significant benefits, including enhanced performance, scalability, and support for modern architectures, it also comes with challenges. In this article I will highlight some of the considerations connected to coding to ensure a successful migration. I will leave everything connected to an actual migration of structures and data outside the scope of this article.

First, when you're considering migrating to a different database system, you need to understand your business logic, whether it's on the side of the application (application server) or the database server. Basically, where do you have your SQL statements that you will need to potentially rewrite? 

I have an IRIS persistent class with a %Stream property whose value is a JSON object. I'd like to use a SQL trigger to pull some value out of the JSON and persist it in another property, for easy querying and indexing. See below for a minimal example:

Class PAB.DebugStream Extends %Persistent
{

Property Contents As %Stream.GlobalCharacter;
Property msg As %String;
ClassMethod InsertRow()
{
    set stream = ##class(%Stream.GlobalCharacter).%New()
    $$$ThrowOnError(stream.Write({"msg":"hello world!"}))
    &sql(insert into PAB.