SQL

Syndicate content 29 

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?

Thank you

Last answer 7 March 2018
0   0 2
0

comments

462

views

0

rating

It is often necessary to sort the results of a query on a string field containing a combination of alphabetic and numeric characters. In cases like this the default string collation may not always return the data in the expected sequence.

 

An example of this may be where a select from Samples.Person should order the results by the street address, but firstly ordered by the street number part as numeric, and then by the street name.

 

The default query will return the results as follows:

 

Last comment 6 March 2018
+ 4   0 2
307

views

+ 4

rating

Hi, Community!  

Please find a new session recording from Global Summit 2017:

Optimizing SQL Queries

 

+ 1   0 3
0

comments

152

views

+ 1

rating

No doubt bitmap indexing, if used with a suitable property, performs just impressive!
But there is a major limit: ID key has to be a positive integer.
For modern class definitions working with CacheStorage this is a default.

BUT: There are hundreds (thousands ?) old applications out in the field that
are still using composite ID keys.
Or - to name the origin - work on Globals with 2 subscript levels (or more).
They are by construction excluded from our "Bitmap Wonderland".

Last comment 17 February 2018
+ 7   1 0
402

views

+ 7

rating

I noticed that the subscript of the index map is actually the collated field (%SQLUPPER).

Is String the only data type going through this transformation? Any other data type would also go through this transformation?

Thanks. 

Last answer 15 February 2018
0   0 2
0

comments

113

views

0

rating

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. 

Last answer 14 February 2018 Last comment 12 February 2018
0   0 5
400

views

0

rating

Hi, I am trying to execute a store procedure within an ensemble operation to connect to aMsSQL database and I am having problem to pass the input arguments... I have tried several things but only one seems to work and it is not the ideal solution.

Last answer 9 February 2018 Last comment 9 February 2018
0   0 1
518

views

0

rating

Last week, we announced the InterSystems IRIS Data Platform, our new and comprehensive platform for all your data endeavours, whether transactional, analytics or both. We've included many of the features our customers know and loved from Caché and Ensemble, but in this article we'll shed a little more light on one of the new capabilities of the platform: SQL Sharding, a powerful new feature in our scalability story.

Last comment 31 January 2018
+ 12   0 8
932

views

+ 12

rating

As I was going though and trying to figure out why our CACHE.dat has increased in size over the past 18 days, I found that EnsLib_HL7.Message is still retaining messages dating back to 2014 even though we have our purge set to 10 days. Has anyone else experienced this?

Thanks

Scott Roth

The Ohio State University Wexner Medical Center

Last answer 30 January 2018
0   0 2
0

comments

138

views

0

rating

Hi,

My doubt is about the SQL Query in Caché:

I don't want to take all results from the table and orgainze them manually, for example: I have a table with 50 records, but I only want to select 10 records, being from tenth until the twentieth and this without knowing their IDs.

So, how can I do this, without losing performance, there is any way?

 

Last answer 24 February 2017 Last comment 25 January 2018
+ 1   0 2
1186

views

+ 1

rating

I'm trying to create an sal connections from one cache system to another. I've created the connection part  and I think that is working. When trying to link a table through the link table wizard, I'm getting an error "<MAXSTRING>zCreateOneLinkTable+52^%CSP.UI.SQL.LinkWizardResultPage1.

Is this due to my global block size in the new system not being large enough? It is currently set at 8192.

Thank you

Mike

Last answer 16 January 2018 Last comment 16 January 2018
0   0 2
216

views

0

rating

Hi -

I'm creating a custom report in Health Insight via SQL and one of the requirements says that I must include patients who are >= 13 years old (which is easy) but I also need to include patients who WILL be 13 years old as of December 31st of current year that the report is ran in.

 

I see that there is an Age column and an AgeInMonths column in HSAA.Patient. and of course a DOB column. I'm wondering if it is possible to do this purely using SQL (and SQL functions) or if I will have to incorporate a COS function to achieve this requirement.

 

Last answer 16 January 2018 Last comment 28 December 2017
0   0 2
808

views

0

rating

I have a database which needs to be synced to another system and considering to use the SQL Inbound adapter to 'watch' the database. The other system, however, has a bulk API so I'd prefer to send multiple SQL rows in a single message to that system. The SQL Inbound adapter seems to trigger a single call (to a process or business operation) per SQL-row.

Last answer 8 January 2018
0   0 1
0

comments

213

views

0

rating

Recently viewed a demo on the new Dynamic Objects in InterSystems (we are still running 2016.1); together with the upcoming IRIS data platform I started thinking about possibilities of building dynamic databases. That is, I can imagine some projects were it might be really nice to store dynamic objects and then run sql queries on them without ever defining the fields of the dynamic objects (i.e. not at storage, but only if you run your sql query).

Last answer 13 December 2017
0   0 1
0

comments

315

views

0

rating

Hi, I'm a student, weak programming. I ask for your help, I write a program in C #, there are two tables, but I do not understand how to organize the connection between them many to many. Table in DataGridView1 from DataSet1 on Form1 key connection in DataGridView1 from DataSet1 to Form3

I do this on Form1

Last comment 12 December 2017
0   0 1
0

answers

246

views

0

rating

Hello,

I'm working on integrating a Cache database into an existing .NET project of mine.  I'm trying to execute certain queries utilizing the Cache ODBC driver.  The first error I ran into was "[S1000] [Cache ODBC][State : S1000][Native Code 417] Access Denied".  The part that's throwing me for a loop though is that the application ran ~200 successful queries before I started getting that.  After some of those Access Denied errors, they turned into "[Cache ODBC][State : S1T00][Native Code 450] Request timed out due to user timeout".  This was all with the regular (2.5?) driver.

Last answer 21 November 2017 Last comment 22 November 2017
0   0 4
2433

views

0

rating

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:

Last answer 20 November 2017 Last comment 20 November 2017
0   0 0
186

views

0

rating