New post

#SQL

16

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

Problem

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.

Here's the Error:

0 2
0 1.2K

Hello,

When i click on the menu to run the Data import wizard from MP, i receive following CSP error

<UNDEFINED>zOnPageHEAD+229^%cspapp.exp.utilsqleximwizardcontent.1 *schemaname : CSP Error

It is happening for all the namespaces. Looks like some permission issue. Same issue with Data Export wizard. Help to resolve this will be appreciated.

I am using

Cache for Windows (x86-64) 2017.2.2 (Build 865_0_18763U)

Thanks,

0 3
0 268

Hi, we are a veterinary lab and we use both the LAB and FIN systems of Antrim. Now we are looking to expose the data in a SQL/Object compatible way so we were wondering if same / similar things had been done by other community members already? If so, could you please share your approach / experience / gotchas with us and we are all ears. I can be reached at yang.jiao@antechmail.com . Thank you!

0 1
0 252

Hello,

I've got a table with many attributes and data. There is 10 000 000 registrations approximatly.

I need to make a research on this table with filters, paging, and order.

You can see an example of my SQL request :

SELECT * FROM ( SELECT TOP ALL * FROM ANCV_Data.Titre WHERE etatTitre = 'Emis' ORDER BY numRemise desc) v WHERE %vid BETWEEN 1 AND 25

Cause there are many data on my table, my SQL req is too long if I don't have index on my attributes BUT there are many associations possible...

0 1
0 207
Question
· Sep 16, 2020
More than 1 cursor in a class

I have a class that has 2 different cursors for different queries, audit1 and audit2. which are in 2 different methods. The first query runs fine, but the second one generates a 102 error.

Is there an issue with having more than 1 cursor in a class?

Has anyone seen this before?

0 2
0 201

I have an SQL text index defined like this

Index TextIndex On (Text) As %iFind.Index.Basic(IGNOREPUNCTUATION = 1, INDEXOPTION = 0, LANGUAGE = "en", LOWER = 1);

If I run a query like this:

SELECT
Text
FROM Post
WHERE %ID %FIND search_index("TextIndex",'ABC')

I get 20 results with posts containing the term ABC.

But each post can contain the term ABC several times.

0 13
0 368

Hi,

Im wondering if its possible to pass a SQL query through to the Power BI IRIS Connector? I can't currently see an option - and have tried through advance editor to use M Query syntax to attempt this with no luck.

Has anyone else in the community had any luck or know if this is supported?

Thanks.

0 2
0 297
Question
· May 12, 2021
Cache sql query

Hi ,

I am trying to retrieve the contact type column data of a patient based on the recent date as shown below.

for instance

contacttype Datefrom

contact 1. 24/03/2020

contact 2. 20/05/2021

i need to retrieve the second contact2 based on the recent date.

i tried like below but not working .

Case max(datefrom) >0 then contacttype

End as contacttype

Can anyone please tell me why this is not working.Any other options?

Thanks

jude

0 3
0 263

Hi,

Is there way to set up an automated batch job in the Management Portal to execute an SQL query. Also, how can the related view be exported. I have executed the SQL queries and see the view and created files manually. I could not find any related documentation on the batch processes, but I thought batch or automated jobs could be set up via the Management Portal. Any information is appreciated...

Thanks

0 5
0 228
Question
· Mar 18, 2022
EnsLib.HL7.Message Querying

We are trying to track down the source of Orphaned messages and noticed that we are unable to query EnsLib.HL7.Message with any kind of WHERE or ORDER BY clauses in our SQL statement.

I know EnsLib.HL7.Message is a system table, but is there a way we can add additional Indexes to the table to make the query run better/faster without affecting the system?

0 4
0 442

Hey everyone,

I'm currently running into a very weird issue to where I am trying to connect with a 64 bit version of SQL Server Management Studio (SSMS) to a HealthShare instance. I have created a System DSN using the Drivers (image below) that were downloaded with the Client version of the install and I'm able to successfully connect using my credentials.

0 2
0 66
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 665

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 948

How do I write DDL script for collection properties?

For example I want to create the following class:

Class SQLUser.Person {

Property Name As %String;

Property FavoriteColors As list Of %String;

}

My DDL script looks like this:

CREATE TABLE Person (Name varchar(50), FavoriteColors ???)
0 4
0 336
Question
· Nov 19, 2017
Force inner select to be executed

I have a query that I am attempting to run, but inner query is being rolled up into the overall query - per the optimizer (and jobexam observation). While I know that if the inner query runs first, I will eliminate a number of records (making the result set smaller, and return quicker).

Just an example of what I am attempting to do:

0 8
0 413
Question
· Feb 9, 2018
SQL prepare error

I'm trying to learn how To use SQL in CACHE, so I hope I don't bore you with "Dumb" questions....

I'm getting "ERROR #6022: Gateway failed" message following this line of code. 

s sc=gc.Prepare(hstmt,pQuery)

This line of code comes from an example I found in the documentation. pQuery is the "Select" statement setting up the variables and tables I'm trying to pull information from.

What does that error indicate?

Thank you.

0 5
0 1.1K