#SQL

In MS SQL Server i can do this:

SELECT 
Category, CrewNumber, MedicalCertificationDate, Seat, SeatbeltUsed, Sex, ShoulderHarnessUsed, ToxicologyTestPerformed, childsub
INTO #tempfemale
FROM Aviation.Crew
WHERE Sex = 'F'

The code would create  a new temporary table with the fields defined from Aviation.Crew.   I cannot find how to do this in Cache SQL in the documentation.  Can someone show me the correct syntax or other solution?

Hi guys,

I'm working on a project with my client.
They have a visit table which has about 7,000,000 records. The table is used in a random search page witch holds 20+ conditions to be combined.
The table is defined as below:

CREATE TABLE SQLUser.

I'm looking for an efficient way in DBeaver to filter system tables (ex: belonging to a schema starting with "%").

By using a user with the %All role, DBeaver shows us a long list of system schemas, which forces us to go down the list before accessing the user tables.

Kindly help me on the below points

1)Concatenate the Date column and Time column.

2)Get the current date only without time.

3)Find the current age using DOB column.

Hi all,
I am trying to execute a query like the below code.
set statement = ##class(%ResultSet).%New("some_class:query_method").   // here query method is empty and with rowspec some columname

statement.Execute(param1)

I want to fetch data type of column value returned from above. eg - Name - VARCHAR, amount - INTEGER etc.
How can I get it. Or if not possible directly. Is there any other way to validate or get datatype of values returned. Line we have type() in python3

Hi all

I'm struggling to insert into a table, and it is baffling me. The insert statement is now very simple (I was using a complex INSERT SELECT statement but I'm trying to narrow down the problem).

Insert statement:

INSERT INTO Phu_Replay_Schema.

Hi developers!

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

Hi folks!

Consider I have a class "X.Y.Z"

What is the SQL table name for it? How can I obtain it via ObjectScript? 

A quick search doesn't show any methods and properties. Documentation is a bit "wrong" here saying that the SQL table name is the same. It will be at least 'x_y.z'.

Any help is very appreciated!)

Hello!

I wonder how to select a conditional value from a Table column that has lists of values?
Example: I have a table in the database that looks like below:

For example, I want to know which Itemname it is for number 10 that is in the IDList. What should this SQL syntax look like? I have tried %INLIST and other things but only get blank value. How should the SQL-syntax for that query look like?

Greatful for all tips.
Regards,
Michael

Is there a way I can call %Save() on a %Persistent object and at the same time prevent indices from being built?

Hi

I get the following error when I am trying to create a linked server connection via MS OLE DB Provider for ODBC Drivers in MS SQL Server Management Studio. The message I get is:

The OLE DB provider "MSDASQL" for linked server "IRIS" reported an error. The provider did not give any information about the error.
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "IRIS". (Framework Microsoft SqlClient Data Provider)

I have setup the InterSystems ODBC setting on the server.

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');

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.

Hi,

I'm trying to load some data into IRIS using LOAD DATA in the IRIS terminal however I am getting blocked by this error. I have read the documentation on this page trying to set up a gateway connection but get the same Connection cannot be established error.

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

Hi everybody,

I am writing an SQL query of the following form:

SELECT
(SELECT COUNT(*) FROM DataTable WHERE Condition1 AND Condition2),
(SELECT COUNT(*) FROM DataTable WHERE Condition1 AND Condition3),
(SELECT COUNT(*) FROM DataTable WHERE Condition1 AND Condition4),
(SELECT COUNT(*) FROM DataTable WHERE Condition1 AND Condition5),
user.id
FROM UserTable user

where Condition1 depends on user and is the same for all four COUNT(*) subqueries. This query is very slow, and I believe that it can be sped up significantly if Condition1 would be checked once rather than four times.

Hey everyone,

I'm currently running into a very weird issue to where I am trying to connect with a 64 bit version of SQL Server Management Studio (SSMS) to a HealthShare instance. I have created a System DSN using the Drivers (image below) that were downloaded with the Client version of the install and I'm able to successfully connect using my credentials.

After I have my System DSN (image below) configured, I go into SSMS and add a Linked Server that is referencing the System DSN that was created. Also, I change the provider to be Microsoft OLE DB Provider for ODBC Drivers.

Is there a way to create a table for the results of a SQL statement ?

Example: Consider the below query and results.

Select ID,Name,DOB,Gender from EMS.EMS

RESULT 

On IRIS version 2024.1.0.262.0 I have a table that store streams as %GlobalCharacterStream.
But depeding on the blob being saved I get the error: [Error: <<MAXSTRING>]

