#SQL

Hello,
I need help in health insight. I am trying to generate reports on deep see but i am not able to pull in the patient ids as these are our requirements for the project. Can anyone help me in unlocking this feature.
Can anyone help me with link to correct documentation on how to access the edge gateways of multiple facilities to access the clinical data on sql explorer.

Hi guys!

As you know there are two (at least) ways to get the stored value of the property of InterSystems IRIS class if you know the ID of an instance (or a record).

1. Get it by as a property of an instance with "Object access":

ClassMethod GetPropertyForID(stId As %Integer) As %String

{

set obj=..%OpenId(stId)

return obj.StringData

}

2. Get it as a value of a column of the record with "SQL access":

Where can i get the latest odbc providers from SQL Server 2012/2016?

I see some posts on line to an ftp site out there but I am unsuccessful at getting access to it. I have a version loaded on my server but get errors when querying cache'.

"[Cache ODBC][State : 22005][Native Code 22005]"

Any help would be appreciated.

the one we have might be version 2014.01.05851 64 bit (would that make sense?)

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.

I'm close with this but I'm not sure how to grab JUST the GT1.3 data. I know I can do a substring but finding the 3rd | is a tad tricky. I've not been this deep in SQL for 15 years.

SELECT SUBSTRING(hm.RawContent, (CHARINDEX('GT1',hm.RawContent)), 50) as NameDesc
FROM Ens.MessageHeader as em, EnsLib_HL7.Message as hm
where em.Status = 'Suspended'
and em.MessageBodyId = hm.id

I expect it should flow like this...

SELECT UNIQUE SUBSTRING(RawContent, (FIND 3RD PIPE),(FIND 4TH PIPE) as NameDesc ....

Hi, we are trying to run a dynamically generated Oracle Insert SQL script using Do $SYSTEM.SQL.DDLImport("Oracle",""," sql.txt",[Error Log]) to insert multiple rows into a Linked Table within HealthConnect to an Oracle database.

We have tried using statements that are accepted within Oracle but not when using Intersystems DLL along the lines of :

I am trying to migrate our SQL Connections from HealthShare Health Connect 2018.1.3 to IRIS HealthShare Health Connect 2022.2 using the Data Export/Import Wizard through the Management Portal. I am able to export the data fine, but I am running into issues trying to Import it into 2022.2 using the Data Import Wizard. When I run through the steps it is not importing everything from the txt file I created from 2018.1.3. I would rather not have to rekey all the connections if possible.

Here is the Background Task Error Log

We are migrating to new servers, and I need to copy the information from Business Partner's over to the new server. Is this information in a Global or SQL table somewhere that I can export and import into the new IRIS environment?

Thanks

Scott

Hi,

Any recommendations on how i can add a logic if an incoming record that is already existing in the table but different total amount value. I need to subtract the totalamt value on the input file vs the totalamt value on the table and update the table with the difference of totalamt. Any help is much appreciated. Thanks!

I am using a JDBC connection to MS SQL server to execute a stored procedure to select data and bring it into InterSystems as a EnsLib.SQL.Snapshot. I loop through the EnsLib.SQL.Snapshot using a while loop, but I also want to iterate through the Columns within that Row to do logic.

Is there a way to iterate through the Columns of the current Row of the EnsLib.SQL.Snapshot so I can apply logic/rules for further processing?

Thanks

Scott

Hello,

In my project, I am calling a store proc on third party database. I am using SQL Gateway and ODBC. Everything is working fine until the store proc changed. In store proc they have changed the return alias name for a column. In Ensemble Snapshot I am not seeing the modified alias name until I restarted the whole Healthshare instance.

Here is my question, is there any other way? We do not want to take the instance down if it happens after we go live.

Thanks

Raghu

I try it with the COLLATE instruction, but doesn't work.

Any ideas ?

Tkanks.

We've been noticing that we're getting this (https://community.intersystems.com/post/cach%C3%A9-sql-error-odbc) error more frequently and having to constantly purge cached queries in order to resolve it. Is there a way to avoid the issue in the first place?

thanks!

Hi,

I have a class with around 400k lines and 60 columns. Class storage is Cache SQL storage (Mapped from a global).

I want to create multiple indices on certain fields.

I am familiar with two approaches:

1. Create a new map (Index type) on a pointer global.

2. Create a bitmap index

Which approach is more recommended to be used in the case I described? If there are any other approaches, I will be happy to hear.

Thanks :)