Hi:

We added a column to a table as follows 

Property SentTime As Ens.DataType.UTC;

And the code to populate this

if pStatus = "SENT" //set SentTime which is used in the Tableau Report
{
Set doc.SentTime = $$$timeUTC
}

For Historical records of type "Rejected" the Sent time is appearing though as 1840-12-31 00:00:00, we don't know why. Current docs work fine with this column just historical seems to display the default instead of blank/null. 

Hi, I have a timestamp of 201906192359 with a HL7 and I need to add a minute to it to get 201906200000. Is there an easy way within Healthshare to do this?

It seems easy enough within SQL but I cannot get the SQL to work within Healthshare,  this is what I have for SQL which does the job in SQL Server.

 SELECT REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16),DATEADD(mi,1,STUFF(STUFF(@test,11,0,':'),9,0,' ')),120),'-',''),' ',''),':','');

Within Healthshare I've tried to use this SQL to populate a :dateout variable using this code but it isn't updating :dateout.

Hi

I was experiencing an issue yesterday where I had over 900 messages in the queue. I had to filter and delete only A31 messages out of the queue allowing the rest of them to be processed.  I tried to use SQL to set the status of the message to 'Suspended' however the A31 messages still remained on the queue and I had to suspend manually by going through each message.  Is there a way of Suspending or Aborting only certain type of messages in a queue.

Since IRIS 2020.1, changed the way how embedded SQL compiled. Now it compiles upon the first execution of such queries.

Is there any way, I could switch that feature back to the previous behavior, or get some more control over it?

We deliver our software as deployed, and we would like to have it well prepared. Another solution would suit us, if we could change the generated classname of the cached query to something which we would be possible to map. At the moment it generates classes %sqlcq.NAMESPACE, but our customers may use different namespaces.

I want to insert multiple records in the table using below queries, but it gives an error.

Hello,

In the DTL, is there a way to set a value for the HL7 data element in the code section?

For example,
set target.SetValueAt("PID:3(k1).1)") = mrn (mrn is the value returned from the SQL query)

When I ran the test utility, I got this error message.

ERROR ErrException:
zTransform+27^testclass.TEST.1 *SetValueAt,EnsLib.HL7.Message -- logged as '-' number - @' set target.SetValueAt("PID:3(k1).1)") = mrn'

I tried both target.SetValueAt("PID:3(k1).1)") and target.GetValueAt("PID:3(k1).1)"). That didn’t make a difference. The code still error out.

After all these years of doing basic Ensemble work, I am just beginning to venture into using Cache Tables instead of either Data Lookup tables, or what I know of Outside SQL tables using JDBC.  I have several Cache SQL tables that I am building for a project I am working on.  

• I was wondering if it was possible to use a SQL call to look up a value against a Cache SQL table  within a Business Rule? 
• Or is this something that I would have to build a custom function for?

Hey I would like to INSERT mulitple rows to 1 table.  I need to intersystems cache equivelant for 

INSERT INTO table_name (column_list)
VALUES
    (value_list_1),
    (value_list_2),
    (value_list_3);

Have tried this post https://community.intersystems.com/post/how-execute-multiple-query-or-script-sql-management-studio
but still got an error

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

A More Industrial-Looking Global Storage Scheme

In the first article in this series, we looked at the entity–attribute–value (EAV) model in relational databases, and took a look at the pros and cons of storing those entities, attributes and values in tables. We learned that, despite the benefits of this approach in terms of flexibility, there are some real disadvantages, in particular a basic mismatch between the logical structure of the data and its physical storage, which causes various difficulties.

Haven't found this in the documentation. I want:

&sql(SELECT name INTO :var FROM :table)

Is it possible?

The Caché System Management Portal includes a robust web-based SQL query tool, but for some applications it’s more convenient to use a dedicated SQL client installed on a user’s PC.

SQuirreL SQL is a well known open source SQL client built in Java, which uses JDBC to connect to a DBMS. As such, we can configure SQuirreL to connect to Caché using the Caché JDBC driver.

Finding Caché’s JDBC driver JAR

The JAR file containing the Caché JDBC driver is installed automatically by the Caché installer when installing a full Caché instance or when installing client components only.

Hi!

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

I have a class that writes to an external SQL Server database.  When the field is empty, it does not write the column and the column has the value NULL.  In some cases, I want the column to be the value of the empty string instead of NULL.

Class myRequestClass Extends Ens.Request
{

    Property MessageType As %String

}

The message that is passed to this class shows:

    <MessageType></MessageType>

