New post

#SQL

16

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

Hi Community!

Please welcome a new video on InterSystems Developers YouTube Channel:

Getting Sharded with InterSystems IRIS

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

0 1
0 463

Hi Community,

Watch this video to see how to connect to InterSystems Cloud Services from your Java application using the InterSystems JDBC driver:

⏯ Connecting to InterSystems Cloud Services with Java

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

0 0
0 225

Here're the technology bonuses for the InterSystems IRIS Cloud SQL and IntegratedML Contest 2023 that will give you extra points in the voting:

  • IntegratedML usage
  • Online Demo
  • Article on Developer Community
  • The second article on Developer Community
  • Video on YouTube
  • First Time Contribution
  • Community Idea Implementation
  • IRIS Cloud SQL Survey

See the details below.<--break->

0 2
0 355
Question
· Apr 1, 2023
NOT %INLIST

Hi.

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
ORDER BY %EXACT 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 255
Question
· Mar 23, 2023
Saving data into SQL table

Hi Community,

I am trying to save data into SQL table but each entry getting saved twice in the SQL table. Is there any reason data is saving Twice in the SQL table

I have created a %Persistent class for the fields

Please find the business operation below

Class Patient.DBOperation Extends Ens.BusinessOperation

{

Parameter ADAPTER = "EnsLib.SQL.OutboundAdapter";

Property Adapter As EnsLib.SQL.OutboundAdapter;

Parameter INVOCATION = "Queue";

0 6
0 253

I need a stored procedure where I can execute multiple delete from statements.

This will work to create a stored procedure, but when I add in other delete from, get error invalid sql statements when try to create

CREATE PROCEDURE DeleteSpecimenTwo
(IN Specno VARCHAR(50))
BEGIN
DELETE FROM PCONT WHERE PSPECNO = :Specno;
END;

This doesn't work

CREATE PROCEDURE DeleteSpecimenTwo
(IN Specno VARCHAR(50))
BEGIN
DELETE FROM PCONT WHERE PSPECNO = :Specno;

DELETE FROM PSPEC WHERE PSPECNO = :Specno;
END;

0 1
0 217

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

Dean-O

0 1
1 243

Is it possible to retrieve the SQL-Executing process ID (PID) from a process spawned from a UNION %PARALLEL? I have a value set in the SQL-Executing process that I need to replicate in the UNION %PARALLEL spawned process(es). The SQL-Executing process writes the value to a global subscripted by its PID so determining the SQL-Executing PID from the spawned process is what I need. I expected $ZPARENT from the spawned process would hold the SQL-Executing PID but the PID returned is different.

With thanks,

Michael Hill

0 11
0 371
Question
· Jan 30, 2023
SQL Query Help in $ListGet

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 165
Question
· 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

0 1
0 161

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

0 4
0 218
Question
· Jan 24, 2023
SQL Query needs optimization

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 276

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 302

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 327

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?

0 2
0 142

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!

0 4
0 157