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

What is the sql command to describe table in cache db.

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

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

I got a resultset, and some columns might be a JSON_OBJECT. Is there a way (based on metadata) to determine that the column was formed from JSON_OBJECT function?

set rs = ##class(%SQL.Statement).%ExecDirect(,"SELECT 1 colA, JSON_OBJECT('id':1) col2")
do rs.%Display()

I do not want to try parsing json.

Hi Developers,

Please welcome the new video, specially recorded by @Zhong.Li for the InterSystems IRIS AI Programming Contest:

⏯ IntegratedML-Template Demo

https://www.youtube.com/embed/YxpMc3sODxk
[This is an embedded link, but you cannot view embedded content directly on the site because you have declined the cookies necessary to access it. To view embedded content, you would need to accept all cookies in your Cookies Settings]

Hi Community!

Enjoy watching the new video on InterSystems Developers YouTube and learn more about IntegratedML feature:

⏯ IntegratedML: Predicting Readmissions

https://www.youtube.com/embed/aDo7PfmBnu8
[This is an embedded link, but you cannot view embedded content directly on the site because you have declined the cookies necessary to access it. To view embedded content, you would need to accept all cookies in your Cookies Settings]

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),'-',''),' ',''),':','');

Since IRIS 2020.1, changed the way how embedded SQL compiled. Now it compiles upon the first execution of such queries.

Is there any way, I could switch that feature back to the previous behavior, or get some more control over it?

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.

Hey I would like to INSERT mulitple rows to 1 table. I need to intersystems cache equivelant for

INSERT INTO table_name (column_list)
VALUES
(value_list_1),
(value_list_2),
(value_list_3);

Have tried this post https://community.intersystems.com/post/how-execute-multiple-query-or-script-sql-management-studio
but still got an error

A More Industrial-Looking Global Storage Scheme

In the first article in this series, we looked at the entity–attribute–value (EAV) model in relational databases, and took a look at the pros and cons of storing those entities, attributes and values in tables. We learned that, despite the benefits of this approach in terms of flexibility, there are some real disadvantages, in particular a basic mismatch between the logical structure of the data and its physical storage, which causes various difficulties.

The Caché System Management Portal includes a robust web-based SQL query tool, but for some applications it’s more convenient to use a dedicated SQL client installed on a user’s PC.

SQuirreL SQL is a well known open source SQL client built in Java, which uses JDBC to connect to a DBMS. As such, we can configure SQuirreL to connect to Caché using the Caché JDBC driver.

I have a class that writes to an external SQL Server database. When the field is empty, it does not write the column and the column has the value NULL. In some cases, I want the column to be the value of the empty string instead of NULL.

See Part 1 here.

Part 2: Index Handling