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.
I wonder how to select a conditional value from a Table column that has lists of values? Example: I have a table in the database that looks like below:
I noticed when creating Record Map's within Ensemble that it is creating a Persistent cache table as it translates the file and puts it into the Record Map data structure.
I was wondering if there was a way to add a column to the cache table that is Hidden from the record map, but is a calculated date value on when that record was inserted?
Do you know if there is any way to disable the automatic tuning IRIS is doing every time a class is altered? It just takes too long in our case and is holding the system, so I'd like to tune the tables when I decide it (again).
I've seen in the documentation that for 2023 version there is an option present in the backend. But not for 2022, so I assume a flag is needed somewhere.
I am calling a stored procedure over an ODBC connection and every time I call it there are several warnings written to the log event {Found no Parameter 1 (used as 1) for query}.I seem to be getting this on every query executed and that seems to happen a number of times the query parameters are per query and its filling up my disc.
1) Is there a way to suppress these warnings as the query seems to be executed and data written to the database?
I have a question regarding SQL insert/update from the mananger portal's SQL window.
I am trying to do an insert with a value that contains multiple spaces ($32) between two words. This is needed for a comparative reason. But the spaces are automatically trimmed away all the time. How should I write to keep these?
Example:
Insert into TableA (MyColumn) Values('xxxx yyyy')
then the spaces are trimmed away and it becomes: 'xxxx yyyy'
But I want to keep the spaces. How do I get around the problem?
I am using ExecDirect() method to get SQL resultset of type %SQL.StatementResult.
now I am fetch a column something like this rs.%Get("Amount") but if amount column is empty then it is return me empty string. If There is any way to set something like this in IRIS rs.%Get("Amount",0.00) if that is possible.
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 am new to cache. In an interview i was asked how to optimize a sql query.
I just said I will create index on conditions which are present in where clause. But as per interviewer I should check How query plan is getting executed. This will help in optimizing Sql queries.
I want to know what will be the answer for how to optimize SQL query in cache.
I'm using a JDBC driver to connect PGSQL -> Cache. I'm noticing when I run SELECT queries and COUNT(*) command against the same table, I get different result sets. I'm pretty new to Cache in general - so I'm trying to understand why these would be different.
Examples (TransID and InvNum should occur in every "row"):
SELECT COUNT(*) FROM ACCT.Services = 1,090,324 WHERE ACCT.Dept = 483
SELECT TransID FROM ACCT.Services = 1,085,776 WHERE ACCT.Dept = 483
SELECT InvNum FROM ACCT.Services = 586,023 WHERE ACCT.Dept = 483
I'm trying to add multiple rows at a time to a SQL table for a persistent object, but I can't seem to get the syntax correct. When I try the below it doesn't work.
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?
In show Query messages in the message viewer the head.%Id is always used. How do you do this via your own sql/objectscript as fast as the portal does a search as using dates searching Ens.MessageHeader on portal is slow.
For instance if you try do a search saying (TimeProcessed >='2023-06-01 00:00:00.000' and TimeProcessed <'2023-06-02 00:00:00.000') it is slow but using the portal the search would know this is head.%ID >= 5344549861 AND head.%ID <= 5347641372. How do you utilize this in your own queries as can't see the logic in EnsPortal.MsgFilter.Assistant
In Oracle database, the synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.
You generally use synonyms when you are granting access to an object from another schema and you don't want the users to have to worry about knowing which schema owns the object.
Is IRIS SQL sintax has something like this: (oracle sintax)
How to read the HL7 V2.5 messages irrespective of message type to Iris database ?
Source system sends HL7 messages of multiple message types to Intersystems through TCP and these messages are to be inserted/read into iris SQL database.
I want to connect the app to Cloud SQL but when I registered and created the deployment (trial) I got the error:
Did anybody face such an error? During the creation of an account, there was a message like 'username is taken' but the account have been created anyway. Maybe it could cause this problem.
So I am working with an inherited SQL query that queries 8 different tables. 5 of which have over a million records. I have 3 different servers.
Server 1, Server 2 and Server 3. They all have the same data/tables/structure across all servers. Server 3 has an Iris database engine and the other 2 are Cache 2015.1.4.
I have a problem with views and performance across the servers being inconsistent.
Thankfully Server 1 is the current live server that performs "fast enough". Server 2 is being synced from Server 1 and acts as a report server.
Is it planned that LOAD DATA takes into account several DATE/DATETIME formats with, for example, a parameter indicating the format used in the source data?
example :
LOAD DATA .../...
USING
{
"from": {
"file": {
"dateformat": "DD/MM/YYYY"
}
}
}
When I use Escape logic when inserting or updating Oracle Table I'm getting Max-Length exceeded error. With the original value the length is good but after I add Escape Logic, it causes value to be greater than max-length. The original value was "I visited O'Brien before heading out of town." and after added Escape logic it was "I visited O''Brien before heading out of town." Max-Length is 45.
INSERT INTO MyText
(text)
VALUES
('I visited O''Brien before heading out of town.')
/\
right here