#SQL

In this article I will explain the usage of %SQL_Diag.Result and %SQL_Diag.Message table along with all-new LOAD DATA functionality.

It is recommended to go through LOAD DATA documentation first. 

After successful operation LOAD DATA insert one record in %SQL_Diag.Result table and details are inserted in %SQL_Diag.Message table

Below is the basic command when table is already created and source file does not contain header row. 

LOAD DATA FROM FILE 'C://TEMP/mydata.txt' 
INTO MyTable

The file name must include a .txt or .csv (comma-separated values) suffix and both source and target have the same sequence of data columns.

Loading from File Source: Header

We recently encountered an Embedded SQL issue while upgrading to IRIS 2021.1, and thought the issue and workaround might be interesting to share.

Key takeaway:  Host variables in an ORDER BY clause of an embedded SQL query that is inside of a method don't work as expected. IRIS versions starting with 2020.1 are affected. As a workaround, add the host variable to the Method's PublicList list and "new" them so the embedded query has access to them.

How to execute it from the command line? I am able to use SQL in Management Portal, but I need to do it in Unix 

select * from Ens_Util.Log where ConfigName='SFTPTo.HQ.SAFETRACE.DFT' AND Text LIKE '%Put Stream%' and TimeLogged LIKE '%2021-10-16%'

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

II try to explore the new SQL LOAD DATA  feature in SQL comparing it to SQL INSERT

I'm stuck at this point:  INSERT INTO <table> (columns...) VALUES  (.....)
allows having not just simple column references but also ALL Standard SQL FUNCTIONS (at least)
example:  

INSERT INTO Test (ShortName,DOB) VALUES  (SUBSTRING(Name,1,4),TO_DATE(displayDate,'MM-DD_YYY'))

This works perfectly. 
BUT the same VALUE clause applied to LOAD DATA  fails in various ways:

Problem to Solve: When an HL7 message hits a particular Process, I need to use the placer order number therein to query the messages that came in over the last 24 hours on a particular Service and grab discrete pieces of data that came in the latest message that has that placer order number.

The code I'm (currently) using looks like this:

<assign property='FullMessage'     action='set' value='"PID||TEST"' />
<sql>
    <![CDATA[SELECT Full_Message INTO :FullMessage, head.ID As ID, {fn RIGHT(%EXTERNAL(head.TimeCreated),999 )} As TimeCreated,
head.SessionId As Session,
head.Status As Status,
CASE head.IsError WHEN 1 THEN 'Error' ELSE 'OK' END As Error,
head.SourceConfigName As Source,
head.TargetConfigName As Target,
head.TargetConfigName,
head.MessageBodyClassName As BodyClassname,
(SELECT LIST(PropValue) FROM EnsLib_HL7.SearchTable WHERE head.MessageBodyId = DocId And PropId=7) As SchTbl_ORC2,
EnsLib_HL7.SearchTable.PropId As SchTbl_PropId,
head.MessageBodyId As BodyId,
EnsLib_HL7.Message.%ID As Body_ID,
EnsLib_HL7.Message.RawContent AS Full_Message
FROM Ens.MessageHeader head, EnsLib_HL7.SearchTable, EnsLib_HL7.Message
WHERE Head.TimeCreated >= DATEADD(day, -1, GETDATE())
AND Head.TimeCreated <= GETDATE()
AND ((head.SourceConfigName = 'SITE ORM-ORU Inbound')
AND (head.TargetConfigName = 'SITE ORM 1 PreProcessor')
AND head.MessageBodyClassName=(('EnsLib.HL7.Message'))
AND head.MessageBodyId = EnsLib_HL7.SearchTable.DocId
AND EnsLib_HL7.SearchTable.PropId = 6
AND EnsLib_HL7.SearchTable.PropValue = '1234567890'
AND head.MessageBodyClassName='EnsLib.HL7.Message'
AND head.MessageBodyId=EnsLib_HL7.Message.%ID)
ORDER BY head.ID Desc
    ]]></sql>

