When working with a large query executed though an ODBC connection what is the best way to allow the paging of the results at the client side.  I have tried some methods using %VID and similar methods, but these really don't seem to work as the value returned is related to the ID of the data and not the position in the results set.  What would be ideal is if the value seen in the management portal when you check of "Row Number" was available to external queries through ODBC.  I have not seen a way to return this however.   This would be similar to SQL server Last n and Skip n capabilities.

Hi guys.

Do You known If Caché has suport to Common Table Expressions (With queries)

By example: https://www.postgresql.org/docs/9.6/static/queries-with.html

Hello,

I read the Cache Documentation where it describes Private Property as below:

If you have a lot of SQL queries to external systems (or even internal dynamic ones), you can encounter something like this:

So, I played with row level security and was unable to find a way to get to work it dynamically. I want to determine if the user can access the row in runtime based on a state of external system, but it seems that row level access is calculated during INSERT/UPDATE and stored in %RLI index. Is there a way to achieve runtime access calculation?

Hello,

Is there some way to reproduce the SQL GROUP_CONCAT(http://sql.sh/fonctions/group_concat) with the Caché DB ?

 That is exactly what I would expect.

That is NOT expected. 

We don't often use SQL within our org, which is mostly due to the performance issue we experience due to the quantity of data we are reviewing.

Aside from the standard performance measures for non-Caché databases, are there any recommended approaches when querying large tables?

The table would have roughly 50M records, but there are not a finite amount of sub-nodes.

Hello Fellow Caché Developers,

The purpose of this post is to ask for everyone's thoughts and input around the use of transient, SQLCalculated/Computed properties within persistent classes.

This approach allows for extra data values needed in SQL queries to be available without having to join to other tables.

Very simple/basic example to illustrate the topic:

Let's say I have a persistent class named ICDAutoCodeDefn to hold ICD Auto-Coding definitions, such as:

Class ApplicationName.DB.MedicalCoding.ICDAutoCodeDefn Extends %Persistent

That has a few properties:

SpecimenPart As %String

 Error:

    [SQLCODE: <-400>:<Fatal error occurred>]
    [Cache Error: <<SUBSCRIPT>%0AmEdun+4^%sqlcq.HSREGISTRY.cls966.1 ^||%sql.temp(1,"")>]
    [Location: <ServerLoop - Query Fetch>]
    [%msg: <Unexpected error occurred:  <SUBSCRIPT>%0AmEdun+4^%sqlcq.HSREGISTRY.cls966.1 ^||%sql.temp(1,"")>]

Here is an example of a query that gives the error:

    SELECT COUNT(DISTINCT Criteria) as Relevance FROM HS_IHE_ATNA_Repository.Aggregation
    WHERE EventType = 'CROSS GATEWAY QUERY'

The content of the actual field is XML, but the query simply returns a number.  Here is one that works:

I have a class which defines a property as array of %String. Is it possible to index values of this property and use this property in SQL?

I have tried 'Index idx On prop(ELEMENTS)' and then a select from the generated collection table, but this is still orders of magnitude slower than queries to the containing class.

Is there a tool to get the slowests queries ?

In writing some code on:

Cache for Windows (x86-64) 2015.1 (Build 429U) Fri Feb 13 2015 14:37:23 EST

I noticed an unexpected "ROLLFAIL" error when a Unique Index fail was generated in a %Save() call.

The object SHOULD fail to save, since the Unique criteria is not met, but I didn't expect to see a ROLLFAIL error as the "Last Error".

When running the following class code:

I would like to integrate SQL into our ObjectScript routines in order to illustrate how SQL can be used to minimize development time. However, there are two questions I have.

1. How does one iterate through the results of a SELECT statement?
2. How does one access a Table in another namespace?

I'm trying to use the standard TSQL DECLARE syntax but I get the following error:

[%msg: < IDENTIFIER expected, @ found^  DECLARE @>]

How do I declare a variable?  For instance:

i have this error

DrawTableError : <MAXSTRING> zDrawTable+349^%CSP.Util.TablePane.2

perhaps i have the possibility of Deleting all history queries, but i think better to check the table where last queries were stored and solve it by deleting wrong registries. anybody knows where is the table 'QueryHistory'.

I get this on some queries in some namespaces.  For instance, this query:

It works fine in HSBUS but in HSREG it throws the error.

Server closed communication device

Does anyone know what would cause this? Would it log something more useful somewhere?

Since now i have been working from external connection but i want to work with the SQL utility of Management Portal

i dont know how to do with several instructions like in other editors like this example

update Prod.Articulos set Alto = 1646, Ancho = 16, Fondo =  80 where CodigoNum = '100' and Empresa = 'CO'
update Prod.Articulos set Alto = 1646, Ancho = 16, Fondo =  400 where CodigoNum = '101' and Empresa = 'CO'
update Prod.Articulos set Alto = 1646, Ancho = 16, Fondo =  400 where CodigoNum = '102' and Empresa = 'CO'

it returns

Running TuneTable accounts among other the parameter named Block Count. In documentation, we see that this is an approximate numbers of 2K-blocks in which SQL-maps are stored. Databases in recent Cache doesn't support 2K-physical blocks so SQL-blocks are not physical blocks as it seems. So two questions:

- what are these blocks?

- how knowledge about count of blocks can help in SQL optimization?

Thanks for intelligent answer to stupid questions!

Beginning in Caché 2013.1, InterSystems introduced Outlier Selectivity to improve query plan selection involving fields with one atypical value.

In this article, I hope to use an example 'Projects' table to demonstrate what Outlier Selectivity is, how it helps SQL performance and a few considerations for writing queries.

Selectivity

I've asked a lot of questions leading up to this, so I wanted to share some of my progress.

The blue line represents the number of messages processed.  The background color represents the average response time.  You can see ticks for each hour (and bigger ticks for each day).   Hovering over any point in the graph will show you the numbers for that period in time.

This is super useful for "at a glance" performance monitoring as well as establishing patterns in our utilization.

Here is the query used:

This post is the direct result of working with an InterSystems customer who came to me with the following problem:

SELECT COUNT(*) FROM MyCustomTable

Takes 0.005 seconds, total 2300 rows.  However:

SELECT * FROM MyCustomTable

Took minutes.  The reason for this is subtle and interesting enough for me to write a post about.  This post is lengthy, but if you scroll to the bottom I'll write a quick summary, so if you've gotten this far and think you've already read enough, scroll to the end to get the main point.  Check for the sentence in bold.

Hi All,

            I have a general query in regards to developers experience on extracting data from cache databases and the most efficient way to do so. I work with a number of clients who have applications with cache databases and require the data off the host system and onto data warehouse platforms for research and analysis. Often they require the data in source state which means the extracts are often simply a table scan of the entire database table without any aggregation or manipulation.

This tells me that there is no timezone offset on this table/field:

So why is TimeCreated in this resultset 4 hours less than OneMinuteAgo when I'm clearly requesting only records with a TimeCreated greater than OneMinuteAgo?

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.

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

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.

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…

[2] https://community.intersystems.com/product-documentation/intersystems-p…

I have a class with a string property with DISPLAYLIST and VALUELIST parameters specified.

When I query this class via ODBC I receive logical value as it is and not a display value.

Is there any way (without stored procedure) to get display value?

I need to do an UPDATE via SQL and I would like the statement to return the `ID` column of each row that is updated.  MS SQL has an "OUTPUT" statement, but I don't see anything similar in Cache.  Is there a way to do this?