New post

#SQL

16

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

In Oracle database, the synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.

You generally use synonyms when you are granting access to an object from another schema and you don't want the users to have to worry about knowing which schema owns the object.

Is IRIS SQL sintax has something like this: (oracle sintax)

1 1
0 181

Hi,

The question is about queries on the System->SQL page. I have a class with a few columns on a global and the test output looks as expected, below. This works as expected as well returning a single row: select * from Utils.RoutineAuditReport where counter=4. However, other columns give an odd error like " Field 'AG' not found in the applicable tables^ SELECT * FROM Utils . RoutineAuditReport WHERE UserR = "AG". As you can see below, both the UserR column and the AG entry in it do exist. What could be the problem?

Thanks in advance,
Anna

0 2
0 179

In show Query messages in the message viewer the head.%Id is always used. How do you do this via your own sql/objectscript as fast as the portal does a search as using dates searching Ens.MessageHeader on portal is slow.

For instance if you try do a search saying (TimeProcessed >='2023-06-01 00:00:00.000' and TimeProcessed <'2023-06-02 00:00:00.000') it is slow but using the portal the search would know this is head.%ID >= 5344549861 AND head.%ID <= 5347641372. How do you utilize this in your own queries as can't see the logic in EnsPortal.MsgFilter.Assistant

0 3
1 179

Is it planned that LOAD DATA takes into account several DATE/DATETIME formats with, for example, a parameter indicating the format used in the source data?

example :

LOAD DATA .../...
USING
{
  "from": {
    "file": {
       "dateformat": "DD/MM/YYYY"
    }
  }
}
2 3
0 176
Question
· Nov 29, 2023
Cloud SQL

I want to connect the app to Cloud SQL but when I registered and created the deployment (trial) I got the error:

Did anybody face such an error?
During the creation of an account, there was a message like 'username is taken' but the account have been created anyway. Maybe it could cause this problem.

0 5
0 175

So I am working with an inherited SQL query that queries 8 different tables. 5 of which have over a million records.
I have 3 different servers.

Server 1, Server 2 and Server 3. They all have the same data/tables/structure across all servers.
Server 3 has an Iris database engine and the other 2 are Cache 2015.1.4.

I have a problem with views and performance across the servers being inconsistent.

Thankfully Server 1 is the current live server that performs "fast enough".
Server 2 is being synced from Server 1 and acts as a report server.

0 1
0 175

Hello everyone!

Some time ago, I changed the configuration in SQL Runtime Statistic to "Turn on Stats code generation to gather stats at the Open and Close of a query". With this change, the CACHE base (cache/mgr/cache/) has grown a lot to reach 198GB.

Yesterday, I returned the configuration of SQL Runtime Statistic to the default which is "Turn off Stats code generation" and the cache base is no longer growing.

My question is?

0 3
0 173
Question
· Mar 3, 2023
? %QUERY Wizard in VSCode ?

For my recent tutorial, I used %Query Wizard of Studio.

Could any of the VSCode experts please show me how to generate

Query Statement with input parameters, CONTAINID , ROWSPEC
and the required class methods
- <queryname>Execute()
- <queryname>Fetch()
- <queryname>Close()

I was just not able to do it.
​​​​​​​

3 3
0 169

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 168

Hi All,

I am trying to execute my SQL query using ExecDirect() method and it returns a ResultSet. No doubt it works fine after this I am trying to fetch each value using a loop code below. But before that, I am also checking for %SQLCODE = 100 which is for an empty resultset I think. However, It is not working as desired. In the case of an empty resultset also I am getting %SQLCODE = 0 until result.%Next() is called. Also, %ROWCOUNT is giving 0 in a case where my query is return 1 result row. I am so confused about this.
while resultset.%Next() {

// code

}

0 4
0 162

I am trying to use Dynamic SQL because I need to supply data at runtime.
The generated query returns 0 rows for some reason. If I copy/paste the query into Monitor, it works correctly. I am suspecting it has something to do with dates being the wrong format (I am supplying them in 'YYYY-MM-DD' format). Is that the cause? And if so, how do I supply dates in correct format?

0 1
0 158

I'm wanting to write a query in SQL that will return a row with a count for each day for a given month or year for a specific operation or configname. The following is a start but I'm not finding what I want in the documentation to parse out the TimeLogged field of the table. Nay help is appreciated.

SELECT count(TimeLogged), ConfigName
FROM Ens_Util.Log
where TimeLogged like '2021-07%'
and ConfigName = 'operation_Name'
group by TimeLogged

0 2
0 153

Hi,

The system I am working on processes large numbers of records. Inserts, updates and so forth. There are multiple processes that can potentially work on the same table at the same time.

It is an almost impossible task to try and time these processes to not run at the same time, due to volumes, SLAs, etc.

Every now and then there are a few locking issues, Not many considering the volumes. It is just a bit of a nuisance as we need to reprocess these records later.

0 6
0 153

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 153
Question
· Sep 5, 2019
Ambiguous sort column error

Hello,

I have a small SQL question.

Running an example queries in our Samples Namespace:

1. select top 5 Description,Category from Cinema.Film order by Category - runs fine no issues and returns 2 columns as expected

2. select top 5 * from Cinema.Film order by Category - runs fine no issues and returns 8 columns as expected

3. select top 5 Description,Category,* from Cinema.Film order by ID - runs fine no issues, and returns 10 columns, with my first 2 repeated

4. When I try to combine the first 2 queries:

0 4
0 150

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 141
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 138

Using VECTOR_COSINE() in SQL query to perform a text similarity search on existing embeddings in a %VECTOR column.

Code is below.

Commented out sql query returns this error: SQLCODE: -29 Field 'NEW_EMBEDDING_STR' not found in the applicable tables^ SELECT TOP ? maxID , activity , outcome FROMMain .AITest ORDER BY VECTOR_COSINE ( new_embedding_str ,

Sql query as written returns ERROR #5002: ObjectScript error: <PYTHON EXCEPTION> *<class 'OSError'>: isc_stdout_write: PyArg_ParseTuple failed!

0 10
0 133

If I had:
________________________________________
Class MN.Student Extends %Persistent
{
Property Name As %String;
________________________________________
Class MN.Course Extends %Persistent
{
Property TeacherName As %String;
Relationship StudentName As MN.Student [ Cardinality = one, Inverse = Name ];
________________________________________

For "Cardinality = one" the SQL line below would be sufficient for archiving
INSERT INTO MN.Course (TeacherName, StudentName) VALUES ('Pierre','3')"

0 1
0 123