#SQL

Class Queries in InterSystems IRIS (and Cache, Ensemble, HealthShare) is a useful tool that separates SQL queries from Object Script code. Basically, it works like this: suppose that you want to use the same SQL query with different arguments in several different places.In this case you can avoid code duplication by declaring the query body as a class query and then calling this query by name.

How do you cancel a query that is executing in the Management Portal > System > SQL?

Does closing the browser cancel the query or does it continue to run in the background?

Is there any added benefit to using the Shell over the Portal? Perhaps there is a way to cancel/stop a query here?

$SYSTEM.SQL.Shell()

Thanks

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.

A quick question regarding to SQL Query Plan:
Why these two requests have different plans, in particularly, why second request needs temp file? To me, temp file is a bad thing which should be avoided, right?

1. select * from Test.Log where cdate = 1
2. select * from Test.Log where cdate > 1

Plan for 1:

Read index map Test.Log.cdateIndex, using the given %SQLUPPER(cdate), and looping on ID.
For each row:
Read master map Test.Log.IDKEY, using the given idkey value.
Output the row.

Plan for 2:

I like to use something like this:

select * from MyTable where MyFieldName like ‘%[^a-zA-Z0-9 !”%#$&”()*+,-./:;<=>?@]%’

to find field that contains special characters that are only allowed.

cache 2017.2.1 (Build 801_3)

when I look into the "sql statements" ( sql page of the management portal ) I have lots of old statements with a lot of them frozen. many of them are %sqlcq routines (I believe these are the SLQ's I run when testing/running random SQL queries in the sql page. )

I can clearly see where I can click on an individual query and untick an individual query to unfreeze it. or delete it. but thats a painful slow process. I can't see anywhere to delete/change multiple queries

I have an array of table names that I want to iterate over and for each table, get the number of rows. I was hoping I could do something like:

What is %SQLRESTRICT

%SQLRESTRICT is a special %FILTER clause for use in MDX queries in InterSystems IRIS Business Intelligence. Since this function begins with %, it means this is a special MDX extension created by InterSystems. It allows users to insert an SQL statement that will be used to restrict the returned records in the MDX Result Set. This SQL statement must return a set of Source Record IDs to limit the results by. Please see the documentation for more information.

Why is this useful?

This is useful because there are often times users want to restrict the results in their MDX Result Set based on information that is not in their cubes. It may be the case that this information may not make sense to be in the cube. Other times this can be useful when there is a large set of values you want to restrict. As mentioned before, this is not a standard MDX function, it was created by InterSystems to handle cases were queries were not performing well or cases that were not easily solved by existing functions.

How to calculate median and mean in SQL?

Hello,

I've got a table with many attributes and data. There is 10 000 000 registrations approximatly.

I need to make a research on this table with filters, paging, and order.

You can see an example of my SQL request :

SELECT * FROM ( SELECT TOP ALL * FROM ANCV_Data.Titre WHERE etatTitre = 'Emis' ORDER BY numRemise desc) v WHERE %vid BETWEEN 1 AND 25

Cause there are many data on my table, my SQL req is too long if I don't have index on my attributes BUT there are many associations possible...

BridgeWorks is pleased to announce a VDM, v9.1.0.1. This release includes the following updates:

Updates

• Historical Linking is now based off connection profile name
• Saved Formatting is now based off connection profile name
• Tables and Fields column headers no longer hide based on connection type

Bug Fixes

Does Caché have any features similar to PostgreSQL 'WITH RECURSIVE'? Something that allows recursion in SQL.

I have the following class:

Class P.RA.SII.F
{

Hello InterSystems Community,

We're excited to announce that we've completed our first Open Exchange submission for InterSystems platforms.

BridgeWorks VDM is an ad hoc reporting and graphical SQL query builder application that was designed for any user who needs access to their SQL projections in InterSystems Caché, InterSystems IRIS, InterSystems IRIS for Health databases as well as access to InterSystems DeepSee and InterSystems IRIS BI Cubes with minimal SQL scripting experience.

VDM features: