SQL

Syndicate content 32 

Mark All as Read

Hi, I want to INSERT a customerID and customerName to a table if the customerID does not exist. Or UPDATE if they've changed their name.
I've reviewed the documentation (https://docs.intersystems.com/irislatest/csp/docbook/Doc.View.cls?KEY=RS...) and have confused myself with the INSERT OR UPDATE statement. Rather than using two individual statements , is there a way to get it in one?

What I have at the moment doesn't do anything. In fact it errors.

Last reply 3 days ago
0   0 2
68

views

0

rating

Hi:

I have an sql outbound adapter. Sometimes we have trouble connecting to the database. 

The timeout in develpment is set to 15 seconds in live it is 150 seconds as it is an always connected Buisness operation. 

I thought adding E=S or/and X=S would suspend the message. Why does it not? 

Is the only way around then adding something to the buisness operation itself/ creating a customised SQL buisness operation rather than EnsLib.SQL.OutboundAdapter?

 

Thanks 

Mark

Last reply 3 days ago
0   0 3
33

views

0

rating

InterSystems IRIS currently limits classes to 999 properties.

But what to do if you need to store more data per object?

This article would answer this question (with the additional cameo of Community Python Gateway and how you can transfer wide datasets into Python).

The answer is very simple actually - InterSystems IRIS currently limits classes to 999 properties, but not to 999 primitives. The property in InterSystems IRIS can be an object with 999 properties and so on - the limit can be easily disregarded.

-1   1 1
112

views

-1

rating

This is a coding example working on IRIS 2020.1 and on Caché 2018.1.3 
It will not be kept in sync with new versions      
It is also NOT serviced by InterSystems Support !   

In most cases, a global used by default storage has just 1 subscript level that represents the IDKEY.
For an index-globals we may see 2 or more subscript levels.
Arrays, or parent-child relationships or persistent classes extending a base data class
are examples where we see more levels. Though all these globals are quite uniform.

+ 2   0 1
0

replies

46

views

+ 2

rating

This is a coding example working on IRIS 2020.1 and on Caché 2018.1.3 
It will not be kept in sync with new versions      
It is also NOT serviced by InterSystems Support !   

Globals in Caché / Ensemble / IRIS are normally invisible over SQL access
This example shows how to overcome this limit.

Last reply 6 days ago
+ 2   0 1
95

views

+ 2

rating

This is a coding example working on Caché 2018.1.3 and IRIS 2020.2 
It will not be kept in sync with new versions 
It is also NOT serviced by InterSystems Support !

Standard error logs in IRIS / Caché / Ensemble are written global ^ERRORS.
As this piece dates back some decades back to previous millennium its structure
is far from the typical SQL storage structures.

Last reply 7 days ago
+ 6   1 3
176

views

+ 6

rating

cache 2017.2 windows 10

Is there any way to change the sql Shell interface to output a <TAB> between the fields instead of outputting one or more spaces between columns

 

I needed to run an sql ( that took a while to run) and then  cut and paste the generated output into excel, trouble was the paste into excel was not very good for columns when there's spaces used between the columns

 

kev

Last reply 9 days ago
0   0 1
40

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 you

Last reply 11 days ago
0   0 3
651

views

0

rating

The Art of Mapping Globals to Classes (4 of 3)

The forth in the trilogy, anyone a Hitchhikers Guide to the Galaxy fan?

If you are looking to breathe new life into an old MUMPS application follow these steps to map your globals to classes and expose all that beautiful data to Objects and SQL.

If the above does not sound familiar to you please start at the beginning with the following:

The Art of Mapping Globals to Classes (1 of 3)

Last reply 20 July 2020
+ 5   0 6
1,167

views

+ 5

rating

Hi,

I get this error [Cache ODBC][State : HYC00][Native Code 469] and zero rows returned with the following query.

SELECT "Timestamp","Skillset"  FROM "dbo"."iSkillsetStat" 
WHERE (Timestamp >= '2020-06-29 00:00:00' 
AND Timestamp < '2020-06-30 00:00:00')

I'm using Qlik Attunity Replicate to extract data, the query was formatted  by the tool. The query will return data in excel and SSIS, but not using Replicate.

So far I've tried using two ODBC drivers

-----------------------------------------------------

Last reply 15 July 2020
0   0 1
80

views

0

rating

Let's say we have two serial classes, one as a property of another:

Class test.Serial Extends %SerialObject
{
Property Serial2 As test.Serial2;
}

Class test.Serial2 Extends %SerialObject
{
Property Property As %String;
}

And a persistent class, that has a property of test.Serial type:

Class test.Persistent Extends %Persistent
{

Property Datatype As %String;

Property Serial As test.Serial;

}

So it's a serial, inside a serial, inside a persistent object.

Last reply 15 July 2020
+ 4   0 2
258

views

+ 4

rating

Hi, I have a timestamp of 201906192359 with a HL7 and I need to add a minute to it to get 201906200000. Is there an easy way within Healthshare to do this?

It seems easy enough within SQL but I cannot get the SQL to work within Healthshare,  this is what I have for SQL which does the job in SQL Server.

 SELECT REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16),DATEADD(mi,1,STUFF(STUFF(@test,11,0,':'),9,0,' ')),120),'-',''),' ',''),':','');

 

Last reply 22 June 2020
0   0 3
74

views

0

rating

Hello, In the DTL, is there a way to set a value for the HL7 data element in the code section? For example, set target.SetValueAt("PID:3(k1).1)") = mrn (mrn is the value returned from the SQL query) When I ran the test utility, I got this error message. ERROR ErrException: zTransform+27^testclass.TEST.1 *SetValueAt,EnsLib.HL7.Message -- logged as '-' number - @' set target.SetValueAt("PID:3(k1).1)") = mrn' I tried both target.SetValueAt("PID:3(k1).1)") and target.GetValueAt("PID:3(k1).1)"). That didn’t make a difference. The code still error out.

Last reply 11 June 2020
0   0 2
67

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 reply 11 June 2020
0   0 4
3,798

views

0

rating