New post

#SQL

15

SQL is a standard language for storing, manipulating and retrieving data in relational databases.

I have the following query which tells me how many documents were retrieved for each customer, but it only works for the "on-demand" customers:

SELECT PatientFacility, LEFT(LocalDateTime,7) as Mnth, Count(*)
FROM HS_IHE_ATNA_Repository.Aggregation
WHERE EventType IN ('RecordRequest','RecordRequestBreakGlass')
AND LocalDateTime >= '2016-01-01'
AND LocalDateTime < '2017-01-01'
GROUP BY PatientFacility, LEFT(LocalDateTime,7)
0 4
0 374

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.

0 5
0 873

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.

0 7
0 918

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:

0 4
0 737
Question
· Feb 12, 2016
Help with SQL error

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'

0 10
0 4.4K

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:

0 4
0 952

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

0 3
0 296

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

SELECT TOP 10 SessionId, datediff(s,min(TimeCreated),max(TimeCreated)) as ResponseTime
FROM ens.messageheader
GROUP BY SessionId
ORDER BY ResponseTime DESC

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?

0 7
0 893

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

0 11
0 664

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?

0 4
0 473

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

Select TOP 1 GETDATE() as Now, TimeCreated FROM ens.messageheader ORDER BY TimeCreated DESC;
Now TimeCreated
------------------- -------------------
2016-10-18 16:16:49 2016-10-18 16:16:31
0 13
0 797

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.

0 7
0 480

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.

0 4
0 424

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?

0 4
0 708
Question
· Aug 18, 2016
Read only Async mirror setup

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?

0 7
0 668

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

0 6
0 4.1K

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:

0 2
0 565

I have a NewBie Question.

I have been playing around with "Basic CLass Queries."

I have defined a very simple "Basic Class Query." (see below)

However, I cannot find in the I/S documentation how to execute this query.

Any help is appreciated.

0 8
0 2.3K