SQL

Syndicate content 29 

I try to create a column with computeonchange. 

Its works with CREATE TABLE sql command, but if i use ALTER TABLE ADD COLUMN sql command this computeonchange doesn't works.

any reason why?

 

example:

CREATE TABLE MyStudents (   Name VARCHAR(16) NOT NULL,   surname VARCHAR(16))

alter table MyStudents add column fullname Varchar(50) COMPUTECODE { SET {fullname}={Name}_ "?" _{surname}} COMPUTEONCHANGE (Name,surname)  

insert into SQLUser.MyStudents (Name, surname) values ('name1',null)

update MyStudents set name = 'name' where name = 'name1'

0   0 2
0

comments

78

views

0

rating

Hi,

When Creating a custom security role, what privileges do I need to add to it, in order for the users that I grant him the role, to be able to use 

dynamic SQL to perform read only queries on all tables in a namespace?

I have a security role, that contains the %Service_SQL privilege,

And yet when I connect to a terminal and try to run an SQL query using dynamic SQL , I get a "user is not privileged for the operation" error.

Last answer 25 February 2019 Last comment 26 February 2019
0   0 4
221

views

0

rating

SetIdentityInsert call controls the ability of the user to specify a value for the IDENTITY property when saving a new object, a value for the IDENTITY column or an explicit ROWID value in an SQL INSERT. If IDENTITY_INSERT is false and the user specifies an explicit IDENTITY or ROWID value when saving a new object or inserting a new ROW then an error condition is reported.

Setting takes effect immediately and lasts for the duration of the process or until SetIdentityInsert is called again.

My question is how can I change this setting system-wide?

Last answer 22 February 2019 Last comment 22 February 2019
0   0 2
84

views

0

rating

Is there a way or can it be done to use conditional logic in sql like so

    

 Query Q1(formal as %String) As %SQLQuery [ Final ]
{
    
    SELECT patientnumber,ID, CASE
    WHEN ID = 50 THEN "The is 50"
    WHEN ID = 30 THEN "This is 30"
    ELSE "The quantity is under 30"
END FROM Audit.Table WHERE ID = :formal AND EndDate is null} 

}

Last answer 14 February 2019 Last comment 14 February 2019
0   0 4
158

views

0

rating

Problem

I got a quick answer from this forum yesterday so I'm going to try my luck again today!  I've hit an error in another table when trying to extract through the Cache ODBC driver, but this one gives me less details and I'm struggling to pinpoint what might be causing the error.

The table I am trying to extract is called REF_TABLE_ONE.

Here's the Error:

Last answer 31 January 2019 Last comment 1 February 2019
0   0 3
182

views

0

rating

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:

Last answer 30 January 2019 Last comment 30 January 2019
0   0 2
163

views

0

rating

Let Say I have 
Class Carrier Extends %Persistent

Property Employee As Array of Class Employees

Class Employees Extends %SerialObject

Property Name As %String

Property Address As Array of Class AddressDetail

Class AddressDetail Extends %SerialObject

Property Street1 As %String

Property Street2 As %String


I can get value for Employee Name  as Employee_Name.

But I want to Get Value of Address Street1 and Street2 of Class AddressDetail using SQL Query
How can I get that?

Last answer 18 January 2019 Last comment 25 January 2019
0   0 2
168

views

0

rating

There are three things most important to any SQL performance conversation:  Indices, TuneTable, and Show Plan.  The attached PDFs includes historical presentations on these topics that cover the basics of these 3 things in one place.  Our documentation provides more detail on these and other SQL Performance topics in the links below.  The eLearning options reinforces several of these topics.  In addition, there are several Developer Community articles which touch on SQL performance, and those relevant links are also listed.

There is a fair amount of repetition in the information listed below.  The most important aspects of SQL performance to consider are:

  1. The types of indices available
  2. Using one index type over another
  3. The information TuneTable gathers for a table and what it means to the Optimizer
  4. How to read a Show Plan to better understand if a query is good or bad
Last comment 18 January 2019
+ 8   3 2
390

views

+ 8

rating

I have a persistent class.

I want to store one of the properties there as a stream or a string depending on a size.

99% of values would be strings (less than $$$MaxStringLength characters) so I don't want to store everything as streams.

What do you think of this approach?

What's the best architecture to implement in this situation?

Last answer 17 January 2019 Last comment 18 January 2019
0   0 2
158

views

0

rating

Let's say I have this property:

Property FavoriteColors As List Of %String;

I heed to convert it to JSON using SQL or at least without object access (so direct global access).

What's the fastest way to do that?

I thought about JSON_ARRAY and JSON_ARRAYAGG sql functions but they don't do that.

Last answer 5 September 2017 Last comment 19 January 2019
0   0 3
621

