SQL

Syndicate content 26 

Any insights, news, alternatives or experience about sequence pattern querying like with Oracle's MATCH_RECOGNIZE, more declarative, less simple direct COS way? Both obvious solutions (shadowing data to oracle cloud or implementing MATCH_RECOGNIZE compiler in COS from scratch) seem big overkill. Do Intersystems have plans to adopt this "2007 ANSI standard proposal" and "SQL:2016 standard"

My problem is regular need to do analytics on medical lab data dynamics and correlations, and while its always doable by COS and multiple SQL statements, such handcrafting for every new hypothesis/idea or statistic takes some hours each and definitely is not something that can be delegated to non-programmers.

Last comment 4 May 2018
0 2
0

answers

148

views

0

rating

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

SELECT * FROM
(
SELECT tMatch.DataValue, Count(*)
FROM ResponseRMAP.Record bRecord
JOIN Match ON tMatch.TableName = 'Match' AND tMatch.KeyName = bRecord.ResponseCode
WHERE bRecord.%ParentBatch = 55

)
 
Last comment 4 May 2018
0 2
0

answers

235

views

0

rating

Hi,

The following sql statement is working through the SMP but couldn't make it work through the code.

The table is a linked table to oracle db.

Tried to change the date property in the linked table class from %Date to %String and also tried using %SQL.Statement and %Library.ResultSet

this work through SMP:

INSERT INTO PACKAGE.SOME_TABLE_NAME

(EFFECTIVE_START_DATE,MORE,DATA,HERE)

VALUES (to_date ('2018-04-01','yyyy-mm-dd'),123,456,789)

In the code i'm using '?' in the statement for the parameters.

So in the code it's go something like this (where the to_date is in the sql statement)

Last answer 23 April 2018 Last comment 24 April 2018
0 2
146

views

0

rating

How to convert SQL persistent classes to JSON output - I have tried 1 option but appears to be very lengthy process.

I have many other SQL storage's defined and have to expose them as well to JSON. Can anyone please suggest a better approach.

My column names usually match with my Json properties.

My User.TestClass is the class with GetJsonList() as my method to return /output JSON formatted data.

The Code tried and SQL storage structure are as follows -

Last answer 19 April 2018 Last comment 18 April 2018
0 3
318

views

+ 1

rating

This is a sample Ensemble/Health Connect production which demonstrates how to receive an HL7 order (ORM) inbound from a file, extract fields (in this case, basic demographic information), and insert those into a table in an external SQL database via ODBC.  

Included in the zip file:

  • Exported code
  • Sample ORM message
  • 'How to configure' doc 
Last comment 19 April 2018
0 3
825

views

+ 1

rating

I am tasked with using CACHE to insert data retrieved from a CACHE data base and insert it into an sql database. The columns in the sql table that I am trying to insert data into have names that contain underscores such as "ACCESSION_DATE" I found a utility in CACHE to connect to the sql data base and perform inserts. I have the data I need to insert. I need to pass the  data into the sql utility referencing the column names.

I tried using indirection to set the data into underscored variables, but that isn't allowed  in cache object script.

Anyone have suggestions as to how to do that?

Thank you,

Mike Minor

Last answer 7 April 2018 Last comment 13 April 2018
0 4
363

views

0

rating

Has anyone use any other setting then -1 on the Stay Connected setting on a SQL.OutboundAdapter? I am having issues with a particular database and wonder if it would be beneficial since I only connect once a day to read the data from the Adapter to create an HL7 message.

Thanks

Scott Roth

Last answer 6 April 2018 Last comment 9 April 2018
0 3
176

views

0

rating

Morning guys  I have a SQL operation that I use to read data and results to a record map. But seems to have trouble saving the objects to a record map as I get this message

ERROR ErrException: zInsertObject+2^%Library.RelationshipObject.1 -- logged as '-' number - @''

please help  here is my code let me know if I missing anything thank you in advanc

Last answer 5 April 2018
0 2
0

comments

145

views

0

rating

Hi all,

I have an operation which uses the SQL outbound adapter to query a data source set up using a system DSN  on the server which uses the Intersystems ODBC35 driver (this is a cache db hosted elsewhere).

Whenever Ensemble runs the following query:

SELECT lnkdid,
c_fastsearch_code,
c_drugfull
FROM JAC.drug_basic
WHERE c_drugfull LIKE 'Para%'

No data is returned, however running the same query via SQL Server Management Studio, all expected rows are returned.

