#SQL

Introduction

Say you have a receiving system that accepts HL7 and provides error messages in field ERR:3.9 in the ACK it returns. You require a different reply code action depending on the error message, however the Reply Code Actions settings for the operation do not provide this level of granularity. One option could be to create a process that takes the ACK and then completes the action you were expecting, however things can get a bit messy if the action is to retry the message, especially when trying to view a message trace.

Hi Community,

I am trying to save data into SQL table but each entry getting saved twice in the SQL table. Is there any reason data is saving Twice in the SQL table

I have created a %Persistent class for the fields

Please find the business operation below

Class Patient.DBOperation Extends Ens.BusinessOperation

{

Parameter ADAPTER = "EnsLib.SQL.OutboundAdapter";

Property Adapter As EnsLib.SQL.OutboundAdapter;

Parameter INVOCATION = "Queue";

One of the reasons why I love Cache and Iris is that not only you can do anything you can imagine, also you can do it in a lot of different ways!!.

Imagine that you have an integration running with IRIS connected by ODBC you probably only run SQL queries but you can also create stored procedures and inside write the code to do everything you can imagine.

I'm going to give you some examples but the limit is your imagination!!

I need a stored procedure where I can execute multiple delete from statements.

This will work to create a stored procedure, but when I add in other delete from, get error invalid sql statements when try to create

CREATE PROCEDURE DeleteSpecimenTwo
(IN Specno VARCHAR(50))
BEGIN
DELETE FROM PCONT WHERE PSPECNO = :Specno;
END;

This doesn't work

CREATE PROCEDURE DeleteSpecimenTwo
(IN Specno VARCHAR(50))
BEGIN
DELETE FROM PCONT WHERE PSPECNO = :Specno;

DELETE FROM PSPEC WHERE PSPECNO = :Specno;
END;

Introduction

This is a simple tutorial on the quickest way I found to create a sample database for any purposes such as testing, making samples for tutorials, etc.

Creating a namespace

1. Open the terminal
2. Write the command "D $SYSTEM.SQL.Shell()"
3. Write "CREATE DATABASE " and the name you want for your namespace.

Now you have a new namespace in a faster way than creating it from the Management Portal - which of course offers way more configuration options.

Newbie here...

I am looking to use fhir to gather patients in our end point that have a certain diagnosis and or certain insurance.

Is this what fhir can be used for?

In the past I have done the normal SQL query/extract to CSV file. Looking to use fhir for this for future.

Thanks in advance for your time

Dean-O

Is it possible to retrieve the SQL-Executing process ID (PID) from a process spawned from a UNION %PARALLEL? I have a value set in the SQL-Executing process that I need to replicate in the UNION %PARALLEL spawned process(es). The SQL-Executing process writes the value to a global subscripted by its PID so determining the SQL-Executing PID from the spawned process is what I need. I expected $ZPARENT from the spawned process would hold the SQL-Executing PID but the PID returned is different.

With thanks,

Michael Hill

Hi,

Can anyone please help me how to use regular expression/wild character on a sql query for a pattern like C00.0 to C00.9, C01.0 to C01.9 and so on until C77.0 to C77.9 .

Thanks for your help

Thanks

Jude

The last days I've work with the great new feature: LOAD DATA With this post I would like to share my first experiences with you. The following points do not contain any order or other evaluation. These are only things that I noticed when using the LOAD DATA command. It should also be noted that these points are based on the IRIS Version 2021.2.0.617 which is a preview release. So it may be that my observations do not apply to newer IRIS versions.

Hi All,

I have a below query for which I need help.

I have a persistent class User.Cars.cls. The properties of this class is mapped through Global mapping with global (^CAR(Date,CarSerialno,Seq)).

Property CarNumber as %String (TRUNCATE=1) [SqlFieldName= CAR_Number];

Mapping of 5th Piece of ^CAR global to CarNumber property.

For e.g.^CAR(Date,CarSerialno,Seq)=1^2^3^4^BMW 4567^6^7

Hi All,

I am currently using D $SYSTEM.SQL.Shell() command or management portal to retrieve the data from database. I just learned we can also run cache table sql queries in MS access. MS access already installed on my machine.

I need to ask how I can connect my database of USER or sample namespace in MS access to run these SQL queries.

Thanks in advance!!

Hi All,

I am a beginner in cache. I need to extract data from multiple tables (4-5) tables based on an SQL query . The data is more than 9 lakhs of records (i checked using count). The total number columns is 16.

When I am running this SQL query through management portal, I am getting time out exception. Could you please suggest how I can retrieve those records. Which way will be good (Dynamic query using %SQL.Statement or Embedded SQL (not known to me) or Writing to a global will help or not).

Hi community,

I am working on Converting Non HL7 message (Using record maps) into HL7 message.

1.Can anyone share few details how to save Non HL7 message into SQL table and the converted HL7 message into SQL table

2.In Message Viewer is there any SQL tables are linked to the session id or where the information regarding the message will be stored? Will the message trace details are stored in globals or in SQL table,If yes can anyone share the details in which tables or globals will it be stored?

Hi everyone.

I have a use case where I'm using embedded SQL within a Business Process to interact with a SQL table. However, when it comes to deployment into our production, the table wont form part of the deployment package created from the production.

Beyond manually creating the table on the production system, is there a standard way of ensuring that a table needed for a class is created during deployment?