#SQL

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;

On IRIS version 2024.1.0.262.0 I have a table that store streams as %GlobalCharacterStream.

But depeding on the blob being saved I get the error: [Error: <<MAXSTRING>]

Need to make ODBC connection to fetch data from SQL Server to IRIS 2021.1. Once we are able to query the data from SQL server, need to Pass it in ensemble production

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?

Hi Community,

I am attempting to create a new table by executing a SELECT statement that involves joining multiple tables. However, I encountered an error during execution: '( expected, AS found^Combined AS.' I would also like to create a cube based on this SQL table. However, during the cube creation process, I am prompted to specify a source class, and I'm unsure which class to use as it requires an existing class. Could you please help me identify the issue with the table creation, and provide guidance on selecting the appropriate source class for the cube creation?"

Hello Community,

Generally we could insert the stream object through SQL for stream properties and insert the list values for list of %String property. So, Is there any way to achieve this insert for the list of objects property( ex: Property CodeTable As list of Sample.CodeTable) via SQL?

Hi folks,

I made a solution (https://openexchange.intersystems.com/package/iris-pretty-gpt-1) and want to use it like

Hello Community,

I have couple of question about the DynamicSQL.

1. The %ObjectSelectMode is working perfectly in the DynamicSQL for the object property in direct query. However this is not supported for class query. Is there any way to resolve this / Get the object value for class query result.

Hi,

Any recommendations on how i can add a logic if an incoming record that is already existing in the table but different total amount value. I need to subtract the totalamt value on the input file vs the totalamt value on the table and update the table with the difference of totalamt. Any help is much appreciated. Thanks!

Hi all,

We are doing a SQL server upgrade to SQL server v.15 from a legacy server which had v.10 . I have our Cache server as a linked server int order to pull data from our system. When building out the ROWSPEC, I have my fields typed as %String with various lengths with one field having a max length of 15000. The SQL server is trying to interpret this as a text data type instead of varchar(n). Text is a depracated data type since SQL server 2008. The error below is produced when trying to query from this view

Looking to pull out HL7 Fields data to put into columns in a SQL table. How to handle the repeated fields & segments data to SQL.

Operations over the extracted data in SQL server can help for Analysis.

Appreciate your Help!!

Hi.

I have a query:

SELECT '['||Material->Sifra||'] '||Material->Opis AS Material,
SUM(MasaBlago) AS MasaBlago
FROM Tehtanje.Dokument
WHERE DatumDokumenta BETWEEN '01/01/2023' AND '04/01/2023'
AND (Material->Sifra %INLIST $LISTFROMSTRING('5,7',','))
GROUP BY Material
ORDER BY %EXACT Material

The query returns all rows where Material->Sifra is 5 or 7. That's OK.

If I want to get all rows where Material->Sifra is NOT 5 or 7 I use query:

Newbie here...

I am looking to use fhir to gather patients in our end point that have a certain diagnosis and or certain insurance.

Is this what fhir can be used for?

In the past I have done the normal SQL query/extract to CSV file. Looking to use fhir for this for future.

Thanks in advance for your time

Dean-O

Hi All,

I want to run an SQL like below

Select ID,Rollno,Marks,Name,Section,Teacher from Marks left outer join Student on Marks.StudentID=Student.ID

Now, the Student.ID is having the encrypted value which is not matching with Marks.StudentID (as this is clear value).

I can get the clear value using encrypted value as follow

^StoreValue("EncryptStudentID",Student.ID)=$LB("123"). That 123 is the clear Student.ID which I need to match in SQL query (ON clause).