Hello!

I'm new to the system I'm working on, and I have a part of a statement that needs to grab the previous month.

....and MONTH(TX3.date_of_service)= MONTH(dateadd(mm,-1,GETDATE()))

However, when it hits January, I'm left with month 0.

Does anyone know a workaround to grab December of the previous year, while still functioning normally for the rest of the current year.

Thank you! 

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.

Date range queries going too slow for you?  SQL Performance got you down?  I have one weird trick that might just help you out! (SQL Developers hate this!)*

If you have a class that records timestamps when the data is added, then that data will be in sequence with your IDKEY values - that is, TimeStamp₁ < TimeStamp₂ if and only if ID₁ < ID₂ for all IDs and TimeStamp values in table - then you can use this knowledge to increase performance for queries against TimeStamp ranges.  Consider the following table:

Class User.TSOrder extends %Persistent 
{ 

Property TS as %TimeStamp;

Property

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

Hello,
I tested the query on the Config.ConfigFile table of the Namespace %SYS, it returned 3 lines and a fatal error:

SELECT
*
FROM Config.ConfigFile

I tested the same query on other IRIS instances (including a 2021.1 instance) and got the same error.
Is it a bug in IRIS or is there something wrong with the installations?
Anyone who can test and leave in the answers if they got the same result, I would be grateful.
Thank you for your attention.

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! 

Hello,

I'm inserting a row using an insert statement from dotnet InterSystems.Data.CacheClient.CacheCommand.

Is there a way to get the inserted ID or IDs if I've inserted multiple rows?

Regards, Timo

I have table

CREATETABLE nodes (
        nameVARCHAR(50) NOTNULL, 
        parentVARCHAR(50), 
        PRIMARY KEY (name), 
        FOREIGN KEY(parent) REFERENCES nodes (name) ONUPDATEcascade
);

I put some data

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

Let's delete all

DELETEFROM 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>]

I am trying to migrate our SQL Connections from HealthShare Health Connect 2018.1.3 to IRIS HealthShare Health Connect 2022.2 using the Data Export/Import Wizard through the Management Portal. I am able to export the data fine, but I am running into issues trying to Import it into 2022.2 using the Data Import Wizard. When I run through the steps it is not importing everything from the txt file I created from 2018.1.3. I would rather not have to rekey all the connections if possible.

Here is the Background Task Error Log

Is there a way around this Validation error?

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

I have a table, with autoincremented id

CREATETABLEusers (
    idSERIALNOTNULL,
    nameVARCHAR(30) NOTNULL,
    PRIMARY KEY (id)
)

I can add a new item there with an explicit id 

INSERTINTOusers (id, name) VALUES (2, 'fred')

And while my id is autoincremented, I can omit it

INSERTINTOusers (name) VALUES ('ed')

So, this time, I don't know the id, and I want to somehow get it.

I could do it with LAST_IDENTITY() function, but it just uses %RowID, and have no relation to the primary id

Hi Developers,

Enjoy watching the new video on InterSystems Developers YouTube:

⏯ InterSystems IRIS Cloud SQL @ Global Summit 2022

Hi Community,

In this article I will explain how to display management portal dashboard by using Python Flask web and Boostrap framework with the help of embedded python
 

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

 Property Status As %String(VALUELIST = ",InProgress,Done,Canceled");

Trigger setStatus [ Event = INSERT, Foreach = row/object, Time = AFTER ]
{
 &sql(update CarDealer.Order set Status = 'InProgress')
}

Hi guys.

I need validate a field string in a table. It needs have a specific mask

I want use regex + CASE expression

There is any function to this purpose in SQL?

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

select  r.*, l.*
from    GMMHTIE_PCMIS_Table_PCMIS.vwPCMISNewCasesCount r
        leftouterjoin GMMHTIE_PCMIS_Table_Local.vwPCMISNewCasesCount l
            on l.created

Hi,

I

want to get the values from a serial property because my code depends upon the class serial class.

For example

Serial class

Class Data.Serial Extends %SerialObject

{

Property FirstName as %String;

Property LastName as %String;

}

Persistent class

Class Data.Persistent Extends %Persistent

{

Property MPID as %Integer;

Property Name as Name.Serial;

}

Now i need save MPID and Name(Serial class property into SQL Table ) so i am trying the below class

Class Data.TestUtil Extends %RegisteredObject

