#SQL

Hello Team,

 I need a help in the following error I am getting while running the SQL Query. I am running the SQL Query from different table using inner join. I run this everyday for last 90 days but for few days while extracting the data through this query I am getting this error. Please assist.

SQLException was thrown: Incorrect list format: 0 >= 0_SQLSTATE: S1000

SQLException was thrown: Incorrect list format: 0 >= 0_SQLSTATE: S1000

Error getting attributeColumn: Name

Error getting attributeColumn: Amount

Error getting attributeColumn: RollNumber

Thanks in advance !!

Hi everyone.

I have a use case where I'm using embedded SQL within a Business Process to interact with a SQL table. However, when it comes to deployment into our production, the table wont form part of the deployment package created from the production.

Beyond manually creating the table on the production system, is there a standard way of ensuring that a table needed for a class is created during deployment?

Hi Developers,

Enjoy watching the new video on InterSystems Developers YouTube:

⏯ Importing Relational Data Using a DDL Script

I have this code:

SELECTidclient

What i do to access the "client" name?

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 Data as %String (MAXLEN=100, MINLEN=200);

Index TSIdx on TS;

Index Extent [type=bitmap, extent];

}

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:

SELECTFROM

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

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

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

CREATE TABLE users (
    id SERIAL NOT NULL,
    name VARCHAR(30) NOT NULL,
    PRIMARY KEY (id)
)

I can add a new item there with an explicit id 

INSERT INTO users (id, name) VALUES (2, 'fred')

And while my id is autoincremented, I can omit it

INSERT INTO users (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

selectfromleft

The view GMMHTIE_PCMIS_Table_PCMIS.vwPCMISNewCasesCount references the table accessed via the SQL gateway connection, and contains the following:

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 Obj.

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. No tuning will be performed. Q

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##classINSERTINTO

So, how do I right use xColName in {xColName*C}?
}

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

CREATE TABLE some_table (
        id INTEGER NOT NULL, 
        x INTEGER, 
        y INTEGER, 
        z VARCHAR(50), 
        PRIMARY KEY (id)
)

And data

INSERT INTO some_table (id, x, y, z) VALUES (1, 1, 2, 'z1');
INSERT INTO some_table (id, x, y, z) VALUES (2, 2, 3, 'z2');
INSERT INTO some_table (id, x, y, z) VALUES (3, 3, 4, 'z3');
INSERT INTO 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.