views

0

rating

I've mapped multiple tables (UNION on mapped SQL classes) into a view, using CREATE VIEW.

Through ODBC, in Entity Framework, I am querying against that view and offering paging. The paging is implemented using IQueryable.Skip and IQueryable.Take. 

Skip seems to have unexpected results, I believe due to incorrect SQL generated by the Entity Framework provider, though perhaps I've done something incorrectly. The generated SQL looks similar to this (with some bits replaced or altered for security reasons), the basic structure is unaltered.

Last comment 16 January 2019
0   0 2
344

views

0

rating

As we all know, Caché is a great database that accomplishes lots of tasks within itself. However, what do you do when you need to access an external database? One way is to use the Caché SQL Gateway via JDBC. In this article, my goal is to answer the following questions to help you familiarize yourself with the technology and debug some common problems.

Last comment 28 December 2018
+ 8   2 4
2042

views

+ 8

rating

Hi Community!

New session recording from Global Summit 2018 is available on InterSystems Developers YouTube Channel:

An Outlook on Scaling Out

 

0   0 1
0

comments

74

views

0

rating

A very simple SQL

SELECT field1 F1, count(field2) CntF2
FROM GCT.PA_Data
where field1 is not null
group by field1

gives an error back:

 [SQLCODE: <-461>:<Communication link failure>]

  [%msg: <Connection 'TRAKCARELIVE': Execute: SQLState: (HY000) NativeError: [10053] Message: [Cache ODBC][State : HY000][Native Code 10053] [c:\intersystems\ensemble\bin\cache.exe] Software caused connection abort> ]

Last answer 12 December 2018 Last comment 14 December 2018
0   0 4
139

views

0

rating

Hello,

I'm looking to find if there is a datatype convert equivalent in Object Script to SQL convert function. Have a VarBinary string coming in from source application (which is really performing a SQL dump). The source application uses the standard SQL convert function to convert from varchar to varbinary on their side.

I know &sql(Convert()) should work in Object Script, but am wondering if there is a better way of doing this. 

Getting data in via flat file (Record Map), then using data transform to transpose this data to SDA3. 

Last answer 15 December 2018 Last comment 17 December 2018
0   0 3
142

views

0

rating

Dear all,

In our application made in Caché object script, when we deploy some classes modified we have the option of calling "Purge queries" feature of Caché.

We only were doing this call when a %Persistent class definition (table) was modified, as we assume that no other changes affect the cached queries and we don't  want to purge them by default, as the first run of a query becomes slow.

We had a case where no table definition was changed but a purge queries was the solution.

Last answer 12 December 2018 Last comment 11 December 2018
0   0 3
127

views

0

rating

I have a persistent class that represents cities across the United States.  It is below, but basically has a City Id, Name, Lat, Lon and a few other unimportant fields for this issue.  Anytime I attempt to query on the Latitude or Longitude it immediately returns no results.  My first thought was that it was a casting issue so I tried casting both sides to floats, ints, even strings and in all cases it immediately comes back with no results.  I then decided to cast it to a string and attempt a like statement thinking it might be something about how floats are handled, but still no joy.  Any

Last answer 8 December 2018 Last comment 8 December 2018
0   0 2
145

views

0

rating

Hello,
I need help in health insight. I am trying to generate reports on deep see but i am not able to pull in the patient ids as these are our requirements for the project. Can anyone help me in unlocking this feature.
Can anyone help me with link to correct documentation on how to access the edge gateways of multiple facilities to access the clinical data on sql explorer.

Last answer 28 November 2018 Last comment 25 October 2018
0   0 3
201

views

0

rating

I am trying to use %INLIST in  SQL query using a cursor and the query fails to return results. It appears that the variable I use against %INLIST returns an empty string. All the examples I have seen use result sets and build the query as a string. Is it possible to use %INLIST in a cursor-based query? Below is a a snippet of the code I am using:

Property mylist as %String (MAXLEN="") [InitialExpression = "route1, route2, route3"];

set routeList = $LISTBUILD(mylist)

&sql(DECLARE MyReport CURSOR FOR

SELECT

      ProcessStartDate,

Last answer 3 December 2018
0   0 3
0

comments

162

views

0

rating

When using the JSON_OBJECT() function in Caché SQL on a %String property that contains JSON syntax, it converts the %String into a JSON object instead of escaping it as a string literal. How can I prevent this? (without ridiculous hacks like "add a space to the beginning of the value" as we don't always know which properties will contain these values and I certainly don't want to have to check for nulls and add/remove a space every single place this value is used in the application)

I don't want these strings automatically marshalled into JSON objects.

For example:

Last answer 20 November 2018 Last comment 28 November 2018
0   0 3
264

views

0

rating