#SQL

Hi All,

I have a below query for which I need help.

I have a persistent class User.Cars.cls. The properties of this class is mapped through Global mapping with global (^CAR(Date,CarSerialno,Seq)).

Property CarNumber as %String (TRUNCATE=1) [SqlFieldName= CAR_Number];

Mapping of 5th Piece of ^CAR global to CarNumber property.

For e.g.^CAR(Date,CarSerialno,Seq)=1^2^3^4^BMW 4567^6^7

To increase the security we have encrypted the database. 5th Piece is also encrypted in global (New value after encryption: 'ASAD=4367' through some tool technique).

Python has become the most used programming language in the world (source: https://www.tiobe.com/tiobe-index/) and SQL continues to lead the way as a database language. Wouldn't it be great for Python and SQL to work together to deliver new functionality that SQL alone cannot? After all, Python has more than 380,000 published libraries (source: https://pypi.org/) with very interesting capabilities to extend your SQL queries within Python. This article details how to create new SQL Stored Procedures in InterSystems IRIS Database using Embedded Python.

Hi All,

I am currently using D $SYSTEM.SQL.Shell() command or management portal to retrieve the data from database. I just learned we can also run cache table sql queries in MS access. MS access already installed on my machine. 

I need to ask how I can connect my database of USER or sample namespace in MS access to run these SQL queries.

Thanks in advance!!

Hi All,

I am having an SQL query (mentioned below). When I am running this query it is taking 90 min to retrieve all records (approx 1 million records).

Fetching data from 5 tables using inner join in cache. Relative cost displaying 10 million

I wan to confirm How this query can be optimized

Select Books.nam,Books.print,Books.Relativename,Books.Firstpage,Books.Lastpage,Books.trn,Books.lastissue,Books.firstissue,Books.person,Books.author,Books.price,Books.cd,Books.ab,Books.pf,Books.ju,Books.er,Books.qw,Books.qt,Books.mn
Records.qw,Records.er,Records.ty,Records.ui,Records.op,Records.as
OrderBooks.

Hi All,

I am a beginner in cache. I need to extract data from multiple tables (4-5) tables based on an SQL query . The data is more than 9 lakhs of records (i checked using count). The total number columns is 16. 

When I am running this SQL query through management portal, I am getting time out exception. Could you please suggest how I can retrieve those records. Which way will be good (Dynamic query using %SQL.Statement or Embedded SQL (not known to me) or Writing to a global will help or not).

Hi Guys

I've been using the example code %SQL.ExportMgr taken from here (an example from the intersystems community) an am having a few problems with it.

The code I have is below, and I'm having the following problems:

• Regardless of the dateformat property I use (e.g.  Set mgr.DateFormat = 1),  date data is always exported with format in YYYY-MM-DD
• Regardless of the timeformat property I use (e.g.     Set mgr.

Hi community,

I am working on Converting Non HL7 message (Using record maps) into HL7 message.

1.Can anyone share few details how to save Non HL7 message into SQL table and the converted HL7 message into SQL table

2.In Message Viewer is there any SQL tables are linked to the session id or where the information regarding the message will be stored? Will the message trace details are stored in globals or in SQL table,If yes can anyone share the details in which tables or globals will it be stored?

Joe

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:

SELECT id,room,client->name,functionary->name FROM rooms ORDER BY id

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"

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.

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

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

select  r.*, l.*
from    GMMHTIE_PCMIS_Table_PCMIS.vwPCMISNewCasesCount r
        left outer join GMMHTIE_PCMIS_Table_Local.vwPCMISNewCasesCount l
            on l.

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.

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=1
			WHILE $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(INSERT INTO Yan.