#SQL

The SQL editor in the contest environment promises these features
InterSystems SQL Reference.

Hi all,

I'm trying to use LOAD DATA to insert 11k (11,377) rows of data. LOAD BULK DATA is not available for the version of IRIS I am using.

After calling LOAD DATA it says only 5,500 rows has been inserted. The LOAD DATA docs says any error rows are skipped and a count of skipped rows can be found in %SQL_Diag.Result however there are no results here. There are no errors in the xDBC error log either.

Why have over half the rows been skipped?

Does InterSystems IRIS has a MEDIAN (or percentile) calculation in SQL?

I have a MySQL server with "posts" table.

I also have a Caché server with "downloadedposts" table.

They are connected from Caché to MySQL via SQL Gateway

I want to keep Caché table synced with MySQL one (MySQL "posts" table is a master copy), so periodically Caché queries MySQL server and downloads data. So far so good, and if a record appears or changes in MySQL table, Caché downloads the changes.

The problem I'm encountering is that sometimes rows would be deleted from MySQL "posts" table.

How do I synchronize deletions?

Hi!

Is there a method in a persistent class that can return the current number of records/persistent objects in it?

Would like to know if there is an alternative or better way to paginate through a dataset using dynamic SQL than what I am using below. The problem is that as the potential pool of data gets larger, this code slows down to the point of not being useable. In analyzing each line of code below, it appears the slow down is related to the initial rset.%Next() iteration. Is there anything available which does not require a subquery/%VID such as a simple LIMIT/OFFSET?

My code is similar to :

s sql=##class(%SQL.Statement).%New()

Hi!
There is interesting question in Stackoverflow.
This query works for MS SQL:

SELECT * 
FROM 
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY date_updated DESC) AS rn
    FROM client_address
) a
WHERE a.rn = 1

There is one answer there, which solves the task:

hi,

i have two where-clauses:

a) ... WHERE company=1 and product=7

b) ... WHERE product=7 and company=1

with other words the position of the where fields are swapped.

now the question is:

bring the where clauses a) and b) the same performance(queryplan) or do i have to write it in a special order???

in my point of view, there is a parser/optimizer who cares about this, so i don't have to care about.

Hi Community

I have extended my operation class from Ens.BusinessOperation class . I want to execute below sql on cache.

Why when I use a SQL on the Cache, the condition between "the expression" and "the expression", not permite to me, delete the all ID of this data class?

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"
    }
  }
}

Is it possible see the execution plan of a SQL sentence in IRIS?

Like this:

explain plan for
select  e.ename,r.rname
from    employees  e
join    roles       r on (r.id = e.role_id)
join    departments d on (d.id = e.dept_id)
where   e.staffno <= 10
and     d.dname in ('Department Name 1','Department Name 2');

That returns this:

I'm trying to use the EnsLib.SQL.Operation.GenericOperation component in a production to read a column from a Redshift table that is set up as VARCHAR(65535) and am getting the following error.

An error was received : ERROR #5023: Remote Gateway Error: JDBC Gateway getClob(0,1) errorRemote JDBC error: Cannot convert the column of type VARCHAR to requested type long..