New post

#SQL

16

SQL is a standard language for storing, manipulating and retrieving data in relational databases.

Question
· Aug 18, 2016
Read only Async mirror setup

I have setup an async reporting mirror member with Read only access. My problem is that if I try to do any sql reporting against that data I am getting errors. I am sure that this is because the DB is read only, but I had assumed that setting up a reporting mirror would handle this.

I there a setting or mapping I am missing?

0 7
0 661

I experience this constantly with Cache SQL. Especially when querying the ATNA log.

SELECT TOP 400000 * FROM HS_IHE_ATNA_Repository.Aggregation ORDER BY ID DESC

That took 12 seconds. I then upped the number to 500,000 and it took 185 seconds.

Shouldn't the execution time scale proportionately?

If I run the 500,00 query again it takes 2.4 seconds.

0 4
0 418

Hi All -

I was wondering if the below query could be enhanced to automatically look back 24 hours from the current datetime the query is run. As is now I, of course, have to update the date range in the WHERE clause manually.

The query is just getting all MessageBodyClassNames, counting them and then doing an AVG on TimeCreated and TimeProcessed. Nothing too complex.

SELECT MessageBodyClassName, count(ID) as Count_Of_Messages, avg(datediff(ss, TimeCreated, TimeProcessed)) as avg_processing_time_in_seconds

FROM %PARALLEL Ens.MessageHeader

0 1
1 2.6K

Hi,

I have a class with around 400k lines and 60 columns. Class storage is Cache SQL storage (Mapped from a global).

I want to create multiple indices on certain fields.

I am familiar with two approaches:

1. Create a new map (Index type) on a pointer global.

2. Create a bitmap index

Which approach is more recommended to be used in the case I described? If there are any other approaches, I will be happy to hear.

Thanks :)

0 11
0 703
Question
· Dec 10, 2017
SQL IN OPERATOR

Hi,

I'm trying to use the IN operator in SQL.

does not work, can anyone tell me why that could be?

for example:

SELECT * FROM TafnitCore_Logistics_TransferPackage_DB.PackageData WHERE ID IN ('1||1||9852553062' , '1||1||9852553061' ) ORDER BY Building

And I get the following error:

0 3
0 418

I came across How to import a tab separated text file into a SQL table programmatically?, which appears to indicate that I can pass the filename and all records will be imported. However, when I put use the Import method only a single record gets imported. However, all records are imported if I use the management console to import the file by selecting it and choosing the options.

Has anyone ran into this in the past?

0 3
0 646
Question
· Oct 30, 2018
ID vs %ID in tables

What is the difference between %ID and ID in a database table? Both seem to reference the same column labelled ID.

For context, I am trying to create a viewer class for an existing persistent class.

Let us call the persistent class A, with SqlTableName = OldA.

The viewer class will be B with SqlTableName = A and ViewQuery = {select %ID, <other fields> from <some other class with the same fields as A>}

0 1
0 1.1K

Let Say I have
Class Carrier Extends %Persistent

Property Employee As Array of Class Employees

Class Employees Extends %SerialObject

Property Name As %String

Property Address As Array of Class AddressDetail

Class AddressDetail Extends %SerialObject

Property Street1 As %String

Property Street2 As %String


I can get value for Employee Name as Employee_Name.

But I want to Get Value of Address Street1 and Street2 of Class AddressDetail using SQL Query
How can I get that?

0 3
0 367 

Class ICT.Experiments.A Extends %Persistent
{
Property Name As %String;
Property Collection As list Of ICT.Experiments.B;
Property Collection2 As array Of ICT.Experiments.C;
}

Class ICT.Experiments.B Extends %Persistent
{
Property Name As %String;
}

Class ICT.Experiments.C Extends %Persistent
{
Property Name As %String;
}

I have the classes above and I can select columns from the array collection by using two joins as follows:

0 9
1 1.8K

Hi Community!

Please welcome the new video on InterSystems Developers YouTube Channel:

Optimizing Your SQL Queries

https://www.youtube.com/embed/Gx6-_fLrsjw
[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]

0 0
0 259
Question
· Feb 20, 2020
Sql request

Hi All,

I need to build a search using a lot of filters, what's the best way to implement my sql requests.

1 sql request per property, or only one "big" sql request ?

what's the disadvantage of using lot of indexes ? Thank you

KR,

Amine

0 5
0 253
Question
· May 27, 2020
Read data from Excel

Hello,

I work as a contractor for the Department of Veterans Affairs. I want to read data from an Excel workbook. I searched posts here and saw Apache POI suggested. I believe Apache POI is not approved for use within VA. I also believe we should be able to run SQL query against Excel. Has anybody done such a thing or are there other ways to read data from Excel workbooks?

Thanks

0 11
0 1.2K
Question
· Sep 16, 2020
More than 1 cursor in a class

I have a class that has 2 different cursors for different queries, audit1 and audit2. which are in 2 different methods. The first query runs fine, but the second one generates a 102 error.

Is there an issue with having more than 1 cursor in a class?

Has anyone seen this before?

0 2
0 198

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.

0 1
0 242

Hi,

I'm getting thousands of errors like the ones below:

2021-05-03 14:11:44 [SQLCODE: <-400>:<Ocorreu um erro fatal>] [Cache Error: <<READ>Dispatch+14^%SYS.BINDSRV>] [Location: <ServerLoop>] [Client info: <Username: KIORAS, Node Name: 3IK0VLS5UU, IP Address: 192.168.1.223, Executable Name: EXTRService.exe, Internal Function: B0>] [%protocol: <52>] $Id: //ce/2018.1.2/kernel/common/src/aclass.c#1 $ 23468 104

0 1
0 463

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?

0 3
0 563

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.

0 1
0 174

Hi everyone, i cant understand what is wrong in my query:

ALTER TABLE MyNamespace.MyTable ALTER COLUMN CurrentColumnName RENAME NewColumnName

I just want to rename column name using sql.

I could not find any life example using this syntax.

SQLCODE for this query is -25:

-25 Input encountered after end of query

Quote from documentation:

0 12
0 536

Hi,

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?

Thanks

0 4
0 335

Hi ,

When I use jdbc driver to query the column info ,the "REMARKS" field always show the same as the "COLUMN_NAME" field.

When I use the sql "select * from INFORMATION_SCHEMA.columns a where a.table_name='some table name ' " to query columns info,there has a "DESCRIPTION" field ,the value is some comment for the current field.

So is there anyway to return the description to the jdbc remarks field? And is there anyway to update the desciption or remarks field? Or is there some other way that I don't know to manage column comment info?

0 1
0 302

Ran into an issue with our upgrade from HealthShare Health Connect 2018.1.3 to IRIS HealthShare Health Connect 2022.1, that I thought I would reach out about, I already have a ticket open with WRC and started a chat on Discord. We have a couple of MS SQL tables that I have setup as Linked Tables within Cache to query NPI, Patient Account Information, Visit information, etc... When we upgraded last week we found that within a day or even a half a day that we were having problems with our JDBC connections.

0 19
0 347