#SQL

When I use Escape logic when inserting or updating Oracle Table I'm getting Max-Length exceeded error. With the original value the length is good but after I add Escape Logic, it causes value to be greater than max-length. The original value was "I visited O'Brien before heading out of town." and after added Escape logic it was "I visited O''Brien before heading out of town." Max-Length is 45.

INSERT INTO MyText
    (text)
VALUES
    ('I visited O''Brien before heading out of town.')
                 /\
             right here  

Any ideas around this?

Hello all,

We have our system with AutoParallel enabled:

USER>w ##class(%SYSTEM.SQL.Util).GetOption("AutoParallel")
1

But whenever I try to run any sql the autoparallel does not work. For example, this simple query:

When I force it with %PARALLEL we can see it will effectively run in parallel:

How to add the following to a stored procedure (Cache Studio)
1. Select from a few tables and insert result into a TABLEA.
2. Then select data from the TABLEA, apply some SQL logic, insert results into TABLEB.
3. SELECT * FROM TableA
UNION ALL
SELECT * FROM TableB

I know that you can use Do $SYSTEM.SQL.Schema.ImportDDL() to insert sql files into IRIS however I was wondering if there is a way that I can upload .sqlite files into iris? I have about 20 .sqlite files that I need to get into my database. I tried using the ImportDDL method but it said "SKIPPING non-SQL SOURCE:"

I'm trying to use the EnsLib.SQL.Operation.GenericOperation component in a production to read a column from a Redshift table that is set up as VARCHAR(65535) and am getting the following error.

An error was received : ERROR #5023: Remote Gateway Error: JDBC Gateway getClob(0,1) errorRemote JDBC error: Cannot convert the column of type VARCHAR to requested type long..

It is probably somewhere in the doc. So hard to search. Hope InterSystems going to AI on their community/support data.

How to see a meaningful value in management portal - sql explorer. For instance
Class MySetting Extends %Persistent{
Property Name As %String;

Class MyObj Extends %Persistent{
Property Setting As MySetting;

I have issue with one of my queries. That query work fine for ages, but suddenly stop works and giving me SQLCODE 100 in the routine.

Hello Community,

I have encountered the following SQL problem trying to create a table from a SELECT statement.

I narrowed the problem down to the following example:

1. The SELECT statement alone works as expected.

SELECT m.Name, h.Status 
FROM EnsLib_HL7.Message m, Ens.MessageHeader h 
WHERE h.MessageBodyClassName='EnsLib.HL7.Message' AND h.MessageBodyID = m.ID

2. But if "CREATE TABLE ... AS" is added it fails

Hello Community,

I've seen two cached queries are generated while executing a query. So, Why the additional query is generated and what is the use of it.

Hence the question: is there a way to do that?

The goal is to get data (from half a thousand to 3-4 thousands lines) from DB, calculate standart deviation then use it as logical condition in analyzer.

For example IF std > custom_value = show_the_result ELSE null

There is a STDDEV(MDX) method used in Analyzer but it is a measure and it can not be used as logical condition (correct me if i am wrong)

I tried executing the SQL JSON_TABLE query with large JSON string(more than 200000 characters) and I got the below error. I'm curious about this under the hood workflow and how does it reach reaches MAXSTRING.