SQL

Syndicate content 27 

So just this morning we started to get this error from all of our SQL-client based tools that connect to our Caché SQL via ODBC: 

[%msg: <Error instantiating cached query class in Prepare(): <METHOD DOES NOT EXIST>Prepare+41^%SYS.SQLSRV *%New,%sqlcq>] (State:S1000, Native Code: 190)]

 

No queries work... any help? stumped over here.

Last answer 21 September 2016 Last comment 2 November 2017
0   0 1
620

views

0

rating

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.

Last answer 10 January 2017 Last comment 9 January 2017
0   0 1
391

views

0

rating

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?

Last answer 18 December 2016
0   0 2
0

comments

188

views

0

rating

SELECT MAX(LocalDateTime)
FROM HS_IHE_ATNA_Repository.Aggregation
WHERE LocalDateTime < '2016-12-01'
> 2016-11-30 23:59:59

 That is exactly what I would expect.

SELECT MAX(IndexedDate)
FROM HS_IHE_ATNA_Repository.Aggregation
WHERE IndexedDate < '2016-12-01'
> 2016-12-01 00:00:00

That is NOT expected. 

Last answer 8 December 2016 Last comment 8 December 2016
0   0 1
164

views

0

rating

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.

Last answer 2 December 2016 Last comment 6 December 2016
0   0 4
442

views

0

rating

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

 

Last answer 18 November 2016 Last comment 21 November 2016
0   0 0
157

views

0

rating

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

ERROR #5001: 25: SQL ERROR #25: Input (UPDATE) encountered after end of query^ 
UPDATE Prod . Articulos SET Alto = ? , Ancho = ? , Fondo = ? WHERE CodigoNum = ? AND Empresa = ? 
UPDATE action = (parse) rtn = () $ze = (<SYNTAX>errdone+2^%qaqqt) err = (qaqperr)

neither it works with a structure like thi

Last answer 16 November 2016 Last comment 17 November 2016
0   0 2
319

views

0

rating

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!

Last answer 14 November 2016 Last comment 15 November 2016
0   0 3
270

views

0

rating

The select query here "select Settings from Ens_Config.Item" in the SQL Query of management portal returns this: 

ValidationHost 7ActOnTransformErrorHost1 ReplyCodeActionsHostE=D AlertOnErrorHost1 9 AlertGroupsHost$LabAlertGroup,1-CriticalAlertGroup BusinessPartnerHost

How do I isolate just one of the settings e.g. ReplyCodeActions or LocalInterface

For example, i want to search for all entries that have  a value for ReplyCodeActions

I realize i can open the production in studio and search for those setting values.

 

 

 

Last answer 12 November 2016 Last comment 13 November 2016
0   0 0
296

views

0

rating

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 

 

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

Last answer 19 October 2016 Last comment 20 October 2016
0   0 2
395

views

0

rating

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
214

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 Quer

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

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
274

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

168

views

0

rating

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.

 

Class LastName.BasicClassQuery Extends LastName.Person
{

Query Display() As %SQLQuery (ROWSPEC="ID:%Integer,Name:%String", CONTAINID = 1)
{
SELECT %ID, Name FROM LastName.Person
 ORDER BY DOB
}

Storage Default
{
<Type>%Library.CacheStorage</Type>
}

}
Last answer 1 August 2016 Last comment 1 August 2016
0   0 4
930

views

0

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?

 

Thanks,

Laura

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

views

0

rating

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

Last answer 1 July 2016 Last comment 15 July 2016
0   0 2
544

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

299

views

0

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
229

views

0

rating

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:

Last answer 2 June 2016 Last comment 3 June 2016
0   0 2
662

views

0

rating

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

 

now                  latest               difference  
-------------------  -------------------  ----------  
2016-05-13 11:05:31  2016-05-13 08:51:16  9945
2016-05-13 11:08:16  2016-05-13 08:51:16  9780    
2016-05-13 11:08:36  2016-05-13 08:51:16  9760 

Last answer 13 May 2016 Last comment 13 May 2016
0   0 1
374

views

0

rating

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.  Where would I find that?

Last answer 28 April 2016 Last comment 29 April 2016
0   0 3
217

views

0

rating