#SQL

SQL is a standard language for storing, manipulating and retrieving data in relational databases.

By likes

Question

Dom GMMH NHS Trust · Mar 17, 2022

How to perform multiple row SQL insert to a Linked Oracle Database record using DLLImport

Hi, we are trying to run a dynamically generated Oracle Insert SQL script using Do $SYSTEM.SQL.DDLImport("Oracle",""," sql.txt",[Error Log]) to insert multiple rows into a Linked Table within HealthConnect to an Oracle database.

We have tried using statements that are accepted within Oracle but not when using Intersystems DLL along the lines of :

#SQL #InterSystems IRIS

0 4

0 488

Question

Scott Roth · Jun 1, 2022

Cache Query for HL7 NACK

I would like to capture any NACK's that is sent back to the Operation. The Operation is already setup to "Save Replies/IndexNotOK's", but I would like to see if we can query Cache and pull those NACK's into an extract.

Is this possible?

Thanks

Scott

#Business Operation #HL7 #SQL #Caché

0 4

0 224

Question

Jun Suzuki · Aug 9, 2022

SQL : ROUTINE ERROR when inserting from another table

Following the documentation to insert into tableA from tableB in the SQL query box from the Management Portal :

INSERT INTO tableA (col1, col2)
SELECT col1, col2
FROM tableB

This straightforward query raises the following error :

#Management Portal #SQL #InterSystems IRIS

0 7

0 503

Question

reetan selvaraja · Dec 9, 2022

Link Table wizards is not working with SQL joins

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

#SQL #InterSystems IRIS

0 4

0 131

Question

Virat Sharma · Jan 30, 2023

SQLComputed Code issue

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

#SQL #Caché

0 1

0 164

Question

Evgeny Shvarov · Apr 3, 2023

How to list all the tables available in a current database?

Hi developers!

What is the way using SQL to list all the tables available in the current database/namespace?

#SQL #InterSystems IRIS

0 4

0 568

Question

Scott Roth · May 15, 2023

Linked Table connection through JDBC

Ran into an issue with our upgrade from HealthShare Health Connect 2018.1.3 to IRIS HealthShare Health Connect 2022.1, that I thought I would reach out about, I already have a ticket open with WRC and started a chat on Discord. We have a couple of MS SQL tables that I have setup as Linked Tables within Cache to query NPI, Patient Account Information, Visit information, etc... When we upgraded last week we found that within a day or even a half a day that we were having problems with our JDBC connections.

#JDBC #Key Question #SQL #Health Connect #InterSystems IRIS

0 19

0 399

Question

Steven Henry Su... · Aug 4, 2023

Calling Second Method inside the Main Method

Hy,

I have a question how to call another methode inside the main method ?

#ObjectScript #SQL #TrakCare #VSCode

0 17

0 252

Question

Prasanth Annamreddy · Oct 9, 2023

How to get ORU messages Base64 content without Truncate from IRIS DB

I have run the query and got ORU^R03 message from SQL Editor and reprocessed the message, But pdf not opened and the base64 content looks truncated.

Is there a way to get full content from the message from query? Why the content is not visible in DB that I can see in Message Viewer?

Thank you so much for your time to read this,

#SQL #InterSystems IRIS for Health

0 2

0 236

Question

Michael Wood · Jan 19

INSERT OR UPDATE only inserts

I am trying to do a INSERT OR UPDATE SQL query, but the query always inserts new rows to table. Is it how I defined the table or am I mising something in the query?

table defines as,

