#SQL

Temporary tables are tables available for a current process only (and destroyed when process ends).

What are you approaches to creating temporary tables?

1. Process-private Globals storage can be used as a data global in storage definition. That way, each process can have its own objects for the class with ppg storage.  Here's how. Here's how 2.
2. InterSystems TSQL supports #tablename temporary tables. A #tablename temporary table is visible to the current procedure of the current process. It is also visible to any procedure called from the current procedure.

Hi Community!

There are two general ways to execute arbitrary SQL  in serverside ObjectScript code: EmbeddedSQL and ObjectScript SQL a.k.a. Dynamic SQL.

E.g. if we want to get the value of the property of instance with a certain ID using SQL we can do:

&sql(SELECT Name INTO :name FROM Sample.Person WHERE ID=1)

write name

Same result with %SQL.Statement:

set rs=##class(%SQL.Statement).%ExecDirect(,"SELECT Name as name FROM Sample.Person where ID=1")
  do rs.%Next()
  write rs.name

Hi VSCode users!

Just want to check with you, is this settings.json file ideal enough if I:

1. Want VSCode to connect to IRIS in docker?

2. Want to use VSCode SQL Tool?

The connection settings file:

{
    "files.associations": {
    
        "Dockerfile*": "dockerfile",
        "iris.script": "objectscript"
      },
    "objectscript.conn" :{
      "ns": "IRISAPP",
      "username":"_SYSTEM",
      "password": "SYS",
      "docker-compose": {
        "service": "iris",
        "internalPort": 52773
      }
    },
    "sqltools.

In today's landscape, enterprises have grown substantially in scale, amassing vast amounts of data. This data is collected from a plethora of sources including different applications, databases, and other channels. Given the diversity and volume of this data, it's only logical for these enterprises to seek a deeper understanding of what their data entails. Some of the data can be stored in IRIS, and it can be reasonable to be able to add this data to a data lake too.

The Internet now offers many different tools for such tasks, that do not yet support IRIS, but it's achievable.

Hi! We have received a request to create a new rule on CachéQuality to identify when a developer uses double quotes (" ") within any SQL statement.

We have been asked many times about SQL validation rules, and we would like to open a debate to allow everyone discuss what would you like to be checked on a SQL statement.

Current examples are for basic situations:

• Using SQL.Statement class:

Set stmt = ##CLASS(%SQL.Statement).

Hi Developers!

How do you handle errors with &sql a.k.a. embedded SQL?
I think I found a universal error-handling template which works in any situation:

&sql(SELECT * FROM Sample.Person)

if SQLCODE < 0 throw ##class(%Exception.SQL).CreateFromSQLCODE(SQLCODE,"Context description")

What is your choice?

Hi,

InterSystems IRIS has long supported the obvious translation functions required to for converting to upper or lowercase to enforce case-insensitive string comparison (e.g. in ObjectScript with $zconvert) and sorting (e.g. with SQL collation functions, not to be confused with NLS collation). Customers in international contexts have at times used custom workarounds to also treat accent insensitivity or even more advanced normalization duct tape. We’re looking to address such use cases at the system and SQL level to increase convenience for this international audience, which is well represented on the Developer Community.

Hi,

we're planning some work on our SQL Query Plan functionality for a future release of InterSystems IRIS and are interested to hear how you're using them today, or what'd keep you from using them. Rather than try and fit everything in a rigid survey, I thought a simple thread on our beloved DC might also reveal some use patterns that we support or could do a better job on. 

Hi All,

We have few queries which are simple selects . For simplicity let's say there is a query that joins two tables and gets few columns and both tables have no indexes.

Select Tab1.Field1, Tab2.Field2
From Table1 Tab1
Join Table2 Tab2
On Tab2.FK = Tab1.PK
When we do query plan for this it shows approx 6 million,  however if we make a simple adjustment to the query

Select Tab1.Field1, Tab2.Field2
From Table1 Tab1
Join Table2 Tab2
On Tab2.FK = Tab1.PK
WHERE Tab1.Id > 0 (Which will always be the case)

The query plan comes down to few thousands. So approx 99% improvement.

Currently, the SQL privileges (SELECT, INSERT, UPDATE, DELETE) are managed at the tables level, which can be very tedious when you have to administer many roles in an organization, and need to keep them sync with a constantly evolving data models.
By managing privileges at the schemas level, will allow to give SELECT and other DML privileges to *all* or *several schemas* to a role|user, fixing the need to manually synchronize the new tables|views to the roles.

If you agree, I invite you to vote for this idea.

I am currently adding a field to our Existing messaging from Epic, however there might be a possibility I need to back load data into the Ancillary system. While I have the previous messages that can be sent, they do not have this additional field that I am adding to the message.

I can do a lookup against Epic Clarity SQL Database; however, I don't want to throw a wrench into the workflow if the system cannot connect to the Epic Clarity SQL Database.

• What is the best solution for this?

Hello everyone, 

My team is currently developing guidance and best practices for the generation, storage, and deployment of TUNE TABLE statistics across development and production environments. With that in mind, we want to get an idea of what methods teams in the field have developed for handling this data. In particular, we’d like to know the following: 

1. How often do you use TUNE TABLE in your development vs. production environments? 
2. Do you utilize the $SYSTEM.SQL.Stats.Table package to generate and export TUNE TABLE statistics as files? If so: 
   1. Do you store these in source control?

In the past, I've created custom SQL operations, but now I had something trivial to do, so I decided to take EnsLib.SQL.Operation.GenericOperation out for a spin. There's no example in the docs, so it was a little tricky. Here's what I ended up doing:

In my external database, I have 'mytable' with two fields 'id1' and 'id2'. Here are the pertinent Business Operation settings:

SQL: select id2 from mytable where id1 = ?
Input Parameters: [1] *id1
RequestClass: Ens.

When I'm asked questions about SQL, I like Intersystems staff at the WRC, generally ask about the Query Plan.  My feeling is that even before you run a query you should examine the results of Show Plan to confirm that the code is going to give me the results in the manner I would expect.  If I expect it to leverage an index and I see that it is not then I go back and look at what I might have done incorrectly and try to re-write the query to get the index behavior I'm looking for.