Hi all,

I'm trying to use LOAD DATA to insert 11k (11,377) rows of data. LOAD BULK DATA is not available for the version of IRIS I am using.

After calling LOAD DATA it says only 5,500 rows has been inserted. The LOAD DATA docs says any error rows are skipped and a count of skipped rows can be found in %SQL_Diag.Result however there are no results here. There are no errors in the xDBC error log either.

Why have over half the rows been skipped?

3 3
0 238

Trying to identify which records in the %SYS.Audit table are fails.

Eg, as user "WORKER", I run an attempted a grant, the terminal returns:

[S1000][Iris ODBC][State : S1000][Native Code 112]
[SQLCODE: <-112>:<Access violation>]
[Location: <ServerLoop>]
[%msg: <User WORKER does not have required privileges to grant the privilege(s)>]
[ISQL]ERROR: Could not SQLExecute

but the record in the audit table gives

0 2
0 148

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:

0 1
0 167

I have table

        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


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

0 7
0 336


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

0 4
0 101

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

0 2
0 169

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?

0 2
0 146

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

0 12
0 225

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

0 1
0 102

Hi All,

I want to run an SQL like below

Select ID,Rollno,Marks,Name,Section,Teacher from Marks left outer join Student on Marks.StudentID=Student.ID

Now, the Student.ID is having the encrypted value which is not matching with Marks.StudentID (as this is clear value).

I can get the clear value using encrypted value as follow

^StoreValue("EncryptStudentID",Student.ID)=$LB("123"). That 123 is the clear Student.ID which I need to match in SQL query (ON clause).

0 2
0 96

Hi guys,

I'm trying find a way to create a row with the number of each row.

For example:

In the SQL above, i want a way to create a collum with the number of corresponding row, in this case:




In SQl SERVER i can use FOR, RANK or ROM_NUMBER to do this, but, i can't find a way to do the same in cache SQL.

Can someone help me?

Thank you.

0 3
0 88

Newbie here...

I am looking to use fhir to gather patients in our end point that have a certain diagnosis and or certain insurance.

Is this what fhir can be used for?

In the past I have done the normal SQL query/extract to CSV file. Looking to use fhir for this for future.

Thanks in advance for your time


0 1
1 91
· Apr 1


I have a query:

SELECT '['||Material->Sifra||'] '||Material->Opis AS Material,
SUM(MasaBlago) AS MasaBlago
FROM Tehtanje.Dokument
WHERE DatumDokumenta BETWEEN '01/01/2023' AND '04/01/2023'
AND (Material->Sifra %INLIST $LISTFROMSTRING('5,7',','))
GROUP BY Material

The query returns all rows where Material->Sifra is 5 or 7. That's OK.

If I want to get all rows where Material->Sifra is NOT 5 or 7 I use query:

0 2
0 178