New post

#SQL

17

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

As you know in Caché / IRIS you have the possibility to define a property as Multidimensional as documented here:
https://docs.intersystems.com/iris20201/csp/docbook/DocBook.UI.Page.cls?KEY=GOBJ_proplit#GOBJ_proplit_multidim
and the explanation of how to use it
https://docs.intersystems.com/iris20201/csp/docbook/Doc.View.cls?KEY=GOBJ_proplit#GOBJ_proplit_multidim_values

Though the access is quite comfortable (in traditional COS sense) there are 2 main restrictions that hurt:

#1) It is not saved to disk unless your application includes code to save it specifically.
#2) It cannot be stored in or exposed through SQL tables
there are some more
I'll show how to overcome these limits

14 1
2 417

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 63

For my recent tutorial, I used %Query Wizard of Studio.

Could any of the VSCode experts please show me how to generate

Query Statement with input parameters, CONTAINID , ROWSPEC
and the required class methods
- <queryname>Execute()
- <queryname>Fetch()
- <queryname>Close()

I was just not able to do it.
​​​​​​​

3 3
0 83

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 286

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).

1 6
0 139

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 73

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 102

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

0 2
0 115

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

0 13
0 424

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

0 5
0 244

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

1 6
1 301

This is not an issue in ObjectScript, due to its typeless nature. But it's essential for external programming languages that care a bit more about types of variables.

And in any case, it's still reproducible in ObjectScript. I have table

CREATE TABLE some_table (
        id INTEGER NOT NULL, 
        x INTEGER, 
        y INTEGER, 
        z VARCHAR(50), 
        PRIMARY KEY (id)
)

And data

INSERT INTO some_table (id, x, y, z) VALUES (1, 1, 2, 'z1');
INSERT INTO some_table (id, x, y, z) VALUES (2, 2, 3, 'z2');
INSERT INTO some_table (id, x, y, z) VALUES (3, 3, 4, 'z3');
INSERT INTO some_table (id, x, y, z) VALUES (4, 4, 5, 'z4');

1 11
0 279

Recently viewed a demo on the new Dynamic Objects in InterSystems (we are still running 2016.1); together with the upcoming IRIS data platform I started thinking about possibilities of building dynamic databases. That is, I can imagine some projects were it might be really nice to store dynamic objects and then run sql queries on them without ever defining the fields of the dynamic objects (i.e. not at storage, but only if you run your sql query).

0 3
0 551