#SQL

I have a tablePane ZEN Component and I am trying to get a filter running on the Specimen Id / Lab Number. The SQL is fairly complex with 3 UNION ALL statements joining 4 tables and a couple of lookup tables.

How would I get the filter on SpecId to work for my tablePane? It's not automatically applying the filter for me so I think I need code something.

I use the HS_IHE_ATNA_Repository.Aggregation table a lot. Someone just referred me to the HS_IHE_ATNA_Repository.Document table, which has an AggregationId column.

I assume that column references the ID column in the .Aggregation table. If so, does this mean that if the same document was requested 1,000 times that there will be 1,000 entries for it in the .Document table? This seems inefficient to me. Why not have one record in the document table and have a DocumentId column in the Aggregation table?

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!

I want to override getter for a serial property as a whole, because getter code depends on a class in which a property is defined.

Let's say I have CS.Serial as a serial class:

Class CS.Serial Extends %SerialObject
{
Property Year As %Integer;

Property Month As %Integer;
}

And CS.Persistent as a persistent class:

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.

Hello,

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

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?

That is exactly what I would expect.

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:

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.

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'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 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?

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?