I want to query the cache database for messages where a specific HL7 segment equals a specific value. Does Cache have a pipe to XML or hl7 segment query function?
I am trying to built a SSIS package thru BIDS Visual Studio 2013. My Datasource is a InterSystems Cache Database, I wanted to Import Tables records from the Datasource to MS SQL Server 2014.
As a Sanity check. I only created one Package to import one Table to MSSQL Server to try out. The connection to the InterSystems Cache Database was successful. The DSN for the InterSystems Cache Database is created in the System for 32 and 64 Bit.
Class ICT.Experiments.A Extends %Persistent
{
Property Name As %String;
Property Collection As list Of ICT.Experiments.B;
Property Collection2 As array Of ICT.Experiments.C;
}
Class ICT.Experiments.B Extends %Persistent
{
Property Name As %String;
}
Class ICT.Experiments.C Extends %Persistent
{
Property Name As %String;
}
I have the classes above and I can select columns from the array collection by using two joins as follows:
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.
How does one iterate through the results of a SELECT statement?
I have a code block in a BPL. I have the below SQL and code. All variables have been declared and set. When I run the SQL, if there are no values returned, I get 100 for the value of SQLCODE when I perform the FETCH, which is correct. If values are returned, I get a -400 error when I perform the first FETCH. I've investigated, but cannot find the reason for a -400 error. Hoping someone out here understands this and knows what's wrong.
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.
Whenever I try to run a simple query on a production database table, I get a timeout 504 error. I'm using the SQL interface in Ensemble. One of the queries is a simple SELECT TOP 10. Even SELECT COUNT(*) gives a timeout.
I have tried to run the SQL Runtime Statistics, but this gives me a timeout as well.
Does anyone have an idea where I might look to find what is causing this?
This might be more of a math problem than a Caché question. I have a SQL query that joins two tables. I want to assign a unique ID to each row of the product table.
Where can i get the latest odbc providers from SQL Server 2012/2016?
I see some posts on line to an ftp site out there but I am unsuccessful at getting access to it. I have a version loaded on my server but get errors when querying cache'.
"[Cache ODBC][State : 22005][Native Code 22005]"
Any help would be appreciated.
the one we have might be version 2014.01.05851 64 bit (would that make sense?)
the ODBC driver is truncating the string to 50 characters.
If I run this same command with $system.SQL.Shell(), this doesn't occur. I did a test creating several parameters for COSClass_Methode, and they all have a 50 character limitation.
ERROR #5540: SQLCODE: -76 Message: Cardinality mismatch between the SELECT-list and INTO-list SQLTEXT: SELECT * FROM PMG_Data_Private.RemitInstance WHERE (VoucherNumber %INLIST ? SIZE ((10)))
I am trying to fetch the data from cache database. But i got the error like "CSP application closed the connection before sending a responce".
Below is the query.
SELECT CallbackComment FROM SQ.CBPhoneResult_View Where PhoneDateODBC = '2018-04-09'
I have investigated and found that "CallbackComment" contains the special character single quotes " ' " for one result and due to this i got this error.
In this field data is enter by customer. so we cannot restrict them like Do Not use single quotes.
Is there a way to query the database structure? In SSMS there are queries for finding tables with a column with a certain name (using LIKE). And there is the redgate tool SQL Search. But I'm not sure how to go about looking for columns that have say a value of 'PATID' and returning all tables that match. Does anyone know?
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
I'm working on a project with my client. They have a visit table which has about 7,000,000 records. The table is used in a random search page witch holds 20+ conditions to be combined. The table is defined as below:
I'm trying to get a count of specific message type with a specific entry and thought I could build the query in Message Viewer but this does not provide counts (as far as I am aware). So when I take the SQL from 'Show Query' it omits the segment criteria as the code shows below.
I have attached the criteria that has been excluded. Is this possible?
I'm working on exporting data from an Intersystems Cache database through the Cache ODBC Driver. There is a particular table that is giving me an error message. The ODBC Driver crashes and reports an error from the Cache system. I think I was able to trace down where the error is coming from, but I do not know how to debug or fix the error.
The table I am trying to extract is called SEDMIHP.
I have a lookup table and record batch Table I would like to do a count on the records stored in that batch by counting the number of records in that batch that have a certain key on the responseKey column. This column keys are stored in the look up table for comparison. So I would like to do a join sql pivot that will use my keys stored in the lookup as columns and count as values
so far I have managed to do this but this is not efficient I would like to fire that sql once not on every count
I need to select my result into a list and be able to loop through the list when query finished any help appreciated here is where I am
##sql(SELECT %ID INTO :IDArray() FROM MergeHyland.TypeTwoDimesionCollection WHERE GUID = :Key AND EndDate IS NULL)
for I=1;1:$LISTLENGTH(IDArray)
{
w $Data(IDArray),i
}