#SQL

This error is sometimes seen while viewing a listing in InterSystems IRIS Business Intelligence:
ERROR #5540: SQLCODE: -99 Message: User <USERNAME> is not privileged for the operation (4)

As the error suggests, this is due to a permission error. To figure out which permissions are missing/needed, we can take a look at the SQL query that is generated. We will use a query from SAMPLES as an example.

In Episode 4 of Data Points, we welcome @Benjamin De Boe to the pod to discuss some of the things you can do to optimize your SQL queries in InterSystems IRIS. We've all heard — either from ourselves or from others — the "this runs too slowly" complaint. I thought Benjamin did a great job walking through many of the things within IRIS you can look at with your queries to see what can be improved.

https://5e18edf067eb59-03854285.castos.com/player/153376
[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]

Hi all,

i‘m traying to run a sql LIKE Query on an attribute datatype LONGVARCHAR.

I also try allready with the SUBSTRING(Attribute,1,...), when i run the query, the resultat is empty without errors.

The Query:

select text from table

where text like ‘%abc%‘

many thx for help

BR

Darko

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