#SQL

As you may well remember from Global Summit 2022 or the 2022.2 launch webinar, we're releasing an exciting new capability for including in your analytics solutions on InterSystems IRIS. Columnar Storage introduces an alternative way of storing your SQL table data that offers an order-of-magnitude speedup for analytical queries. First released as an experimental feature in 2022.2, the latest 2022.3 Developer Preview includes a bunch of updates we thought were worth a quick post here.

Is there a way I can call %Save() on a %Persistent object and at the same time prevent indices from being built?

A good writer is supposed to draw you in with the title and bury the answer somewhere in the article. I suppose this makes me a bad writer – don’t think less of me, my whole feeling of self-worth comes from the opinions of internet strangers!

I have table

CREATE TABLE nodes (
        name VARCHAR(50) NOT NULL, 
        parent VARCHAR(50), 
        PRIMARY KEY (name), 
        FOREIGN KEY(parent) REFERENCES nodes (name) ON UPDATE cascade
);

I put some data

INSERT INTO nodes (name, parent) VALUES ('n1', NULL);
INSERT INTO nodes (name, parent) VALUES ('n11', 'n1');
INSERT INTO nodes (name, parent) VALUES ('n12', 'n1');
INSERT INTO nodes (name, parent) VALUES ('n13', 'n1');

Let's delete all

DELETE FROM nodes;

Nope, no way.

SQL Error [124] [S1000]: [SQLCODE: <-124>:<FOREIGN KEY constraint failed referential check upon DELETE of row in referenced table>]
[Location: <ServerLoop>]
[%msg: <At least 1 Row exists in table 'SQLUser.nodes' which references key 'NODESPKey2' - Foreign Key Constraint 'NODESFKey3', Field(s) 'parent' failed on referential action of NO ACTION>]

Hi,

I tried to join my local DB table with link table. but I am getting below error.

5475 5475 reporterr2+40^%occRoutine Error #5475: Error compiling routine: %sqlcq.HSANALYTICS.cls483. Errors: %sqlcq.HSANALYTICS.cls483.cls(%OnNew+5) : SQLCODE=-161 : References to an SQL connection must constitute a whole subquery

I tried to execute below query in Managementportal

select dg.ID from HSAA.Diagnosis dg
left join LinkTableData.FacilityFullList la on dg.ID=la .ID

Hi Developers,

Enjoy watching the new video on InterSystems Developers YouTube:

⏯ InterSystems IRIS Cloud SQL @ Global Summit 2022

https://www.youtube.com/embed/QLaAH5o60y0
[This is an embedded link, but you cannot view embedded content directly on the site because you have declined the cookies necessary to access it. To view embedded content, you would need to accept all cookies in your Cookies Settings]

Hi, how do I create a trigger that will only set the value of the "Status" field for new objects, not for all available?

Class CarDealer.Order Extends (%Persistent, %Populate)
{

Property Title As %String

Hi

I have two tables: one a local table using cache and the other is via an SQL gateway connection to an MSSQL Server via a 64bit ODBC driver (ODBC Driver 17 for SQL Server).

When I try to join them on a date field I don't get any matches, and I know that there are matches, e.g. running both views separately returns resuls for 30/11/2022.

The following statement returns rows for the first table r, but nothing for the table l

Hello,

searching messages in our Message Bank is quite slow, often runs into timeout.

I wanted to perform a tune table on Ens_Enterprise_MsgBank.MessageHeader because this apparently has not been done yet - the Tune Table utility shows no entries for selectivity, etc.

I tried

 w $SYSTEM.SQL.Stats.Table.GatherTableStats("""Ens_Enterprise_MsgBank"".MessageHeader")

and got this error message

Hello DC Members,

Please welcome the new video on InterSystems Developers YouTube:

⏯ Ten Times Faster Queries with Columnar Storage @ Global Summit 2022

https://www.youtube.com/embed/lcEUPPKU6OY
[This is an embedded link, but you cannot view embedded content directly on the site because you have declined the cookies necessary to access it. To view embedded content, you would need to accept all cookies in your Cookies Settings]

Hello Community,

We're happy to announce that the InterSystems IRIS, IRIS for Health, HealthShare Health Connect, & InterSystems IRIS Studio 2022.2 is out now!

And to discuss all the new and enhanced features of it we'd like to invite you to our webinar What’s New in InterSystems IRIS 2022.2.

With IRIS 2021.1, we released a significant revision our SQL utilities API at %SYSTEM.SQL. Yes, that's a while ago now, but last week a customer asked a few questions about this and then @Tom Woodfin applied gentle mental pressure ;-) to make me describe the rationale of these changes in a little more detail on the Developer Community. So here we go!

In Cache 2018, we were using a macro in a query that looked like this:

select $$GetExtraSQL^GetExtra('B',bddtl.odnumb,bddtl.odsnum,bddtl.oddsc1) as "Description", * from sqluser.bddtl

We could save that query as a view, and there was no problem with it.

In IRIS, if we put that query into SQL in the management portal, it still works, but if we save that query as a view, when we try to run a query on that view, we get a big error message:

I just wrote up a quick sample to help a colleague load data into IRIS from R using RJDBC, and figured it's worth sharing here for future reference.

Ultimately it was pretty simple, aside from IRIS not liking "." in column names; the workaround is to just rename the columns. Someone better at R than me could probably provide some generic approach.