I have a SQL query that I want to run against MS SQL from cache ensemble using the SQL outbound adapter. If I run this query direct from MS SQL Studio it take about 7 seconds MAX and returns about half a million rows. The row only contains one column it is a number all same size and if I run this query in the production in ensemble it takes for ever the production finishes without getting the response back. The same query run in cache outside the ensemble environment writing results to a file it returns results taking about 3minutes to complete.
Is there a way to use a class Parameter in embedded SQL without having to declare a local variable, in a similar way to how it is done with Property by preponing i% as described here. See my example:
I am experiencing a problem with an EDI process that uses a SQL Batch Service to connect to our DEV environment. However, when we point the EDI service to our TEST server, it errors out.
I have checked every single property on our TEST and DEV servers as well as the properties on the associated tables. They are identical. Nothing has changed in the SQL either.
See how cubes are constructed for use in business intelligence, and learn about SQL and MDX query languages. Physical and virtual cubes are used in InterSystems IRIS® Business Intelligence and Adaptive Analytics:
https://www.youtube.com/embed/l6XFj1JQ5Fw [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]
We need to export last 5 days Ens.MessageHeader data for a specific messages into file. I have written custom code to execute SQL query in object script.
Could somebody help me to iterate resultSet and write the data to text file.
I've checked the syntax with some public tools with adjusted Tablename "Fehlermeldung" -> `Fehlermeldung`. It seems to be valid, but cache does not accept it.
What is the SQL table name for it? How can I obtain it via ObjectScript?
A quick search doesn't show any methods and properties. Documentation is a bit "wrong" here saying that the SQL table name is the same. It will be at least 'x_y.z'.
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.
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?
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.
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.