Class AH.AHCOMMON.Tables.HospDepartmentData Extends %Persistent [ Language = objectscript ]
{

#SQL #InterSystems IRIS for Health

0 1

0 87

Question

Ishan Srivastava · Mar 14

Need to make ODBC connection to fetch data from SQL Server to IRIS 2021.1. Once we are able to query the data from SQL server, need to Pass it in ensemble production

#ODBC #SQL #Ensemble #InterSystems IRIS

0 1

0 77

Question

Padmaja Konduru · Apr 11

Select View throwing Error "References to an SQL connection must constitute a whole subquery"

Hi,

I have created view from External table joins internal persistent table. Create VIEW worked but Select * from Viewxxx throwing error.

"References to an SQL connection must constitute a whole subquery"

https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY...(SELECT%20*%20FROM%20Mylink.

#SQL #InterSystems IRIS

0 2

0 57

Question

Scott Beeson · Apr 27, 2016

How to determine SQL access available?

I have two servers in our TEST environment. One with HSBUS and one with HSREGISTRY. Let's call them "HSBUS01" and "HSDB01" respectively.

Now, I can access HSBUS with my SQL client by using the JDBC driver pointed to hsbus01.full.name.com and port 1972 and specifying a namespace of "HSBUS". I am, however, unable to access the HSREGISTRY instance via SQL with a similar combination of information.

#Caché #SQL

0 2

0 395

Question

Chip Gore · Jun 21, 2016

Is there an Embedded SQL Cursor "rewind"?

Hi -

I'm trying to rewind a cursor back to the first row after looping part of the way through the implied result set, but I'm not finding a way to make this happen, is there some such iterator variable or directive that I can leverage to accomplish this?

I could code around it by pulling identifiers and/or values into a local array, and then hand code up an iterator over my local results copy, but this feels like a "redesigning of a wheel" approach, and I thought I would check before I start down this path.

Thanks

#SQL #Caché

0 2

0 461

Question

Alexandre Borges · Nov 29, 2016

Is there a tool to get the slowests queries ?

#SQL #Caché

0 6

0 486

Question

Eduard Lebedyuk · Feb 1, 2017

How can I override the getter for serial property?

I want to override getter for a serial property as a whole, because getter code depends on a class in which a property is defined.

Let's say I have CS.Serial as a serial class:

Class CS.Serial Extends %SerialObject
{
Property Year As %Integer;

Property Month As %Integer;
}

And CS.Persistent as a persistent class:

#Caché #Object Data Model #SQL

0 7

0 535

Question

Arockia Raj B · May 19, 2017

SQL/Non-SQL

Is the InterSystems Cache has SQL or Non-SQL concept?

#Data Model #SQL #Caché

0 9

0 507

Question

CM Wang · Jul 3, 2017

How to escape the single quote in the single quoted string

I have a stored procedure like below:

Call USER.SP('select * from Sample.Person where SSN='aaaaa'','0','S')

How could I escape the single quotes for aaaaa? I try double single quotes but in vain.

Thanks you.

#Object Data Model #SQL #Caché

0 11

0 2.3K

Question

CJ H · Oct 14, 2017

Multiple joins in a SQL query

HI,

I have a query like below but its syntax is not accepted by Cache.

I would like to perform a left join on two tables first and then make a inner for this result with another table.

Given the constraint that we only allow one SELECT in the query, it is possible to achieve this semantics ?

Thank for your help.

Select *
FROM ( sample . employee e
LEFT JOIN sample . company c
on c . id = e . id ) g
JOIN sample . vendor v
on v . %id = g . attr

#SQL #Caché

0 3

0 985

Question

Roger Beeman · Dec 13, 2017

SQL or Cache Function?

Hi -

I'm creating a custom report in Health Insight via SQL and one of the requirements says that I must include patients who are >= 13 years old (which is easy) but I also need to include patients who WILL be 13 years old as of December 31st of current year that the report is ran in.

I see that there is an Age column and an AgeInMonths column in HSAA.Patient. and of course a DOB column. I'm wondering if it is possible to do this purely using SQL (and SQL functions) or if I will have to incorporate a COS function to achieve this requirement.

Any suggestions?

#Caché #SQL

0 12

0 1.3K

Question

CJ H · Feb 19, 2018

%NOMERGE for query optimization

I don't quite understand the %NOMERGE mentioned in the documentation.

http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_from#RSQL_from_nomerge

Is there any specific example provided to illustrate the idea?

Thanks.

#SQL #Caché

0 3

0 330

Question

Minsu Kim · May 9, 2018

Question: Are there any restrictions on the number of SQL commands?

Hello Global masters.

I am junior developer about Intersystems cache in South Korea.

I want to develop process management program on server.

My code :

#Beginner #Object Data Model #SQL #Caché

0 5

0 317

Announcement

Harry Tong · Jul 9, 2018

New Video: What's Lurking in Your Data Lake?

Hi Community!

Please welcome a new session recording from Global Summit 2017:

What's Lurking in Your Data Lake?

https://www.youtube.com/embed/FGr5CAyWcGY
[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]

#Global Summit 2017 #SQL #Video #Global Summit

0 0

0 313

Question

Emanuel Lazar · Sep 5, 2018

SQL Gateway, errors while building proxy classes

SQL gateway.

There is a basic problem with sql gateway,

while building the proxy methods, with:

#API #Caché #SQL

0 4

0 309

Question

Yaniv Ben Malka · Nov 6, 2018

Detection of last update timestamp of each class row (DSTIME ?)

Hi,

Most of my classes are mapped from Globals. I want to access Cache classes from a BI software through ODBC connection.

'Last update' information does not exist in most of the classes. My question is whether there is a 'last update' timestamp that is automatically generated for each line in classes I can extract to external systems?

Thanks

#Globals #ODBC #SQL #Caché

0 9

0 761

Question

Eduard Lebedyuk · Jan 17, 2019

String or Stream in the same persistent property?

I have a persistent class.

I want to store one of the properties there as a stream or a string depending on a size.

99% of values would be strings (less than $$$MaxStringLength characters) so I don't want to store everything as streams.

What do you think of this approach?

What's the best architecture to implement in this situation?

#Beginner #SQL #Caché

0 4

0 549

Question

Jimmy Christian · Apr 23, 2019

SQL CREATE Table Name with System defined variable

Hello all,

I am still learning CACHE and have below question.

In one of my ZEN method i am creating a table using

#SQL #Caché

0 2

0 212

Question

Nicki Vallentgoed · Oct 7, 2019

&SQL and ORDER BY ID DESC

I know &SQL returns only one result but is

&SQL(SELECT ID FROM Cinema.Film ORDER BY ID DESC)

and

&SQL(SELECT TOP 1 ID FROM Cinema.Film ORDER BY ID DESC)

the same in terms of processing required?

#SQL #Caché

0 7

0 355

Question

Matías Peña · Nov 15, 2019

Connect Oracle with InterSystems

Hello everyone,
It is my first post in the community. Very recently I started working using IRIS, creating services with Cache.
Today they gave me a new task and I'm really lost.

How can I use an oracle database to create services using iris?

Throughout the day I was looking for a guide to use a remote Oracle database. but unfortunately I only managed the connection in SQL Gateway

#SQL #InterSystems IRIS

0 5

0 884

Question

amine benchaou · Feb 20, 2020

Sql request

Hi All,

I need to build a search using a lot of filters, what's the best way to implement my sql requests.

1 sql request per property, or only one "big" sql request ?

what's the disadvantage of using lot of indexes ? Thank you

KR,

Amine

#SQL #Caché

0 5

0 258