#SQL

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? 

From time to time I have gotten questions about what was valued in the incoming data stream, and I thought it would be helpful to add a date so I can sort on by which date that record came in on.

Hey Developers,

New demo show by InterSystems Product Manager @Raj.Singh5479is already on InterSystems Developers YouTube: 

⏯ Develop a Python Flask app with InterSystems IRIS in 10 minutes

Hi,

I'm trying to ultimately create a function that I can use with HL7 messages that calls a stored procedure held on a SQL Server.

Initially I tried extending the function class to use the sql inbound adapter and/or EnsLib.SQL.Common, but this wouldn't work from the class method for the function.

Then I attempted to use the linked stored procedure using the wizard where it connected fine and I could find the sp but but it wouldn't return any data when ran. The test sp should just return an int.

The class document produced is this.

Hi Developers!

How do you handle errors with &sql a.k.a. embedded SQL?
I think I found a universal error-handling template which works in any situation:

&sql(SELECT * FROM Sample.Person)

if SQLCODE < 0 throw ##class(%Exception.SQL).CreateFromSQLCODE(SQLCODE,"Context description")

What is your choice?

Hey Developers,

New demo show by InterSystems Manager @Amir Samary is already on InterSystems Developers YouTube: 

⏯ InterSystems IRIS: Kafka, Schema Normalization and Service Enablement

I am trying to create a Procedure in Caché, but this message is showing:

<UNDEFINED>frmit+118^%qaqpsq *mt("v",1)

This is the procedure:

CREATE PROCEDURE testebi.sp_cargainicial()
BEGIN
    INSERT INTO testebi.Fato_Atendimentos (
    PK_OsProcedimento )
    SELECT ID
     from dado.TblOsProcedimento    ;    

    UPDATE testebi.Fato_Atendimentos as A SET PrimeiraOS = 1
    FROM 
    (select min(convert(int,data)) data, paciente 
    from dado.arqordemservico
    group by paciente) as b
    WHERE a.fk_paciente = b.paciente and  a.fk_Data  = b.

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?

Hi guys!!

In the system that I work, I came across an iterator pattern that uses the %Resultset library without performing the close after executing the query. Does anyone know how to say what are the impacts of not performing such a procedure?

If you have any model of iterator pattern made in caché to recommend as a good example, I will be grateful hehe  :D

Hi everyone

Anyone knows how can we add a column to an existing linked table in Cache to reflect the external SQL table?

I've tried different queries. It does not fail but does not add the column either...

Caché2016

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

Keywords:  IRIS, IntegratedML, Machine Learning, Covid-19, Kaggle 

Purpose

Recently I noticed a Kaggle dataset  for the prediction of whether a Covid-19 patient will be admitted to ICU.  It is a spreadsheet of 1925 encounter records of 231 columns of vital signs and observations, with the last column of "ICU" being 1 for Yes or 0 for No. The task is to predict whether a patient will be admitted to ICU based on known data.

This dataset seems to be a good example of what's called "traditional ML" task. The data seem to have the right quantity and relatively right quality.

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!

Hi,  

In my previous articles, one of the most exciting things about FHIR that I mentioned it's the wide range of possibilities that we have and not only for creating something but the ways to achieve this goal.

I try to compare classic JOIN against implicit JOIN.

It is a simple case. But I am surprised.

The class:
Class Sample.Person1 Extends (%Persistent, %Populate)
{  Property Name As %String;
Property Home As Sample.Address;
Property AR As array Of Sample.Address; }

Query #1 - classic:
select name,home_state,a.* from sample.person1 p
join sample.person1_AR a on p.Id=a.person1
and home_state=AR_state

Query #2 - implicit
select person1->name,person1->home_state,*
from sample.person1_AR where person1->home_state=AR_state

for both I have the same query plan:

It is a simple query on  2 or 1 simple  tables.

Hi,

As part of our continuous efforts to expand and improve the InterSystems IRIS Data Platform, we’ve set up a brief survey around SQL monitoring. Your feedback will help us in designing and developing the right tools for the job and improve the platform’s overall ease-of-use. Please use the link below to access the survey, which should only take around 5 minutes to complete.

Hi:

I have an sql outbound adapter. Sometimes we have trouble connecting to the database. 

The timeout in develpment is set to 15 seconds in live it is 150 seconds as it is an always connected Buisness operation. 

