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.
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?
The last days I've work with the great new feature: LOAD DATA With this post I would like to share my first experiences with you. The following points do not contain any order or other evaluation. These are only things that I noticed when using the LOAD DATA command. It should also be noted that these points are based on the IRIS Version 2021.2.0.617 which is a preview release. So it may be that my observations do not apply to newer IRIS versions.
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.
Thanks to @YURI MARX GOMES we have seen a very nice example for DB migration from Postgres to IRIS. My personal problem is the use of DBeaver as a migration tool. Especially as one of the strengths of IRIS ( and also Caché) before is the availability of the SQLgateways that allow access to any external Db as long as for them an access usinig JDBC or ODBC is available. So I extended the package to demonstrate this.
Finding errors in your code or examining unexpected behavior is the main purpose of Debugging I will try to refresh the traditional tools away from the helpers you have in Studio, VScode, Serenji, .... to the basics which have been there before your preferred EDI used it in the background.
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.
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.
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 second piece in our series on 2021.2 SQL enhancements delivering an adaptive, high-performance SQL experience. In this article, we'll zoom in on the innovations in gathering Table Statistics, which are of course the primary input for the Run Time Plan Choice capability we described in the previous article.
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
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?
I need to generate a DDL file from a .cls class that already exists, the idea is to create a mirror table in SQL. Is it possible to do this export or do I need to do the CREATE TABLE manually?
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
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.
A good writer is supposed to draw you in with the title and bury the answer somewhere in the article. I suppose this makes me a bad writer – don’t think less of me, my whole feeling of self-worth comes from the opinions of internet strangers!
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?
By update
4m read
Open Exchange app
.
