Question
· Nov 4, 2020
Avoid $c(0) in globals

Good Morning,

I have this question for a long time. When I make an insert by SQL from an external application in the fields of type %String, if they are empty, it writes the character $c(0) in the global.

Is there a way so that if an Insert is received in a field of type% String with null value instead of the $ c (0) it leaves it empty?:

0 7
0 841

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 896

When i use &sql(SELECT ......) in Cache I can watch the generated code
In the generated .int code and see what is happening.
Just with my normal rights

Now in IRIS I have just 4 line calling some class %sqlcq.***
With enough rights i find there is no such class but the generated .int routine
%sqlcq.IRISAPP.xEZgUjdXCCgQdZQPpRdOye1Ci2ue.1
That holds the code that i had in my .int on Cache

Can i switch this back somehow ?

0 7
0 468

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 464

Is "time" a reserved word in the %CONTAINS function?

When I run the following SQL query, I get 0 matching results

SELECT ID, text
FROM Test.Sentence
WHERE text %CONTAINS('time') OR text %CONTAINS('time ') OR text %CONTAINS(' time')

However, when I run a query where the search string contains words other than "time" alone, it returns the expected matching results

1 7
1 257

This applies to embedded and dynamic SQL queries in Caché ObjectScript. If I attempt to terminate the process via Management Portal, nothing happens. Neither does Ctrl C nor closing the terminal window in which my program is running, nor setting a stop flag in a global which is read by the program on each loop iteration. The only way to stop the query appears to be restarting the Caché server (which is running locally on my PC).

0 6
0 326

I'm using Cache SQL and want the ability to choose a specific index.

I've boiled the problem down to one table and simplified the query down to

SELECT *
FROM Registration.PatResp
WHERE SchedApptNum=8450022

SchedApptNum is indexed, but instead of using that column, "Show Plan" indicates that it's looping through the entire Registration.PatResp table on Id (the primary key for the table).

I've done a tune-table with no change.

0 6
0 868

Hi guys,

My client has a requirement to add a column of random numbers to the query result.

I wrote a function as below:

Class Utils.SqlUtility Extends %RegisteredObject
{

ClassMethod GetSomeNumber(intInput As %Integer) As %Integer [ SqlName = GetNumber, SqlProc ]
{
Return $R(intInput)
}

}

But in the returned sql result, every row share the same value, as below,

SELECT Utils.GetNumber('456'),
ID, Citizenship, DOB, FirstName, Gender, IDNumber, LastName, PatientNumber, PhoneNumber
FROM CDR.Patient

0 6
0 867

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

I running a query and get the results inside a result set. Now I have to iterate through the result set many times. From the doc I've seen only the Next() method. Is there a way to reset the cursor? Otherwise what is a good data structure to save multiple rows of a table?

My code in this case is something like this:

set sql = "SELECT * FROM MY_TABLE WHERE X= '"_Y_"'"
set status = ..Adapter.ExecuteQuery(.rs, sql)
// somehow iterate the rs more than one time

0 6
1 1.3K

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

1 6
0 397

A very simple SQL

SELECT field1 F1, count(field2) CntF2
FROM GCT.PA_Data
where field1 is not null
group by field1

gives an error back:

[SQLCODE: <-461>:<Communication link failure>]

[%msg: <Connection 'TRAKCARELIVE': Execute: SQLState: (HY000) NativeError: [10053] Message: [Cache ODBC][State : HY000][Native Code 10053] [c:\intersystems\ensemble\bin\cache.exe] Software caused connection abort> ]

0 6
0 454

Hello,

searching messages in our Message Bank is quite slow, often runs into timeout.

I wanted to perform a tune table on Ens_Enterprise_MsgBank.MessageHeader because this apparently has not been done yet - the Tune Table utility shows no entries for selectivity, etc.

I tried

 w $SYSTEM.SQL.Stats.Table.GatherTableStats("""Ens_Enterprise_MsgBank"".MessageHeader")

and got this error message

1 6
1 416

Hi,

The system I am working on processes large numbers of records. Inserts, updates and so forth. There are multiple processes that can potentially work on the same table at the same time.

It is an almost impossible task to try and time these processes to not run at the same time, due to volumes, SLAs, etc.

Every now and then there are a few locking issues, Not many considering the volumes. It is just a bit of a nuisance as we need to reprocess these records later.

0 6
0 142
Question
· May 7, 2019
Linked Tables and Dialects

Hi! I've been fiddling with linked tables to get data from other servers, and I encountered a problem that I'm curious about. Maybe I'm not using these tools as intended or there's more going on, so I'm asking here.

I'm running a query on linked table A, something simple like this:

select name from A where id = 5983658923646

And I get this error:

[SQLCODE: <-400>:<Fatal error occurred>]

[%msg: <>]

0 6
0 486