#SQL

It is common to test a connection sending a general SQL instruction to the database, in Oracle we send select 1 from dual, for example, and in IRIS what the best option?

Hi! I have a complex SQL select query which I execute via %ResultSet. It also includes a UNION. The select statement within the union should only be done when an external parameter is set, and I'm not sure of way of doing that within SQL, other than something like

SELECT FOO FROM BAR

WHERE (some conditions)

UNION

SELECT FOO2 FROM BAR2

WHERE :doUnion = 1 AND (some conditions)

...this works, but I don't think it's particularly optimal as I believe it will still execute the second SELECT regardless of :doUnion. It's also sort of hard to read...

Hi folks!

Sometimes when we develop a mockup or PoC there is a need for a simple interface that will provide data in IRIS in JSON against SQL queries.

And recently I contributed a simple module that does exactly that:

accepts SQL string and returns the JSON.

How to install? Just call:

zpm "install sql-rest"

If you install it in a namespace X it will setup a /sql endpoint to your system that will accept POST requests with SQL string and will return the result for you for the data available in the namespace X.

I'm participating in the Developing with InterSystems Objects and SQL with Joel Solon. The course is very nice and I will share with you some tips I got during the training. Tips presented in the day 3:

I have vendors asking for verification that messages for locations are coming through to them. I can get generic ADT_A01 type of numbers in Activity. I'd really like to get some good SQL queries that can give me a count of MSH.4s (for example) for a day for X Operation. I'm not sure which table to look at for that information.

Hello all,

I have a Business Service which retrieves data via a SQL adapter and writes the data in text file. My production is doing the job, but instead of the Business Operation output being one file, I am getting many files, with 1 , 2 or 3 rows only.

Any help is appreciated.

Thank you

Hello fellow community members,

I would like to offer my services as an Intersystems Professional and am available to work on projects.

I have more than a decade experience into Intersystems stack of technologies including IRIS, Ensemble, Healthshare, Healthconnect, Cache Objectscript, Mumps, Zen, Analytics etc. with companies spread over US and UK involved in multiple domains.

In the Inbound SQL Adapter settings, is it possible to specify more than 1 field as the Key Field Name?

Because of the way the Query is being index in Ensemble by the Key Field Name, sometimes transactions get missed and I would like to see if we can add an additional key to the mix to ensure all the transactions are picked up. In this case the InterfaceTrigger is an ID that is auto generated by the table, and I would like to use that as well to ensure we don't miss transactions, and it does not throw any warning messages when it executes the Delete Query.

Does anybody know if there is an easy way to know if the execution of the command $System.SQL.PurgeForTable went well/wrong?

The documentation describes that a string is returned, but in fact there is a nice Quit "" in the code.

Maybe inside PurgeForTable^%apiSQL there is any process variable set when it goes well/wrong?

Thank you very much!

DataGrip is a multi-engine database environment targeting the specific needs of professional SQL developers, DataGrip makes working with databases an enjoyable and productive experience.

To work with InterSystems IRIS from DataGrip you'll need to add InterSystems JDBC driver first (once per DataGrip) and after that add all your InterSystems IRIS connections.

Part 1: Add InterSystems IRIS JDBC Driver

1. Go To File → DataSources

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

I am trying to create an ODBC class that includes all of these as records, but I don't see how I can since the first record has 3 subscripts, the rest have 4 subscripts:

Here is my current storage definition:

I have a case where I need to look up the NPI provider against an External MS SQL database to retrieve our Provider Identifier to send to a downstream system. In the past I would use a Business Process (BPL) to connect to the outside MS SQL via JDBC and get that information for me. But I was thinking instead of creating a BPL process to do this, would it be better just to link to the outside MS SQL database table to retrieve the information in a SQL statement within a DTL?

We are trying to convert some of our SQL Service Integration Service jobs from Visual Studio to Ensemble. If we execute a Stored Procedure within SQL Server Management Studio it is returning approx 12,000 rows. However when Ensemble executes the same Stored Procedure it is only returning 250 rows.

Is there a limitation to EnsLib.SQL.Snapshot?

This is how we are calling the Stored procedure