#SQL

I want to query the cache database for messages where a specific HL7 segment equals a specific value. Does Cache have a pipe to XML or hl7 segment query function?

Customer is experimenting with %vid variable for selecting row numbering for sql queries. Basically he's trying to implement paging functionality.

He is confused that select %vid from (select ...) returns dummy number whilst select *,%vid from (select...) returns correct data.

I believe, according to the documentation...

“SELECT * ...”%vid“SELECT *, %vid ...”

... that the syntax select *,%vid... is mandatory, but want to double check. Can anyone confirm, please?

Thank you!

Hi!
There is interesting question in Stackoverflow.
This query works for MS SQL:

SELECT * 
FROM 
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY date_updated DESC) AS rn
    FROM client_address
) a
WHERE a.rn = 1

There is one answer there,  which solves the task:

SELECT *
FROM 
   client_address a
   LEFT JOIN client_address b
   on a.client_id  = b.client_id 
     and a.date_updated < b.date_updated 
WHERE
   b.client_id is null

But it goes with comment, that there are no Window functions in Caché SQL.

I wonder, are there any other options to make this query in Caché?

Hi,

I'm trying to perform a SELECT with parameters using the EnsLib.SQL.OutboundAdapter. The SELECT returns results but seems to discard the parameters I try to send. I have tried two methods.

First:

Set par(1) = "20160630"
Set par(1,"SqlType") = 12
Set sql = "SELECT Cod, Ing, score FROM [bbdd].[dbo].[vw_Test] WHERE MyParam >= '?'"
Set tSC = ..Adapter.ExecuteQueryParmArray(.QueryResultSet,sql,.par)

Second:

Set par(1) = "20160630"
Set par(1,"SqlType") = 12
Set sql = "SELECT Cod, Ing, score FROM [bbdd].[dbo].[vw_Test] WHERE MyParam >= '?'"
Set tSC = .Adapter.

Hi, after installed Healthshare 2015.2, all the tables previously correctly listed working with Healtshare 2014.1 are NOT listed anymore. TrakCare tables are not listed in the catalogue, nor by the WinSQL Intellisense.

Does anybody know a trick or have a hint to resume this useful functionality back?  

Thanks.

Earlier in this series, we've presented four different demo applications for iKnow, illustrating how its unique bottom-up approach allows users to explore the concepts and context of their unstructured data and then leverage these insights to implement real-world use cases. We started small and simple with core exploration through the Knowledge Portal, then organized our records according to content with the Set Analysis Demo, organized our domain knowledge using the Dictionary Builder Demo and finally build complex rules to extract nontrivial patterns from text with the Rules Builder Demo.

This time, we'll dive into a different area of the iKnow feature set: iFind. Where iKnow's core APIs are all about exploration and leveraging those results programmatically in applications and analytics, iFind is focused specifically on search scenarios in a pure SQL context. We'll be presenting a simple search portal implemented in Zen that showcases iFind's main features.

I have a MySQL server with "posts" table.

I also have a Caché server with "downloadedposts" table. 

They are connected from Caché to MySQL via SQL Gateway

I want to keep Caché table synced with MySQL one  (MySQL "posts" table is a master copy), so periodically Caché queries MySQL server and downloads data. So far so good, and if a record appears or changes in MySQL table, Caché downloads the changes.

The problem I'm encountering is that sometimes rows would be deleted from  MySQL "posts" table.

How do I synchronize deletions?

Recently I have been posting some updates to our JSON capabilities and I am very glad that so many of you provided feedback. Today I would like to focus on another facet: Producing JSON with a SQL query.

Hi -

I'm trying to rewind a cursor back to the first row after looping part of the way through the implied result set, but I'm not finding a way to make this happen, is there some such iterator variable or directive that I can leverage to accomplish this?

I could code around it by pulling identifiers and/or values into a local array, and then hand code up an iterator over my local results copy, but this feels like a "redesigning of a wheel" approach, and I thought I would check before I start down this path.

Thanks

Introduction

The field test of Caché 2016.2 has been available for quite some time and I would like to focus on one of the substantial features that is new in this version: the document data model. This model is a natural addition to the multiple ways we support for handling data including Objects, Tables and Multidimensional arrays. It makes the platform more flexible and suitable for even more use cases.

Order is a necessity for everyone, but not everyone understands it in the same way (Fausto Cercignani)

