#SQL

Hi,

I'm doing a query in SQL and I need to sort my data by some non-repeated field.

Unfortunately, my data is grouped in a way that I cannot guarantee that any column will not have repeated data, so one solution would be to take the row number.

Also, the Cache is not accepting Row_Number () in my querry and I would like to know if there is another solution to return line numbers or some way to add this function to the Cache.

Best regards.

I'm facing a specific sort problem.
There are several thousands of articles sold all over. 
Users expect to get a description in local language sorted by their specific collation.

Hello. I have a query in the database that returns several rows, I need to get an "X" number of results randomly. I tried traditional commands from other languages but was unsuccessful. Does Caché have something like that?

Hi folks!

Do we have an embedded REST API in IRIS which accepts arbitrary SQL and returns data in JSON?

Or if anyone did something like this?

The idea is to have something like MDX2JSON but SQL2JSON.

Thanks!

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?

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).

Hi Community

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

&sql(Select demoVersion into :DemoVersion from demointerface.DemoInstances where ID=:demoID)

this line not even giving error.

Please suggest me how to make sure my sql query should work everytime , when i call the class method

Same code is working fine in production but not working in local:(

Thanks a lot

Prashanth Ponugoti
 

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

I have written below method inbound adapter myAdapter Extends Ens.InboundAdapter [ ProcedureBlock ] returns -400

If i have written in any other Extends %Persistent  cls , always returning -30 which means Table not found

ClassMethod FetchMsgCount() As %Integer
{
    set msgCount=-1
&sql(SELECT count(ID) INTO :msgCount FROM Ens.MessageHeader)
If SQLCODE=0 Quit msgCount
Quit SQLCODE
}

Could you please some one tell me where should write this class method? What I am doing wrong?

Thanks

PRASHANTH

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:

SELECT *
FROM 
   client_address a
   LEFT JOIN client_address b
   on a.client_id  = b.client_id 
     and a.date_updated < b.date_updated 
WHERE
   b.client_id is null

But it goes with comment, that there are no Window functions in Caché SQL.

I wonder, are there any other options to make this query in Caché?

Hello, I need to use IRIS to connect to an MSSQL base.
It has to be done via ODBC, I can't use JDBC at this time by client option.

I am trying to use Microsoft Driver
libmsodbcsql-13.1.so.9.2

But I can't, my attempts result in:
Connection failed.
SQLState: () NativeError: [11001] Message:

I have done all DSN configuration, and my configuration is listed in SQL Gateway Connections. I know it's working, because when I run a test with isql I have the information that connects to the bank.

This is my IRIS and Server configuration:
IRIS for UNIX (Ubuntu Server LTS for x86-64) 2018.1.1 (Build 643U)

Hi!

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

Hi Community,

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.

Atelier Users:

Has anyone found an Eclipse plug-in that provides the capability to connect to a Caché server and give the user a way to write SQL queries using the tables from that server? I'm picturing something like a "WinSQL"-client built as an Eclipse plugin.

I've found and tried the following, but I couldn't get it to connect to my local Caché instance.

http://eclipsesql.sourceforge.net/

I do a lot of SQL query building with the SQL tool on the Management Portal or $system.SQL.Shell() from terminal before moving the queries into my COS code.

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?

I want to override the Get and Set methods of a class property.  The class maps to a pre-existing global. The property is defined like so:

Property Invalid As %Library.Boolean;

with the property mapping to a node like ^GLOBAL(Code,"INVALID")=1
Code is a property in the same class.

The value can be 0 or 1 or the node might not exist. When it doesn't exist I want the value of the SQL field to come out as 0 (false).

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?

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. 

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?

I need to consume the content of ^SPOOL output over SQL.
^%SPOOL utilities don't look so promising

I have this query That I am trying  to use in my class when testing on the terminal I expect to get the results printed on the terminal but I am only getting zero printed please can anyone out there advice on what I am doing wrong

Method PatientInfo(ID As %String) As %Status
{ #dim status as %Status=$$$OK
  SET myquery="SELECT GUID, IDType,IDValue FROM MergeHyland.TypeTwoDimesionCollection WHERE GUID ="_ID
  SET rset=##class(%ResultSet.SQL).%Prepare(myquery,.err,"")
    WHILE rset.%Next() {
    WRITE !,rset.GUID & ":" & rset.IDType& ":" & rset.

During runtime I build an object which is essentially a wrapper over in-memory table:

I want to use this object as a part of INSERT or UPDATE queries, based on a value of some column (the main use case one of the columns is an ID value)

What's the best way to expose the object to SQL?

I use these example classes:
Class Sample.Address Extends (%SerialObject, %Populate)
{Property Street As %String;
Property City As %String;
Property State As %String(POPSPEC = "USState()");
Property Zip As %String;}
Class Sample.Person1 Extends (%Persistent, %Populate)
{  Property Name As %String;
Property Home As Sample.Address;
Property AR As array Of Sample.Address; }

In SQL it is Table Sample.Person1

No problem to define INDEX on Home_State

But Table Sample.Person1_AR

HowTo define an INDEX on AR_State ?
Index st On AR.State;

Does not compile!

I need iterate multiple times the same ResultSet. How can I do a rewind on it?

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)

CREATE PUBLIC SYNONYM suppliers
FOR app.suppliers;

This first CREATE SYNONYM example demonstrates how to create a synonym called suppliers

Hi all,

I have a non objectscript application connecting to a cache instance via ODBC and one column is a list of serial objects. The output from the query contains a lot of special characters and I'm hoping there's a better way to get this data back so I won't have to perform extensive parsing on the application side.

I've tried using the $ListToString() function, but that didn't help much, probably because the list contains complex objects rather than primitives. 

Any help will be greatly appreciated!

Hi!

I was trying to create a query that can be exposed as a stored procedure (function actually) that would return a resultset with a random number of columns. 

Unfortunately, it seems that unless I specify the ROWSPEC annotation on the Query method, I won't get any columns exposed. I was hoping to implement QueryNameGetInfo method and specify the names and number of columns I would be returning dynamically. But it seems that GetInfo information is simply ignored.

Here is my code:

Class Test.

Does anyone know if there is a table or query to get the Application Error Log (^ERRORS) by SQL?

Hello,

I need to create a query that return the amount of heath related information from a patient. 

I created one using the Analitics but, once our Analitcs database is update once a day this information is not reliable during the current query day.

Part of my Analitcs query is shown below. Where can I find the equivalent tables in Health Share? Any help is appreciate.

Tanks In Advance.

--Analitcs Query

SELECT patient FROM  HSAA.AdvanceDirective union all
SELECT patient FROM  HSAA.Allergy union all
SELECT patient FROM  HSAA.Appointment union all
SELECT patient FROM  HSAA.

Hi,

My doubt is about the SQL Query in Caché:

I don't want to take all results from the table and orgainze them manually, for example: I have a table with 50 records, but I only want to select 10 records, being from tenth until the twentieth and this without knowing their IDs.

So, how can I do this, without losing performance, there is any way?

NOTE: In other SQL languages I can do, for example, SELECT * FROM extbl Limit 10 OFFSET 10

Thanks,

Andrei L. Nenevê