Also if I run  the following via Ensemble:

SELECT lnkdid,
c_fastsearch_code,
c_drugfull
FROM JAC.drug_basic
WHERE c_drugfull NOT LIKE 'Para%'

This returns all the rows as expected

It's probably obvious to someone what the issue is, any help much appreciated.

Last answer 16 March 2018 Last comment 9 February 2018
0 4
238

views

0

rating

I came across How to import a tab separated text file into a SQL table programmatically?, which appears to indicate that I can pass the filename and all records will be imported. However, when I put use the Import method only a single record gets imported. However, all records are imported if I use the management console to import the file by selecting it and choosing the options.

Has anyone ran into this in the past?

Last answer 17 February 2018 Last comment 14 March 2018
0 2
207

views

0

rating

Hi all,

I am new in Cache. I have to update a record with a long text field, for that,  I am using ODBC but the issue is that it is returning an error when I execute the ODBC  SQL statement because the field contains some commas, colon, double quotes, single quotes, and CR LF characters.

There is a way to escape this chars? I have seen I can save the field when I replace the single quotes with a double single quote (it's -> it''s) but I can not save the information when the field contains double quote chars. What about the other chars?

The statement is something like:

SET description = '240-page + cover perfect bound book. Insides on 29" 22.1 ",  productName = ' "AAA244 - Sometext, sometext.  "  '  WHERE id=25

Any Ideas? Than you in advance.

Last answer 13 March 2018 Last comment 13 March 2018
0 3
320

views

0

rating

I have several BPL's that act like SQL Server Integration Services (SSIS) where they are transferring data. When I do this they are all being written under 1 session id, is it possible to make the BPL create multiple session ID's as it is cycling through a Snapshot, so it is easier to read in the trace?

Thanks

Scott Roth

Last answer 9 March 2018 Last comment 9 March 2018
0 2
182

views

0

rating

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 yo

Last answer 7 March 2018
0 2
0

comments

330

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:

 

SELECT Name,Home_Street

FROM Sample.Person

Last comment 6 March 2018
0 2
284

views

+ 4

rating

Hi, Community!  

Please find a new session recording from Global Summit 2017:

Optimizing SQL Queries

 

0 3
0

comments

118

views

+ 1

rating

I am working my way through some SQL documentation.

The documentation follows and my question comes after.

A query supplied to %Prepare() can contain input host variables, as shown in the following example:

  SET minage = 80

  SET myquery = 3

  SET myquery(1) = "SELECT %ID AS id, Name, DOB, Home_State"

  SET myquery(2) = "FROM Person WHERE Age > :minage"

  SET myquery(3) = "ORDER BY 2"

  SET qStatus = tStatement.%Prepare(.myquery)

This talks about input host variables

I thought that input host variables only work with Embedded SQL, not Dynamic SQL

Thank you

Last answer 21 February 2018 Last comment 22 February 2018
0 3
157

views

0

rating

Hi,

I am afraid I am stuck again. I have ensemble connected with a MsSQL database and I have a service that calls a store procedure. All is working, and I can get strings from the database in the service. The problem is that the data the store procedure returns is suppose to be an HL7 message so I need to convert the string into HL7 format. I have been using the ##class(EnsLib.HL7.Message).ImportFromString  method but although I have a valid HL7 message from the string received from the DB...apparently the content of the message is being modified

Last answer 19 February 2018 Last comment 19 February 2018
0 2
338

views

0

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

Of course, using the feature of multiple storage maps could possibly allow to escape - somehow.
But in those cases where you have many many GB of data stored in that way and
where you work with millions of lines of old  - often poorly documented - code.
You wouldn't think seriously more than 5 min. if you change the storage structure or not.
So the bitmap is not with you

Last comment 17 February 2018
0 5
310

views

+ 7

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 5
327

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.

ADAPTER = "EnsLib.SQL.OutboundAdapter";

This is the code that works...as you can see the parameter is added to the sql query...not the best solution but the only that works at the moment

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

views

0

rating

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.

  1. I could append the GUIDs of the rows in the two tables, but there are a number of clients that expect a maximum length of 50 on this unique ID. Two GUIDs appended make 72 characters.
  2. I could append the two GUIDs and then truncate the result, but now I'm worried about collision.

What's the chance of collision if I append the GUIDs and truncate the result to 50 characters? Is there a good way to solve this without updating the clients?

Last answer 31 January 2018 Last comment 31 January 2018
0 3
358

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
0 8
836

views

+ 12

rating