I thought adding E=S or/and X=S would suspend the message. Why does it not? 

Is the only way around then adding something to the buisness operation itself/ creating a customised SQL buisness operation rather than EnsLib.SQL.OutboundAdapter?

Thanks 

Mark

Hi, I want to INSERT a customerID and customerName to a table if the customerID does not exist. Or UPDATE if they've changed their name.
I've reviewed the documentation (https://docs.intersystems.com/irislatest/csp/docbook/Doc.View.cls?KEY=R…) and have confused myself with the INSERT OR UPDATE statement. Rather than using two individual statements , is there a way to get it in one?

What I have at the moment doesn't do anything. In fact it errors.

I intend to use this within a code block in a DTL.

 set customerName = ##class(BAB.Utils.ExternalSystem.TransformFunctions).

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

cache 2017.2 windows 10

Is there any way to change the sql Shell interface to output a <TAB> between the fields instead of outputting one or more spaces between columns

I needed to run an sql ( that took a while to run) and then  cut and paste the generated output into excel, trouble was the paste into excel was not very good for columns when there's spaces used between the columns

kev

I'm trying to get a count of specific message type with a specific entry and thought I could build the query in Message Viewer but  this does not provide counts (as far as I am aware). So when I take the SQL from 'Show Query' it omits the segment criteria as the code shows below. 

 I have attached the criteria that has been excluded. Is this possible?

Thank you

SELECT head.ID AS ID, 
       {fn RIGHT(%EXTERNAL(head.TimeCreated),12)} AS TimeCreated,
       head.SessionId AS SESSION,
       head.Status AS Status,
       COUNT (*) AS messages,
       CASE head.

What is the sql command to describe table in cache db.

Hello, I am running into a little problem.

I am trying to create a Cache Trigger on a FACS table using the SQL CREATE TRIGGER command but I am getting the message [SQLCODE: <-300>:<DDL not allowed on this table definition>].

I am not creating a persistent class but using the SQL CREATE TRIGGER command.

Please advise.

thanks.

Hi,

I get this error [Cache ODBC][State : HYC00][Native Code 469] and zero rows returned with the following query.

SELECT "Timestamp","Skillset"  FROM "dbo"."iSkillsetStat" 
WHERE (Timestamp >= '2020-06-29 00:00:00' 
AND Timestamp < '2020-06-30 00:00:00')

I'm using Qlik Attunity Replicate to extract data, the query was formatted  by the tool. The query will return data in excel and SSIS, but not using Replicate.

So far I've tried using two ODBC drivers

-----------------------------------------------------

    Driver Name:    CacheODBC64.DLL
    Unicode Client Version:    2017.2.2.865.

Let's say we have two serial classes, one as a property of another:

Class test.Serial Extends %SerialObject
{
Property Serial2 As test.Serial2;
}

Class test.Serial2 Extends %SerialObject
{
Property Property As %String;
}

And a persistent class, that has a property of test.Serial type:

Class test.Persistent Extends %Persistent
{

Property Datatype As %String;

Property Serial As test.Serial;

}

So it's a serial, inside a serial, inside a persistent object.

I got a resultset, and some columns might be a JSON_OBJECT. Is there a way (based on metadata) to determine that the column was formed from JSON_OBJECT function?

set rs = ##class(%SQL.Statement).%ExecDirect(,"SELECT 1 colA, JSON_OBJECT('id':1) col2")
do rs.%Display()

I do not want to try parsing json.

Hi,

Could someone help me in identifying all characters in a column is whole number 0 or greater

CASE WHEN '123' %MATCHES '[0-9]*' then 'VALID' else 'INVALID' end

The above statement is finding only for first character.

Thanks

Hi Developers,

Please welcome the new video, specially recorded by @Zhong.Lifor the InterSystems IRIS AI Programming Contest: 

⏯ IntegratedML-Template Demo

Is there any way to execute many SQL commands at once as an (anonymous)block?

for example in PostgreSQL there is 

DO $$

BEGIN

--here is Inserts/Updates/Deletes

END $$

Or in Oracle  

BEGIN

END; 

Thanks for the answers!

Hi Community!

Enjoy watching the new video on InterSystems Developers YouTube and learn more about IntegratedML feature:

⏯ IntegratedML: Predicting Readmissions