{

Method Savedata(MPID,FirstName,LastName)

{

Set tSC=0

Set Obj=##Class(Data.Persistent).%New()

Set Obj.MPID=MPID

Set

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

Table 'Ens_Enterprise_MsgBank.MessageHeader' is mapped to a readonly datababase.No tuning will be performed.0 ЉpTable 'Ens_Enterprise_MsgBank.MessageHeader' is mapped to a readonly datababase.

Hi,

when I using ObjectScript Trigger Code , I want to get whether the fields has changed by using "{fieldName*C}" ,  actually, fieldName in "{fieldName*C}" is a real field name ,but in my code, fieldName is a variable as follows:

SET stat=##class(%SYSTEM.SQL).GetColumns(tableName,.byname,.bynum,1)
		IF stat=1{
			SET i=1WHILE$d(bynum(i)){
				SET xColName=bynum(i)
				SET valComp={xColName*C}
				
				IF valComp=1{
					SET oldVal=1//{xColName*O}SET newVal=2//{xColName*N}
					&sql(INSERTINTO Yan.LogTableExt(Parref,ProperName,OldValue,NewValue) VALUES

Hello DC Members,

Please welcome the new video on InterSystems Developers YouTube:

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

This is not an issue in ObjectScript, due to its typeless nature. But it's essential for external programming languages that care a bit more about types of variables.

And in any case, it's still reproducible in ObjectScript. I have table

CREATETABLE some_table (
        idINTEGERNOTNULL, 
        x INTEGER, 
        y INTEGER, 
        z VARCHAR(50), 
        PRIMARY KEY (id)
)

And data

INSERTINTO some_table (id, x, y, z) VALUES (1, 1, 2, 'z1');
INSERTINTO some_table (id, x, y, z) VALUES (2, 2, 3, 'z2');
INSERTINTO some_table (id, x, y, z) VALUES (3, 3, 4, 'z3');
INSERTINTO some_table (id, x, y, z) VALUES (4, 4, 5, 'z4');

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.

Hi Community,

In this video, you will learn about exciting new ways to perform analytics using data in your HL7® FHIR® repository:

⏯ InterSystems IRIS FHIR SQL Builder: Sneak Peek @ Global Summit 2022

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!

Hello all; I am using a one-to-many relationship.  I have a Claim (one), in a relationship with Lines (many), but the Claim is storing a list of Lines IDs, rather than the Lines storing the Claim Id.  This seems upside down, and not what I expected.

Class Claim

{
    Relationship ClaimLineRel as ClaimLine [Cardinality = many, Inverse = Claim];
}

Class ClaimLine
{
    Relationship Claim as Claim [ Cardinality = one, Inverse = ClaimLineRel, OnDelete = cascade];
    Index ClaimIndex on Claim;
}

But the storage globals for Claim show data in the ClaimLine spot:

^ClaimD(1)=$lb("",$lb(2,3).

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:

ERROR #5540: SQLCODE: 400 Message: Process 604526 failed to compile Cached Query Class %sqlcq.R001.cls2136 with these errors: ERROR #5475: Error compiling

Recently viewed a demo on the new Dynamic Objects in InterSystems (we are still running 2016.1); together with the upcoming IRIS data platform I started thinking about possibilities of building dynamic databases.That is, I can imagine some projects were it might be really nice to store dynamic objects and then run sql queries on them without ever defining the fields of the dynamic objects (i.e.not at storage, but only if you run your sql query).If there is any system where this might be possible it is InterSystems Cache.

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. 

# Need a valid value for JAVA_HOME prior to calling library(RJDBC)
Sys.setenv(JAVA_HOME="C:\\Java\\jdk-8.0.322.6-hotspot\\jre")
library(RJDBC)
library(dplyr)
# Connect to IRIS – needs path to InterSystems JDBC JAR in your installation
drv <-

As we all know, Caché is a great database that accomplishes lots of tasks within itself. However, what do you do when you need to access an external database? One way is to use the Caché SQL Gateway via JDBC. In this article, my goal is to answer the following questions to help you familiarize yourself with the technology and debug some common problems.

Outline

• What are the connection parameters you need to connect to a remote database?
• What is the JDBC Gateway vs the Java Gateway Service in Ensemble?
• What tools and methods are available to debug issues?
• What are common types of problems and

How to delete this redundant task. Cannot press delete when logged in as _system