SQL | InterSystems Developer Community

Question

David.Satorres6134 · Nov 13, 2024

Hello all,

We have our system with AutoParallel enabled:

USER>w ##class(%SYSTEM.SQL.Util).GetOption("AutoParallel")
1

But whenever I try to run any sql the autoparallel does not work. For example, this simple query:

When I force it with %PARALLEL we can see it will effectively run in parallel:

#Databases #SQL #InterSystems IRIS

1 9

0 225

Question

KEVIN TAYLOR · Sep 17, 2024

Stored procedure for insert into and select from tables

How to add the following to a stored procedure (Cache Studio)
1. Select from a few tables and insert result into a TABLEA.
2. Then select data from the TABLEA, apply some SQL logic, insert results into TABLEB.
3. SELECT * FROM TableA
UNION ALL
SELECT * FROM TableB

#SQL #Caché

0 7

0 220

Question

Jude Mukkadayil · May 13

Append a string in a update query

Hi,

Can anyone please tell me how to append a string using a update query.

For eg : "Hello" is the string . I need to append the string world and it needs to be "Hello world".

Thanks

Jude

#Databases #SQL #InterSystems IRIS #InterSystems IRIS for Health

0 17

0 174

Question

Kaveh Kaviani · Oct 30, 2024

dynamic SQL in object script code

i am trying to embed a dynamic SQL into Objectscript code , but the %NEXT() will return 0 , however when i copy the same query and run it in the SQL bit it will give me the result i want
the code :

#ObjectScript #SQL #HealthShare #InterSystems IRIS #InterSystems IRIS for Health

0 2

0 157

Question

Don Martin · Jul 22, 2024

SQL Gateway getClob() errors in Redshift and Postgresql

I'm trying to use the EnsLib.SQL.Operation.GenericOperation component in a production to read a column from a Redshift table that is set up as VARCHAR(65535) and am getting the following error.

An error was received : ERROR #5023: Remote Gateway Error: JDBC Gateway getClob(0,1) errorRemote JDBC error: Cannot convert the column of type VARCHAR to requested type long..

#SQL #Health Connect #InterSystems IRIS for Health

1 5

0 148

Question

Nimisha Joseph · Dec 2, 2024

Issue with Parameterized Queries in EnsLib.SQL.OutboundAdapter - Ensemble

Hi everyone,

I’m using EnsLib.SQL.OutboundAdapter in my InterSystems Ensemble operation to execute an insert statement in Sql Server Management Studio 20. While inline queries (e.g., constructing the query string with _ concatenation) work fine, I'm facing issues when trying to use parameterized queries.

Serialized JSON: {"CHI":"2894","Forename":"Janis"}

#SQL #Ensemble

0 10

0 136

Question

Patrik Spisak · Nov 28, 2024

Embedded SQL - CODE 100

I have issue with one of my queries. That query work fine for ages, but suddenly stop works and giving me SQLCODE 100 in the routine.

#Management Portal #SQL #InterSystems IRIS

0 2

0 135

Question

Ashok Kumar T · Jul 20, 2024

Index Build uses SetMapSelectability

Hello Community,

#Databases #Indexing #Performance #SQL #InterSystems IRIS for Health

0 3

0 130

Question

Oleksandr Kyrylov · Jan 8

MySQL JDBC connection, Linked Table issue

Hello, community.

I have a problem with running a SQL query on a linked MySQL table.

The connection works fine, but the following query throws an error:

SELECT TOP 10 * FROM linkedinternal_test.persons

[SQLCODE: <-400>:<Fatal error occurred>]

#Databases #ObjectScript #SQL #InterSystems IRIS for Health

0 6

1 113

Question

Andrew Sklyarov · Mar 26

"Bad value for type long" on select from PostgreSQL via EnsLib.SQL.OutboundAdapter

Here is my code:

#Java #JDBC #SQL #InterSystems IRIS

0 3

0 110

Question

Ashok Kumar T · Jul 14, 2024

Additional Cached Query generation while execution

Hello Community,

I've seen two cached queries are generated while executing a query. So, Why the additional query is generated and what is the use of it.

#SQL #InterSystems IRIS #InterSystems IRIS for Health

0 3

0 103

Question

Richard Prouvot · Nov 13, 2024

SQL CLASS NODE SYNTAX

I HAVE A NEW SQL CLASS THAT DISPLAYS ENTRIES BASED ON THE FIRST 2 NODES OF A GLOBAL. I FOUND OUT THAT THE CLASS ALLOWS FOR AN ADDITIONAL NODE(s) TO BE INSERTED IN THE "User Specification Node:" along the delimiter and the Piece in the NewStorage Map1 for "ModifyDDDD" shown below. it is not working.

#SQL #Caché

0 5

0 94

Question

Kurro Lopez · Oct 18, 2024

How to get the native sql error in SQL outbound adapter?

Hi all,

We have an restriction in a SQL database with a unique index.

We want to catch that exception when it tries to insert or update a value that violates the unique index condition.

#SQL #HealthShare #InterSystems IRIS for Health #Health Connect #InterSystems IRIS

0 3

0 93

Question

Dmitrij Vladimirov · Nov 22, 2024

Calculate STD DEV with ObjectScript

Hence the question: is there a way to do that?

The goal is to get data (from half a thousand to 3-4 thousands lines) from DB, calculate standart deviation then use it as logical condition in analyzer.

For example IF std > custom_value = show_the_result ELSE null

There is a STDDEV(MDX) method used in Analyzer but it is a measure and it can not be used as logical condition (correct me if i am wrong)

#Analytics #Analyzer #MDX #ObjectScript #Python #SQL #InterSystems IRIS

0 2

0 90

Question

Ashok Kumar T · Jan 8

<MAXSTRING>CompileRtns+295^%occRoutine in SQL execution

I tried executing the SQL JSON_TABLE query with large JSON string(more than 200000 characters) and I got the below error. I'm curious about this under the hood workflow and how does it reach reaches MAXSTRING.

#JSON #ObjectScript #SQL #InterSystems IRIS #InterSystems IRIS for Health

0 2

0 82

Question

Sylvain Guilbaud · Mar 7

Dynamic SQL Queries with a ROUTINE (CODE) database mounted in read-only mode: ERROR #5002: ObjectScript error: <UNDEFINED>ShowPlan+6^%apiSQL *sqlOrig

Using IRIS 2024.1.2 when I mount the routine database as read-only I get an error when executing dynamic SQL queries.

What globals need to be mapped in a read-write database in order to allow execution plans to be written?

#SQL #InterSystems IRIS #InterSystems IRIS for Health

0 1

0 73

Question

Steven Henry Su... · Jan 13

How to Give A Status Old and New based on First Input Data

Hy Friends,

I have a little problem, I need to diplay the data based on input status (first time and after)

this is the data:

and this what I need to display

the notes become new because it is the first time that the data has been inputed, and it will become old if we have the data before (2nd data, 3rd data the notes will become old).

#SQL #InterSystems IRIS #HealthShare

0 1

0 63

Question

Yone Moreno Jiménez · Jun 23

Get Last Message DateTime, Received by Production Component, with ObjectScript code.

Hello, good morning, thank you so much for reading this question. ☺️🙂👍

We are developing a code to get information about our Production's items: services, processes and operations.

We know we can get various configurations of a given item: Category, Port, Enabled...

But we wonder how we could get the date time of the last mesage (most recent) received in an item.

To give a code snippet a small section of the code we have developed (and tested), it looks like:

#Code Snippet #CSP #Interoperability #Monitoring #SQL #HealthShare

1 2

0 40