Which writes the field as NULL with:

    tSQL = "insert into myTable (MessageType) values (?)"

    set tSC = ..Adapter.ExecuteUpdate(.tNumberOfRowsUpdated,tSQL,myRequestClass

Introduction

In the first article in this series, we’ll take a look at the entity–attribute–value (EAV) model in relational databases to see how it’s used and what it’s good for. Then we'll compare the EAV model concepts to globals.

Sometimes you have objects with an unknown number of fields, or perhaps hierarchically nested fields, for which, as a rule, you need to search.

Take, for example, an online store with diverse groups of products. Each product group has its own unique set of properties and has common properties as well.

See Part 1 here.

Part 2: Index Handling

Now you have a good idea of what kind of indices you need for your class and how to define them. Next, how do you handle them?

Query Plan

(REMEMBER: Like any modifications to a class, adding indices in a live system has its risks – if users are accessing or updating data while an index is populated, they may encounter empty or incorrect query results, or even corrupt the indices that are being built.

Hi

Sometimes I search mesage with inappropriate sql, It caused query hanged long time. 

So  Is it have way to find these hanged query and kill it?

Tks.

Hi Developers,

New to machine learning? Watch this video on InterSystems Developers YouTube to understand the basic concepts of machine learning and how it provides value in applications around the world today: 

⏯ What is Machine Learning?

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?

When I describe InterSystems IRIS to more technically-minded people, I always start with how it is a multimodel DBMS at its core.

In my opinion that is its main advantage (on the DBMS side). And the data is stored only once. You just choose the access API you want to use.

• You want some sort of summary for your data? Use SQL!
• Do you want to work extensively with one record? Use objects!
• Want to access or set one value and you know the key? Use globals!

Hi,

Do you have any experience in inserting a panda dataframe into SQL IRIS Table? The dataframe.to_sql method uses sqlalchemy and it seems no dialect is available for IRIS.

thanks, Blaise

Hey Community,

my Caché Version: 2013.1 and can't update now.

is it possible to highlight SQL Statements like in embedded SQL with all the features from SQL Statements?

Actually I use SQL Statements with a simple string like:

set myquery = "SELECT TOP 5 Name,DOB AS bdate,FavoriteColors FROM Sample.Person"

But when the queries are more complex it will be very cluttered and unstructured.

It would be nicer if I could set line breaks (I know here could I use indexed variables) and have highlighting like in an embedded SQL:

&sql(
 SELECT TOP 5 Name,DOB AS bdate,FavoriteColors 
 FROM Sample.

Hi Community,

We're pleased to invite you to join the upcoming InterSystems IRIS 2020.1 Tech Talk: Data Science, ML & Analytics on April 21st at 10:00 AM EDT!

In this first installment of InterSystems IRIS 2020.1 Tech Talks, we put the spotlight on data science, machine learning (ML), and analytics. InterSystems IntegratedML^TM brings automated machine learning to SQL developers. We'll show you how this technology supports feature engineering and chooses the most appropriate ML model for your data, all from the comfort of a SQL interface. We'll also talk about what's new in our open analytics offerings. Finally, we'll share some big news about InterSystems Reports, our "pixel-perfect" reporting option. See how you can now generate beautiful reports and export to PDF, Excel, or HTML.

Hey Community,

my Caché Version is 2013.1 and I can't update now.

I want to serialize a SQL Answer row into an Array filled with objects and then convert it to json.

Actually I use the following, which is very error prone when I have to do that often:

set list = ##class(%Library.ListOfDataTypes).%New()

       &sql(
            declare queryONLWK01 cursor for 
            select F0103, F0104
            into :articlenumber,  :amount
            from GL.

Hi,

I issue a sql like follow:

select *, %vid from (select * from order_info) where %vid  between 1 and 10

I got error

 [SQLCODE: <-400>:<Fatal error occurred>]

  [%msg: <Remote JDBC error: ORA-00911: invalid character >]

So ,what's the correct paging syntax for query external data?

Tks.

This is the first in a pair of articles on SQL indices.

Part 1 - Know your indices

What is an index, anyway?

Picture the last time you went to a library. Typically they have books sorted by subject matter (and then author and title), and each shelf has an end-plate with a code describing the subject of its books. If you wanted to collect books of a certain subject, instead of walking across every aisle and reading the inside cover of every book, you could head straight for the bookshelf labelled with your desired subject matter and choose your books.

I am experimenting with Relationships, both Parent to Child and One to Many.

I have done some SQL look-ups and have searched through the documentation, however not in a lot detail, but wonder if there are more and better ways to access both sides of Relationships through SQL?

Thank you in advance for any help provided.

Hi Community!

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

⏯ What is IntegratedML in InterSystems IRIS?

Hey Developers,

We're pleased to invite you to the upcoming webinar "Best Practices of In-Platform AI/ML" by InterSystems on April 3rd at 10:00 (UTC+3, Moscow time)!

When using CallableStatement to call a stored procedure, the log shows an error. SqlCode-400.

This is my calling statement.

CALL web_DHCENS_CDSS.patadviceinfo_patadviceinfo(?,?,?)