#SQL

Looking to pull out HL7 Fields data to put into columns in a SQL table. How to handle the repeated fields & segments data to SQL.

Operations over the extracted data in SQL server can help for Analysis.

Appreciate your Help!!

Suppose you have an application that allows users to write posts and comment on them. (Wait... that sounds familiar...)

For a given user, you want to be able to list all of the published posts with which that user has interacted - that is, either authored or commented on. How do you make this as fast as possible?

Here's what our %Persistent class definitions might look like as a starting point (storage definitions are important, but omitted for brevity):

Say I have a property in a persistent class that stores list of colours and I would like to query that field and return a list and be able to loop that list to get individual colours how will l go about achieving this I have tried something like this but its not working as expected

 &sql(SELECT colour INTO :colourList FROM favouritecolours)
 While (SQLCODE = 0) 
{
 for i=1:1:$LENGTH($P(colourList,","))
 {
 set fvalue=$P(colourList,",",i) 
write "the first"_fvalue,i, 
} 
}

Within IRIS I defined a task to export audit data every day.

I provide some sample files in my GitHub repo.

I have a ZEN page with nine tablepanes. Each tablepane queries a table in the same SQLServer db. I have a single SQLGateWay(odbc) to this SQLServer db. I need to get better performance when I query all nine table at the same time. Would my performance improve if I had nine SQLGateWays(nine odbc configurations/connections), one for each query? I would appreciate any and all suggestions for getting the very best performance when using SQLGateWays. Thank you.

I'm running SQL reports on some HL7 messages and need to report the raw content of both the outbound message and the ACK response.

In this article I will explain the usage of %SQL_Diag.Result and %SQL_Diag.Message table along with all-new LOAD DATA functionality.

It is recommended to go through LOAD DATA documentation first.

After successful operation LOAD DATA insert one record in %SQL_Diag.Result table and details are inserted in %SQL_Diag.Message table

Below is the basic command when table is already created and source file does not contain header row.

LOAD DATA FROM FILE 'C://TEMP/mydata.txt' 
INTO MyTable

The file name must include a .txt or .csv (comma-separated values) suffix and both source and target have the same sequence of data columns.

Loading from File Source: Header

Hi all,

We are doing a SQL server upgrade to SQL server v.15 from a legacy server which had v.10 . I have our Cache server as a linked server int order to pull data from our system. When building out the ROWSPEC, I have my fields typed as %String with various lengths with one field having a max length of 15000. The SQL server is trying to interpret this as a text data type instead of varchar(n). Text is a depracated data type since SQL server 2008. The error below is produced when trying to query from this view

1. Define Persistent Class
Call utility class to fetch json via query.

Class Test.JSONFromSQL Extends (%Persistent, %Populate)
{

How to delete this redundant task. Cannot press delete when logged in as _system

I am trying to do a INSERT OR UPDATE SQL query, but the query always inserts new rows to table. Is it how I defined the table or am I mising something in the query?

table defines as,

Class AH.AHCOMMON.Tables.HospDepartmentData Extends %Persistent [ Language = objectscript ]
{

I try to create a column with computeonchange.

Its works with CREATE TABLE sql command, but if i use ALTER TABLE ADD COLUMN sql command this computeonchange doesn't works.

any reason why?

example:

CREATE TABLE MyStudents ( Name VARCHAR(16) NOT NULL, surname VARCHAR(16))

alter table MyStudents add column fullname Varchar(50) COMPUTECODE { SET {fullname}={Name}_ "?" _{surname}} COMPUTEONCHANGE (Name,surname)

insert into SQLUser.MyStudents (Name, surname) values ('name1',null)

update MyStudents set name = 'name' where name = 'name1'

Hello,

I am trying to work out if there are any methods available to be able import a result set returned by SQL query into a persistent class.

I have to connect to some legacy SQL databases through SQL Gateway and run some queries. I need to inster the rows returned into a class to then be able to do a %JSONExport to produce a JSON object. I know I can iterate through the resultset and insert one row at a time into the class but was wondering if there is any other/direct way of importing the resultset rows into a class.

Regards,

Utsavi

It is probably somewhere in the doc. So hard to search. Hope InterSystems going to AI on their community/support data.

How to see a meaningful value in management portal - sql explorer. For instance
Class MySetting Extends %Persistent{
Property Name As %String;

Class MyObj Extends %Persistent{
Property Setting As MySetting;