I had that with Caché, but to solve that I'd just enable the long strings on the page System Administration > Configuration > System Configuration > Memory and Startup > Enable Long Strings.
But for Iris I can't find that configuration, I looked on every config page from the portal and couldn't find it.

Btw this blobs are stored on Caché and the system using IRIS is reading from Caché and saving on IRIS.

Hi, what is the easiest way to clear all the tables which store FHIR data? The most straitforward approach is to get a list of tables using the information_schema.tables view, then execute dynamic SQL with the "delete from" clause, but perhaps some helper class has already been written for this purpose?

This applies to embedded and dynamic SQL queries in Caché ObjectScript. If I attempt to terminate the process via Management Portal, nothing happens. Neither does Ctrl C nor closing the terminal window in which my program is running, nor setting a stop flag in a global which is read by the program on each loop iteration. The only way to stop the query appears to be restarting the Caché server (which is running locally on my PC).

Hi all! 

How can I get the ODBC drivers for IRIS 2024.1 if I am only using IRIS 2024.1 Community Edition? 
This page gives you the information: "The official location for customers to download the latest released and fully supported InterSystems IRIS driver packages is the WRC download site." My first thought on this is: Why does Intersystems make it so difficult for users to use their database? Why is the ‘official’ driver package hidden behind a login wall? Shouldn't Intersystems be happy if someone wants to work with their database, wants to connect?

Hi,

when I using ObjectScript Trigger Code , I want to get whether the fields has changed by using "{fieldName*C}" ,  actually, fieldName in "{fieldName*C}" is a real field name ,but in my code, fieldName is a variable as follows:

		SET stat=##class(%SYSTEM.SQL).GetColumns(tableName,.byname,.bynum,1)
		IF stat=1{
			SET i=1
			WHILE $d(bynum(i)){
				SET xColName=bynum(i)
				SET valComp={xColName*C}
				
				IF valComp=1{
					SET oldVal=1 //{xColName*O}
					SET newVal=2 //{xColName*N}
					&sql(INSERT INTO Yan.

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 :

Error #5475: routine compilation error : %sqlcq.TESTuNAMESPACE.cls305. Errors: %sqlcq.TESTuNAMESPACE.cls305.cls
ERROR: %sqlcq.TESTuNAMESPACE.csl305.1(14) :
<UNDEFINED>parseExtFrom+19^%qaqcmx
*mt("f", "1^TABLEA") :

Could someone help me decipher this error message ? Thanks for your help.

Hello,

I have a global whose structure is multi-level and I am trying through a class and a SQL query to display a table which includes all the values ​​and levels.

Hi,

I

want to get the values from a serial property because my code depends upon the class serial class.

For example

Serial class

Class Data.Serial Extends %SerialObject

{

Property FirstName as %String;

Property LastName as %String;

}

Persistent class

Class Data.Persistent Extends %Persistent

{

Property MPID as %Integer;

Property Name as Name.Serial;

}

Now i need save MPID and Name(Serial class property into SQL Table ) so i am trying the below class

Class Data.TestUtil Extends  %RegisteredObject

{

Method Savedata(MPID,FirstName,LastName)

{

Set tSC=0

Set Obj=##Class(Data.Persistent).%New()

Set Obj.MPID=MPID

Set Obj.

Hello everyone,

I am looking for the syntax or the way to use a class created in the "BNA" Namespace (my application) from the %SYS Namespace.

Here is the context:

I have a "BNA" application contained in the "BNA" NS, this application provides a user creation functionality. This feature creates both the user in a table in the application and in the Iris system.

I created an initialization script for my database to be able to reset it at will, this script starts by emptying the database of this data, then initializes the basic data.

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

  Table 'Ens_Enterprise_MsgBank.MessageHeader' is mapped to a readonly datababase. No tuning will be performed.0 ЉpTable 'Ens_Enterprise_MsgBank.MessageHeader' is mapped to a readonly datababase.

Most of the databases with SQL access offers the DATE_TRUNC function or analogs. This helps to easily truncate a date by any part while keeping the DateTime in a valid format

Hi, how do I create a trigger that will only set the value of the "Status" field for new objects, not for all available?

Class CarDealer.Order Extends (%Persistent, %Populate)
{

 Property Title As %String

 Property Status As %String(VALUELIST = ",InProgress,Done,Canceled");

Trigger setStatus [ Event = INSERT, Foreach = row/object, Time = AFTER ]
{
 &sql(update CarDealer.Order set Status = 'InProgress')
}