New post

#SQL

13

SQL is a standard language for storing, manipulating and retrieving data in relational databases.

All time

I'm trying to execute SQL on a EC2 via SSM:

import boto3

instanceid = "i-123456789"
sql = """SELECT path FROM Security.Applications WHERE ID = '/csp/sys'"""
template = """su - irisusr -c 'cat << EOF | iris sql iris -U %SYS
                """ + sql + """
                        q
                        EOF'
                       """
template = [line.strip() for line in template.splitlines()] 
template = """\n""".join(template) 
ssm_client = boto3.client('ssm') 
response = ssm_client.send_command(
            InstanceIds=[instanceid],
            DocumentName="AWS-RunShellScript",
            Comment=AWS,
            Parameters={'commands': template})

0 0
0 206

When working with InterSystems IRIS, database developers and architects often face a critical decision: whether to use Dynamic SQL or Embedded SQL for querying and updating data. Both methods have their unique strengths and use cases, but understanding their performance implications is essential to making the right choice. Response time, a key metric in evaluating application performance, can vary significantly depending on the SQL approach used. Dynamic SQL offers flexibility, as queries can be constructed and executed at runtime, making it ideal for scenarios with unpredictable or highly variable query needs. Conversely, Embedded SQL emphasizes stability and efficiency by integrating SQL code directly into application logic, offering optimized response times for predefined query patterns.

In this article, I will explore the response times when using these two types of SQL and how they depend on different class structures and usage of parameters. So to do this, I'm going to use the following classes from the diagram:

6 3
0 142
Question
· Jan 30, 2023
SQL Query Help in $ListGet

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).

0 2
0 200

Hey Developers,

Watch the latest video on InterSystems Developers YouTube:

⏯ Working with InterSystems IRIS Cloud SQL

https://www.youtube.com/embed/okMOwIW-yXY
[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]

3 1
0 180

I need to show the absence of data, so I have to join the list of predefined values with a result of a select statement.
However, it seems like Table Value Constructors in JOIN are either not supported, or I do not understand the syntax.
Basically, I am going for something like this:

Select v.valueId, m.name 
 From (values (1), (2), (3), (4), (5)) v(valueId)
     left Join otherTable m
        on m.id = v.valueId

Is it supported in Cache SQL or not?

0 6
0 194

Hi everyone.

I have a use case where I'm using embedded SQL within a Business Process to interact with a SQL table. However, when it comes to deployment into our production, the table wont form part of the deployment package created from the production.

Beyond manually creating the table on the production system, is there a standard way of ensuring that a table needed for a class is created during deployment?

0 2
0 195

Hello!

I'm new to the system I'm working on, and I have a part of a statement that needs to grab the previous month.

....and MONTH(TX3.date_of_service)= MONTH(dateadd(mm,-1,GETDATE()))

However, when it hits January, I'm left with month 0.

Does anyone know a workaround to grab December of the previous year, while still functioning normally for the rest of the current year.

Thank you!

0 4
0 193

In the modern world, the most valuable asset for companies is their data. Everything from business processes and applications to transactions is based on data which defines the success of the organization's operations, analysis, and decisions. In this scenario, the data structures need to be ready for frequent changes, yet in a managed and governed way. Otherwise, we will inevitably lose money, time, and quality of corporate solutions.

3 0
1 195
Question
· Jan 19, 2024
INSERT OR UPDATE only inserts

I am trying to do a INSERT OR UPDATE SQL query, but the query always inserts new rows to table. Is it how I defined the table or am I mising something in the query?

table defines as,

Class AH.AHCOMMON.Tables.HospDepartmentData Extends %Persistent [ Language = objectscript ]
{

0 1
0 193

This article describes a significant enhancement of how InterSystems IRIS deals with table statistics, a crucial element for IRIS SQL processing, in the 2025.2 release. We'll start with a brief refresher on what table statistics are, how they are used, and why we needed this enhancement. Then, we'll dive into the details of the new infrastructure for collecting and saving table statistics, after which we'll zoom in onto what the change means in practice for your applications. We'll end with a few additional notes on patterns enabled by the new model, and look forward to the follow-on phases of this initial delivery.

10 5
3 93

Hi,

I tried to join my local DB table with link table. but I am getting below error.

5475 5475 reporterr2+40^%occRoutine Error #5475: Error compiling routine: %sqlcq.HSANALYTICS.cls483. Errors: %sqlcq.HSANALYTICS.cls483.cls(%OnNew+5) : SQLCODE=-161 : References to an SQL connection must constitute a whole subquery

I tried to execute below query in Managementportal

select dg.ID from HSAA.Diagnosis dg
left join LinkTableData.FacilityFullList la on dg.ID=la .ID

0 4
0 185

In this article, exceptions are covered.

Working with Exceptions

Instead of returning a %Status response, you can raise and throw an Exception. You are then responsible for catching the exception and validating it. IRIS provides five main classes to handle exceptions effectively. Additionally, you can create custom exception class definition based on your needs.

3 0
0 186

I implemented a Python Flask application for the 2024 Python Contest with a page that provides common form fields for an outgoing email such as the To and CC fields. And it lets you input a message as well as uploading text based attachments.

Then using LlamaIndex in Python, the app analyzes the content you put in and returns to you in a result box if there is anything that should stop you from sending that email.

Take a look at the Github repo here.

4 3
0 126
Question
· Jul 11, 2023
debugging %Library.ResultSet

Hi All,

I am trying to fetch resultset using the below code -

set rs = ##class(%ResultSet).%New("Simple.Person:ValidateAge")

do rs.Execute()

it is giving me error while the same code I run it through iris terminal worked fine.
I want to understand that problem behind this. Also how can I check the possible methods that I can use on 'rs' somewhat dir() does in python.

0 2
0 179

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?"

0 1
0 177