Disclaimer: This article uses Russian language and Cyrillic alphabet as examples, but is relevant for anyone who uses Caché in a non-English locale. Please note that this article refers mostly to NLS collations, which are different than SQL collations. SQL collations (such as SQLUPPER, SQLSTRING, EXACT which means no collation, TRUNCATE, etc.) are actual functions that are explicitly applied to some values, and whose results are sometimes explicitly stored in the global subscripts. When stored in subscripts, these values would naturally follow the NLS collation in effect (“SQL and NLS Collations”).

Hello, is there a way to reset all of the Sample Classes back to their "Installed State."? In other words, delete all the other classes I have put into the samples namespace and leave only those classes that were there when I installed the instance.?

-thank you

Hello,

We are sometimes asked for examples of how to do 3rd party SQL database selects/updates/inserts/stored procedures from an Ensemble SQL Business Operation.

Recently I was asked by a customer with this question. In MS SQL Server, there is a function called host_name() that will return the work station name.

Here is how I would do it in Caché :

With default SQL schema name, in my case SQLUser in SQL (which is equivalent as User in Caché class definition), I have a class called User.dummy, I added a classmethod called hostname and expose it as SQL function host_name:

/// 
Class User.Dummy Extends %Persistent [ ClassType = persistent, DdlAllowed, ProcedureBlock, SqlRowIdPrivate, SqlTableName = dummy, StorageStrategy = "" ]
{
Property f1 As %Library.

I have an sql that is refusing to play fairly.

I have

• tuned affected tables
• made sure I'm on the latest version (2016.1)
• got enough global buffers
• tried forcing the sql to use outliers correctly

Still nothing.

This is a bit weird.  I'm trying to use `DATEDIFF()` to calculate the time since the last message on an interface.  For some reason, when no messages are received, the number steadily decreases.  This is the opposite of what should happen.  Here is my current query and a few sequential result sets:

SELECT getDate() as now, max(TimeCreated) as latest, DATEDIFF (s,getdate(), max(TimeCreated)) as difference
FROM EnsLib_HL7.Message

Hi,

If you want to import data from a mySQL export file (exported with mysqldump), you will find here a little script that could help.

Only the INSERT commands in the sql file are executed into Caché. Indices are not computed for better performance.
%NOINDEX, %NOCHECK and %NOLOCK are generated on each INSERT line.

Currently, the file can not contain a "),(" pattern inside the values part of the INSERT command. If this is the case, the line is skipped. This feature may be implemented in the extractValuesList method.

Feel free to improve it as needed.

On the class reference %Library.GlobalCharacterStream is marked as deprecated in favor of  %Stream.GlobalCharacter.

How I can migrate the data from class properties defined as %Library.GlobalCharacterStream to the new one %Stream.GlobalCharacter? 

Does the %Stream.GlobalCharacter have the same SQL support from %Library.GlobalCharacterStream?

Thanks.

I have two servers in our TEST environment.  One with HSBUS and one with HSREGISTRY.  Let's call them "HSBUS01" and "HSDB01" respectively.

Now, I can access HSBUS with my SQL client by using the JDBC driver pointed to hsbus01.full.name.com and port 1972 and specifying a namespace of "HSBUS".  I am, however, unable to access the HSREGISTRY instance via SQL with a similar combination of information.

I have access to the web interface on both servers (/csp/sys/#CSP.Portal.Home.zen).  What screen can I use to compare the relevant settings?  I think it might be a port issue.

I have to disect thousands upon thousands of strings like this:

96842-2-AEV.00001-63561-001958-100728760I-42

I've been able to pull out the first, second and final segments as separated by hyphens, but now I have a predicament where I need to use the second segment, the 2 shown in the example above, in a join to another table.  Problem is Cache apparently doesn't like doing that.  Just the same, I have to find a way.

Here's the code I'm using to pull that segment out into as separate field in the query results:

substring(prov_record_id, charindex('-', prov_record_id) +1, charindex('-AEV.

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

Does Caché support SQL CREATE SEQUENCE as in PostgreSQL?

If not, what would be the best alternative? Create my own sequence logic as the example bellow? 

 Class Util.MySequence Extends %Persistent [ ClassType = persistent, ProcedureBlock ]
{


Property dummy As %String;

Property myvalue As %Integer [ Calculated, SqlComputeCode = { Set 
{myvalue}=##class(Util.MySequence).CurrentValue()}, SqlComputed ];

ClassMethod CurrentValue() As %Integer
{
        s x=$I(^MySequence)
	Quit x
}

Method myvalueGet() As %Integer [ CodeMode = expression ]
{
..CurrentValue()
}

}

Thanks.

Presenter: Andreas Dieckow
Task: Apply SQL security to multiple servers in a distributed system
Approach: Provide code samples for using new API calls to apply SQL security statements to multiple instances of our products
 

Description: The requirement that started at all. See examples on how to use this new feature and integrate it into your application by discuss code examples.

Problem: SQL Security is local to the instance and most of time driven by customer application code. That it is only local to the instance and is not automatically going to other instances requires a solution.

Solution: With application code use new API calls to issue SQL security statements that is applied to multiple instances.

Content related to this session, including slides, video and additional learning content can be found here.

Presenter: Anton Umnikov
Task: Run SQL queries quickly on huge amounts of data
Approach: Use a shared-nothing architecture to leverage a cluster of small, cost-effective servers
 

Explains how a shared nothing architecture allows you to leverage a cluster of small, costeffective servers to serve query results that would have taken much longer on a single, big and expensive box. This brings affordable horizontal scalability.

Content related to this session, including slides, video and additional learning content can be found here.

Presenter: Kyle Baxter
Task: Upgrade to a new version of Caché without concerns about slowing existing SQL queries
Approach: Use the new Frozen Plans feature in Caché
 

Content related to this session, including slides, video and additional learning content can be found here.

Stream datatypes are not displayed when you execute SQL (in SMP for example).

If I have defined a class query in one of my classes and I want to use that query from a method of another class, what are the pros and cons of using the %SQL.Statement interface versus the %Library.ResultSet interface?

I believe %SQL.Statement is the newer interface.

So if the old way is:

USER>s rs=##class(%Library.ResultSet).%New("%Library.File:FileSet")
 
USER>s sc=rs.Execute("c:\s\","*.txt")
 
USER>w sc
1
USER>while rs.%Next() {w !,rs.Data("Name")}

...

then the new way is:

USER>s oStmt=##class(%SQL.Statement).%New()
 
USER>s sc=oStmt.%PrepareClassQuery("%Library.File","FileSet")
 
USER>w sc
1
USER>s oResult=oStmt.%Execute("c:\s\","*.txt")
 
USER>while oResult.%Next() {w !,oResult.%Get("Name")}

...

Hello! 

I'm trying to run a simple SQL query in atelier but it complains about the manager.  I'm not sure if this is the best way to do it but here is what I got so far:

	ZNSPACE "User"
	
	SET domId = 1
	SET flister=##class(%iKnow.Source.SQL.Lister).%New(domId)
	SET myloader=##class(%iKnow.Source.Loader).%New(domId)
 
 
	SET myquery="select top(10) EntityValue, CorpusFrequencyAsRelation, CorpusSpreadAsRelation from %iKnow_Objects.EntityUnique  WHERE DomainId = 1 ORDER BY  CorpusFrequencyAsRelation desc"
	SET idfld="EntityValue"
	SET grpfld="CorpusSpreadAsRelation"
	SET dataflds=$LB("CorpusSpreadAsRelation")

	SET stat=flister.AddListToBatch(myquery, idfld, grpfld, dataflds)	
		IF stat '= 1 {WRITE "The lister failed: ",$System.Status.DisplayError(stat) QUIT }
	SET stat=myloader.ProcessBatch()
		IF stat '= 1 {WRITE "The loader failed: ",$System.Status.DisplayError(stat) QUIT }

What is the recommended approach to handle the following scenario: Large number of HL7 messages are serviced via Ensemble. Those messages are then transformed and routed to an Operation which calls a web service which does a number of different activities. If will do address normalization, location geo coding, MPI lookup and resolution, and insert into a SQL Server database to stage for an analytics product. Unfortunately all of this work takes time and the first thing that happens in the web service is a lock taken out on the Patien in SQL Server.

In MSSQL I think you can do something like this:

select *
from HS_IHE_ATNA_Repository.COLUMNS
where TABLE_NAME='Aggregation'

How can I do this in Cache SQL?

[%msg: < Table 'HS_IHE_ATNA_REPOSITORY.COLUMNS' not found>]