Recently our team have been getting requests to pickup a large amount of data from API nightly (e.g. using ODATA to loop through pages) and placing that into MS SQL databases.
My question is, what is considered the best practice to get data from API and route to external SQL database WITHOUT persisting any messages/traces etc?
I'd like to know if there are any issues if an index is inserted into a table without running the %BuildIndices() method.
It's important to note that data inserted before the index is not important for retrieval, so it's not a problem data inserted before the index don't show up in queries.
The reason why I'm asking this is that I'd like to avoid index reconstruction on big tables which I need to inser such index.
Is there a way to find the median in Intersystems Cache SQL? I know it is not available as an aggregate function. Also in SQL Server I could try something like:
SELECT
(
(SELECT MAX(Score) FROM
(SELECT TOP 50 PERCENT Score FROM Posts ORDER BY Score) AS BottomHalf)
+
(SELECT MIN(Score) FROM
(SELECT TOP 50 PERCENT Score FROM Posts ORDER BY Score DESC) AS TopHalf)
) / 2 AS Median
However, there is no PERCENT Keyword in Cache as well. Any suggestions?
We are trying to track down the source of Orphaned messages and noticed that we are unable to query EnsLib.HL7.Message with any kind of WHERE or ORDER BY clauses in our SQL statement.
I know EnsLib.HL7.Message is a system table, but is there a way we can add additional Indexes to the table to make the query run better/faster without affecting the system?
Hi, we are trying to run a dynamically generated Oracle Insert SQL script using Do $SYSTEM.SQL.DDLImport("Oracle",""," sql.txt",[Error Log]) to insert multiple rows into a Linked Table within HealthConnect to an Oracle database.
We have tried using statements that are accepted within Oracle but not when using Intersystems DLL along the lines of :
I'm new to cache, come from an oracle and sql server background. In oracle and sql server I could write basically a stored procedure like script and pass it in as text to the command to execute.
Example:
Below would be the _sqltext
DECLARE @Id INT;
select id into @Id from something;
if @Id = 9
BEGIN
do something
END
The _sqltext would work in sql server, what would be the equivalent for cache for it work?
I want to have a script that can run from the usual unix, linux, or aix command line. It has to be able to get into an irissession and use set statements to get data using sql. It seems like I'm in a catch-22. When I use the irissession SERVER command at the command line, I can't run a script. When I put the irissession SERVER command in a script, it won't run anything in the script after that.
My goal for this script is to get this information and put it into a file which I can then parse.
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've checked the syntax with some public tools with adjusted Tablename "Fehlermeldung" -> `Fehlermeldung`. It seems to be valid, but cache does not accept it.
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.
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'.
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:
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?
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.
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.
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 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?
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.
By date
