SQL

Syndicate content 29 

I have a list of about 100 MPI IDs that I would like to run a report on.  I want to list times that any data for these patients were accessed.  Currently in "Managed Reports" we have a "Disclosure Report" which I think was a custom development effort, but it is per-patient.

I have a SQL query for the ATNA log but I'm not confident in its accuracy, so I thought I'd reach out and see how other Information Exchange's might get this data.

Last answer 11 October 2016 Last comment 12 October 2016
0   0 1
222

views

0

rating

Hello,

I need to create a query that return the amount of heath related information from a patient. 

I created one using the Analitics but, once our Analitcs database is update once a day this information is not reliable during the current query day.

Part of my Analitcs query is shown below. Where can I find the equivalent tables in Health Share? Any help is appreciate.

Tanks In Advance.

 

--Analitcs Query

Last answer 27 September 2016 Last comment 10 October 2016
+ 1   0 1
240

views

+ 1

rating

I experience this constantly with Cache SQL.  Especially when querying the ATNA log.

 

SELECT TOP 400000 * FROM HS_IHE_ATNA_Repository.Aggregation ORDER BY ID DESC

 

That took 12 seconds.  I then upped the number to 500,000 and it took 185 seconds.

 

Shouldn't the execution time scale proportionately?

 

If I run the 500,00 query again it takes 2.4 seconds.

Last answer 6 October 2016 Last comment 7 October 2016
0   0 1
283

views

0

rating

I have to connect to an external database and I'd like to use the JDBC SQL Gateway.

I followed this tutorial [1] to create a JDBC SQL Gateway and after configured it works as expected, but I don't know

how use this connection into source code. Maybe these other tutorial is related [2] and [3].

 

[1] http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

Last answer 5 October 2016 Last comment 7 October 2016
+ 1   0 0
382

views

+ 1

rating

I have setup an async reporting mirror member with Read only access.  My problem is that if I try to do any sql reporting against that data I am getting errors.  I am sure that this is because the DB is read only, but I had assumed that setting up a reporting mirror would handle this.

 

I there a setting or mapping  I am missing?

Last answer 22 September 2016 Last comment 23 August 2016
0   0 2
337

views

0

rating

I have been using the query below and it was working fine but now it's giving a SQL error. There were no changes made that could cause this to stop working. There is no Field 'APPLICATIONID' in the table.

QueueSQL=select distinct (convert(char(5),SkillsetID)+'='+Skillset) from iagentbySkillsetStat where ApplicationID > 10000
QueueMappingSQL=SELECT DISTINCT (convert(char(5),SkillsetID)+'='+Skillset), SkillsetID FROM iagentbySkillsetStat iagentbySkillsetStat WHERE (iagentbySkillsetStat.ApplicationID>10000)

DB- Intersystems Cache

Last answer 16 September 2016 Last comment 16 September 2016
0   0 1
2371

views

0

rating

Let's say we have two serial classes, one as a property of another:

Class test.Serial Extends %SerialObject
{
Property Serial2 As test.Serial2;
}

Class test.Serial2 Extends %SerialObject
{
Property Property As %String;
}

And a persistent class, that has a property of test.Serial type:

Class test.Persistent Extends %Persistent
{

Property Datatype As %String;

Property Serial As test.Serial;

}

So it's a serial, inside a serial, inside a persistent object.

+ 4   0 1
0

comments

182

views

+ 4

rating

Hello Fellow Cache Developers:  

Has anyone ever created an index on values of a list property?   If so, would you be willing to share an example?

Also, feel free to offer input and suggestions regarding use of indexes on List values.

Here is my database scenario:

Parent Class:

PropertyA - %String  

PropertyB - %Integer 

Child Class:

PropertyC - %Integer

PropertyD - list of %Integer

Data illustration:

Last answer 31 August 2016 Last comment 31 August 2016
0   0 1
316

views

0

rating

Given a complex method flagged with [ SqlProc ] so it is available as an SQL stored procedure, what's the best way to report a non-system error detected in that method - say, for example, an error %Status - so that the SQL query calling it fails descriptively? Is it best to create and throw an exception, or are there special % variables involved (like in a trigger)? I haven't been able to find an answer in the documentation.

Thanks in advance!

Last answer 17 August 2016
0   0 2
0

comments

175

views

0

rating

We are creating a package (written in Caché Object Script) that will provide access to an external DB (MySQL). Because applications that use our package will be run from machines with various, potentially unexpected, operating systems, we’d like to establish a connection to the external DB without using DSNs (we’ve heard that setting up DSNs on certain non-Windows machines can be cumbersome and problematic).

Last answer 1 August 2016 Last comment 26 July 2016
0   0 4
618

views

0

rating

I want to override the Get and Set methods of a class property.  The class maps to a pre-existing global. The property is defined like so:

Property Invalid As %Library.Boolean;

with the property mapping to a node like ^GLOBAL(Code,"INVALID")=1
Code is a property in the same class.

The value can be 0 or 1 or the node might not exist. When it doesn't exist I want the value of the SQL field to come out as 0 (false).

Last answer 22 July 2016 Last comment 28 July 2016
+ 2   0 2
773

views

+ 2

rating

i'm looking for a way to display a list of tables in an application.  I need a Table or Schema API, and I can't find one.  I found this:

 

http://docs.intersystems.com/latest/csp/docbook/%25CSP.Documatic.cls?PAG...

 

with a google search, but I'm not sure if I can even use this (it says that SQL name is "TABLES"), or how to use it.

 

Is there a way to get at the table names available in a namespace?

Last answer 26 July 2016 Last comment 27 July 2016
0   0 1
547

views

0

rating

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.

Last comment 13 July 2016
+ 7   0 9
2801

views

+ 7

rating

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:

Last answer 4 July 2016 Last comment 5 July 2016
0   0 2
585

views

0

rating

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.

Last comment 30 June 2016
0   0 4
0

answers

312

views

0

rating

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 Demoorganized 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.

Last comment 28 June 2016
+ 7   0 6
566

views

+ 7

rating

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?

Last answer 27 June 2016 Last comment 24 June 2016
+ 2   0 1
616

views

+ 2

rating

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

Last answer 21 June 2016 Last comment 22 June 2016
0   0 1
238

views

0

rating

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.

Last comment 15 June 2016
+ 13   0 11
1841

views

+ 13

rating

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”).

Last comment 10 June 2016
+ 9   0 0
1609

views

+ 9

rating