#SQL

Hi,

The question is about queries on the System->SQL page. I have a class with a few columns on a global and the test output looks as expected, below. This works as expected as well returning a single row: select * from Utils.RoutineAuditReport where counter=4. However, other columns give an odd error like " Field 'AG' not found in the applicable tables^ SELECT * FROM Utils . RoutineAuditReport WHERE UserR = "AG". As you can see below, both the UserR column and the AG entry in it do exist. What could be the problem?

Thanks in advance,
Anna

Application to import 12 Datasets along with 43 tables dynamically by using LOAD DATA command which loads data from a source into an IRIS SQL table.
List of Datasets

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

Presenter: Anton Umnikov
Task: Identify your slowest SQL queries and tune them for better performance
Approach: Use InterSystems’ query profiling and analysis tools. Discuss how system configuration can affect performance

This session will show you how you identify the weakest link in your application SQL and introduce you to the fine art of tuning those queries. To do this we will take a look at InterSystems query profiling and analysis tools, as well as how system configuration can impact SQL performance.

Problem: Obscurity on how our SQL engine works

Content related to this session, including slides, video and additional learning content can be found here.

SetIdentityInsert call controls the ability of the user to specify a value for the IDENTITY property when saving a new object, a value for the IDENTITY column or an explicit ROWID value in an SQL INSERT. If IDENTITY_INSERT is false and the user specifies an explicit IDENTITY or ROWID value when saving a new object or inserting a new ROW then an error condition is reported.

Setting takes effect immediately and lasts for the duration of the process or until SetIdentityInsert is called again.

My question is how can I change this setting system-wide?

A Quick Start to InterSystems Cloud SQL Data in Databricks

Hello everyone,

I want to fill data into my select component with sql request and parameter.

I tried to follow this example https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY...

I need to call a method because my parameter can be different. I tried something like this but it doesn't work...

The 2021.2 release of the InterSystems IRIS Data Platform includes many exciting new features for fast, flexible and secure development of your mission-critical applications. Embedded Python definitely takes the limelight (and for good reason!), but in SQL we've also made a massive step forward towards a more adaptive engine that gathers detailed statistical information about your table data and exploits it to deliver the best query plans. In this brief series of articles, we'll take a closer at three elements that are new in 2021.2 and work together towards this goal, starting with Run Time Plan Choice.

It's hard to figure out the right order to talk about these (you can't imagine how often I've reshuffled them in writing this article!) because they fit together in such a nice way. As such, feel free to go on a limb and read these in random order .

Hi All,

I am facing some issues. I am having a SQL query that is executed using %SQL.Statement ExecDirect() method. Now I want to convert it into Embedded SQL using &SQL().
But where clause is dynamic in my case and It may contain 3-4 clauses.

What do you do if you want to have the ID field have a meaningful name for your application?

Sometimes it comes to pass that when you're making a new table that you want to have the unique row identifier (a.k.a. IDKEY) to be a field that has a name that is meaningful for your data. Moreover, sometimes you want to set this value directly. Caché fully supports this functionality and it works Suppose you have a class Test.Kyle. The data will be stored like so:

^Test.Kyle(IDKEY)=$LB("",Field1,Field2,...,Fieldn)

Hi,

I've a Service utilising the Adapter EnsLib.SQL.InboundAdapter, which uses a Credentials item set with the details of a local SQL account. This currently works, however, we're looking to use the credentials of an AD domain account.

The domain account is a member of an AD security group, which has the required permissions on the source SQL database. I've checked that access is possible with this account via SQL studio.

Hello everyone

I am new to cache. In an interview i was asked how to optimize a sql query.

I just said I will create index on conditions which are present in where clause. But as per interviewer I should check How query plan is getting executed. This will help in optimizing Sql queries.

I want to know what will be the answer for how to optimize SQL query in cache.

Thanks in advance!!

Wondering what version the Information.Schema schema was introduced, so that I can decide how to query the metadata for user defined tables.

Thank you!

I'm playing with some anayltic queries against FHIR server tables. The HSFHIR_X0002_S_Patient.addressCity table contains a lot of cities which names contain german charachers such as ä, ö and ü.

The following query works fine:

select value from HSFHIR_X0002_S_Patient.addressCity

But this one converts city names to uppercase, and characters with umlauts are lost, so instead of "Köln" or "München" I see KOLN and MUNCHEN: