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.
After linking in Oracle Table with Field Column's Data Types of NUMBER, my updates into these table fields are resulting in data that is being rounded to 2 decimal places. I insert a record with 1234.1234 and 1234.12 is stored.
It appears Cache xDBC might be manipulating my values prior to sending to Oracle. Is there a setting or system parameter that is controlling this? If so, is there a way to relax this from occurring so the values I send are being stored in Oracle with the same values?
I'm wanting to write a query in SQL that will return a row with a count for each day for a given month or year for a specific operation or configname. The following is a start but I'm not finding what I want in the documentation to parse out the TimeLogged field of the table. Nay help is appreciated.
SELECT count(TimeLogged), ConfigName FROM Ens_Util.Log where TimeLogged like '2021-07%' and ConfigName = 'operation_Name' group by TimeLogged
How strange this is to me. I'm querying a table against a numeric(15,2) column call max_client_resp_service. When I query max_client_resp_service > 0 or any number, I get results, but they are not always right, for example where max_client_resp_service > 5 returns values less than 5, e.g.
I'm working in an application that uses %SIMILARITY to find matches among a set of documents that vary greatly in length. It's generally good but I've noticed issues with ranking short partially-matching documents over longer documents that match the search string entirely.
When for any particular reason I need to update a record and don't want to pull the triggers, the keyword %NOTRIGGER can be used. But I've been trying to do the same when I change the row using the object approach, but I can't find it. Anyone knows if it's possible to avoid pulling triggers when working with objects?
Sadly, the utility "DISABLE^%NOTRIGGER" doesn't seem to exist ;-)
Apologies if this isn't the correct forum but here goes..
I'm accessing an Intersystems database from MS SQL Server using Intersystems ODBC driver and using OPENQUERY statement to read metadata of tables using
SELECT * FROM OPENQUERY([<linked server name>], 'SELECT * FROM INFORMATION_SCHEMA.COLUMNS')
but I get an error message saying INFORMATION_SCHEMA.COLUMNS cannot be found.
Intersystems SQL documentation does reference this table so is there some other way to access this table or is it maybe a permissions issue?
When I'm asked questions about SQL, I like Intersystems staff at the WRC, generally ask about the Query Plan. My feeling is that even before you run a query you should examine the results of Show Plan to confirm that the code is going to give me the results in the manner I would expect. If I expect it to leverage an index and I see that it is not then I go back and look at what I might have done incorrectly and try to re-write the query to get the index behavior I'm looking for. To that end, there's a poll below to ask you if think moving the Show Plan button as the first button would be
I'm running into an issue performing UPDATES that I'm not getting on INSERTS. It's probably obvious, but I'm just not seeing it and could use a little help.
I'm going over an HL7 message and depending upon varying criteria, the relevent variables will get items added to them like the following:
Set patientId = pRequest.GetValueAt("PID:3")
Set sqlColumns = sqlColumns_",patient_id"
Set sqlValues = sqlValues_",?"
Set par($i(p)) = patientId
After compiling the variables, I check to see if accession number is found in the table.
I'm close with this but I'm not sure how to grab JUST the GT1.3 data. I know I can do a substring but finding the 3rd | is a tad tricky. I've not been this deep in SQL for 15 years.
SELECT SUBSTRING(hm.RawContent, (CHARINDEX('GT1',hm.RawContent)), 50) as NameDesc FROM Ens.MessageHeader as em, EnsLib_HL7.Message as hm where em.Status = 'Suspended' and em.MessageBodyId = hm.id
I am trying to work out if there are any methods available to be able import a result set returned by SQL query into a persistent class.
I have to connect to some legacy SQL databases through SQL Gateway and run some queries. I need to inster the rows returned into a class to then be able to do a %JSONExport to produce a JSON object. I know I can iterate through the resultset and insert one row at a time into the class but was wondering if there is any other/direct way of importing the resultset rows into a class.
I have created a custom role in IRIS for users to provide limited view-only access for querying tables in the HSANALYTICS namespace. WinSQL has been installed on a Windows server (WinSQL Lite version 14.0.244.784) and an ODBC connection entry has been created in WinSQL for the users to log in and run SQL queries. The custom role provides the roles/resources included in the attached file. The WinSQL System DSN entry defined is:
Health Insight UAT HSANALYTICS 64-bit InterSystems ODBC35
We have a need to track Database changes over time - down to the SQL level of granularity if possible. Such as: User xyz runs routine ^abc and we get something similar to a changelog that tells us: table A had this value updated, insert, update etc....
Is that possible using IRIS level tools (Audit Log, Journal File, etc...) , is there a way to convert the global sets and kills from the journals into SQL level changes?
https://www.youtube.com/embed/f7A-wbNkIic [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]
I am trying to populate a table using the sql Data Import Wizard. The input file is a tab delimited text file. But the import keeps failing with a 104 error showing validation for the columns which use %Library.TimeStamp and %Boolean datatypes is failing. Yet when I insert values into the table through a SQL insert command, the values get saved correctly in the table.
For the TimeStamp format in the wizard form, I am choosing YYYY-MM-DD-HH:MI:SS because there was no option for this format: YYYY-MM-DD HH:MM:SS.
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
https://www.youtube.com/embed/tgq_rvb3ZLg [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]
In this article we are going to show the results of the comparision between IRIS and Postgress when handling Astronomy data.
Introduction
Since the earliest days of human civilization we have been fascinated by the sky at night. There are so many stars! Everybody has dreamed about them and fantasized about life in other planets.
I need to show the absence of data, so I have to join the list of predefined values with a result of a select statement. However, it seems like Table Value Constructors in JOIN are either not supported, or I do not understand the syntax. Basically, I am going for something like this:
Select v.valueId, m.name
From (values (1), (2), (3), (4), (5)) v(valueId)
left Join otherTable m
on m.id = v.valueId
By update.png)
Open Exchange app