<assign property='target.{PID:2}'   action='set' value='..Piece(..Piece(FullMessage,"PID",2),"|",3)' />

I have csv date file with date values like this "4/10/2021" for April 10, 2021. I defined a table with this property: Property TranDate As %Library.Date.

I capture error

[SQLCODE: <-104>:<Field validation failed in INSERT, or value failed to convert in DisplayToLogical or OdbcToLogical>] [Location: <ServerLoop>] [%msg: <Field 'dc_data_finance.transact.TranDate' (value '4/10/2021') failed validation Field ...

I do not really want to change TranDate to %String. How can I import "4/10/2021" into %Date property?

I read under Topics and Rules of InterSystems Datasets Contest:

• Class naming convention. Start the class names with: dc.data.your_name.class. E.g. if there is a dataset on trading data the class names could be: dc.data.finance.transaction, dc.data.finance.instrument.

I created a class “dc.data.finance.transaction”. I expected that it would not compile, because I remembered “Date” was a reserved word in SQL, and one of its properties was named “Date.”  To my surprise, the class compiled successfully. When I tried to execute query “select * from dc_data_finance.

I have a csv file with 13 columns.

The header row is:

"Date","Check","Merchant","Category","SubCategory","Memo","Credit","Debit","Bill Pay","Debit Card","Account","Balance","Status"

The first data row is:

"","","","","","","","","","","Cash","56",""

I have created a SQL table to LOAD DATA into. When I ran

LOAD DATA FROM FILE '/irisrun/repo/data/finance-transactions-08-Jan-2022_08-25.csv' into dc_data_finance.transact

I got Cash into “Account” property (correct) and I saw 56 in “Status” property. I want to skip the “Balance” column.

Is there some generic way to output a %SQL.StatementResult to CSV?

What is the correct data type for decimal numbers precision 5?

I tried %Numeric with Scale 5, but not worked, see:

Is it possible to call java programs inside SQL statements, like the new LOAD Data, or is this just internal?

Hi all,
I try to build a little project for the dataset contest, you can find it here on github: Openflights dataset
My plan is to use just plain SQL. So I've made a few experiments with the new feature LOAD DATA.

Unfortunately all loads produce errors like:  
 Error: [SQLCODE: <-400>:<Fatal error occurred>]
[Error: <<UNDEFINED>zExecute+83^%sqlcq.OPENFLIGHTS.cls4.1 *%qparsets>]
[Location: <ServerLoop>]

The statements I use looks like this:

This happend with the preview release "IRIS Version 2021.2.0.617". While this error occurs, most rows are written to the database, but some rows are missing.

I need iterate multiple times the same ResultSet. How can I do a rewind on it?

One of my colleagues had developed an interface in Health Connect (HealthShare 2019.1) to add large amounts of data to an external SQL Server database. The data comes from many text files with delimited rows and data for one table per file. There is a business process to read a file line by line and send an Insert Request to an operation. The request contains an Insert statement like ‘Insert into TABLE columns (col1, col2, … colZ) values (val1, val2, … valZ).’ The Health Connect operation utilizes Outbound SQL Adapter to insert one row into a table per request.

Hello Community,

I am trying to insert multiple values in a table. Below is the simple sql statement.

Insert Into TableX

values ('Name', 'Address', 'Phone')

How can i do multiple inserts(rows) in one single statement?

Values are not in another table, so i cannot use Select into.

Thanks,

Jimmy Christian.

Hi there,

Does anyone have an example of the method used to run an SQL query and generate the view in a Mumps routine.   I have several SQL queries created from a vendor that need to be converted and the corresponding view referenced.  I cannot seem to find any examples in IDX routines or in the  IRIS/Cache documentation.  Any help is appreciated.

-Alicia

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 .

This is the third article in our short series around innovations in IRIS SQL that deliver a more adaptive, high-performance experience for analysts and applications querying relational data on IRIS. It may be the last article in this series for 2021.2, but we have several more enhancements lined up in this area. In this article, we'll dig a little deeper into additional table statistics we're starting to gather in this release: Histograms

Hi members,

I have these data :

and need to exploit the first character of cot field (Library CDU classification) like this :

I get this result... it's approximatively what I want... but I need it in just one line !

With mySQL I get it with just GROUP BY rcddate

In Cache SQL I have to add {fn LEFT(cot,1)} to get the counts of each first character of cot... and seems {fn WEEK(rcddate)} doesn't make the job !

There is a trick ? or I make something wrong ?

kind regards.

I've been accessing Cache tables from a developer/reporting side, but am now involved in a project to create a data warehouse for our application.  I'm trying to find a query I can use to return the sizes of all the tables in the database, so we can identify the largest tables and handle those individually.  Can someone give me a query I can run against our Cache database to return the sizes of all the tables from largest to smallest?  

Thanks for the help

When i use &sql(SELECT ......)  in Cache I can watch the generated code
In the generated .int code and see what is happening.
Just with my normal rights

Now in IRIS I have just 4 line calling some class %sqlcq.***
With enough rights i find there is no such class but the generated .int routine
%sqlcq.IRISAPP.xEZgUjdXCCgQdZQPpRdOye1Ci2ue.1
That holds the code that i had in my .int on Cache

Can i switch this back somehow ?

Hi Dev Community,

I have a persistent Document class that has a FileName string property and another Question class that has an optional one-to-many relationship with Document.

I'm trying to add a SqlComputed property to the Question class (docFileName) where docFileName = Document.FileName if there is a related Document or an empty string if there isn't one. 

I'd prefer the property to be SqlComputed so that if Question.Document changes, Question.docFileName will automatically update.

Does anyone know the syntax for accessing the properties of a related object in the SqlComputeCode?

Thanks!

Hi,

Is there way to set up an automated batch job in the Management Portal to execute an SQL query.  Also, how can the related view be exported.  I have executed the SQL queries and see the view and created files manually.   I could not find any related documentation on the batch processes, but I thought batch or automated jobs could be set up via the Management Portal.  Any information is appreciated...

Thanks

Hey Developers, 

Please welcome the first session from the InterSystems Virtual Summit 2021:

⏯ Introduction to Embedded Python

I want to switch between different DSNs in business operations to query different servers, but the DO ..Adapter.DSNSet("DSNName") code will report a 15-second connection timeout error when switching DSNs. What should I do?

A permanent job opportunity has arisen for a Caché/Ensemble /Iris developer with at least 2 years experience. My client is a specialist resource provisioner of developers for high profile clients in the finance,  healthcare, retail, distribution and credit business that are mainly based in central London. My client is looking for a highly-motivated individual who thrives in an environment where problems are open-ended.

Hi Guys

I'm rather new to HealthConnect, and I've been doing a lot of work with the browser based SQL portal and don't particularly like it. Is there an alternative application that I can use, something like SQL Express or VScode with a plugin?

Andy

One of our apps uses a class query to support a ZEN Report and works just fine in that report, producing the expected results every time. We’ve since migrated to InterSystems Reports and noticed that, for a report using the same class query, 100s of extra rows with the same column values appear at its bottom.

We eliminated InterSystems Reports as the source of the problem by recreating the same “extra rows” issue with an Excel spreadsheet calling the same class query as a stored procedure.

What was the issue?

I want to INSERT a record in a database using JDBC in  OBJECTSCRIPT. At the same time, I want to obtain the insert ID. Is there a way to achieve this using the SQL Outbound adapter?

My code is something like this now:

Property Adapter As EnsLib.SQL.OutboundAdapter;

set sql = " INSERT INTO Prenotazioni_CUP "_
                  " (ID, cf
                  " VALUES (SEQTAB.NextVal, ?) "
set status = ..Adapter.ExecuteUpdate(.rs, sql, pRequest.cfAssistito)