New post

#SQL

15

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

Hello,

We have a need to track Database changes over time - down to the SQL level of granularity if possible. Such as: User xyz runs routine ^abc and we get something similar to a changelog that tells us: table A had this value updated, insert, update etc....

Is that possible using IRIS level tools (Audit Log, Journal File, etc...) , is there a way to convert the global sets and kills from the journals into SQL level changes?

1 3
1 384

Hi Community,

Please welcome the new video on InterSystems Developers YouTube:

⏯ Getting Started with IntegratedML

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

2 0
0 358

I am trying to populate a table using the sql Data Import Wizard. The input file is a tab delimited text file. But the import keeps failing with a 104 error showing validation for the columns which use %Library.TimeStamp and %Boolean datatypes is failing. Yet when I insert values into the table through a SQL insert command, the values get saved correctly in the table.

For the TimeStamp format in the wizard form, I am choosing YYYY-MM-DD-HH:MI:SS because there was no option for this format: YYYY-MM-DD HH:MM:SS.

0 4
0 307
Question
· May 27, 2021
JDBC counts don't match

I'm using a JDBC driver to connect PGSQL -> Cache. I'm noticing when I run SELECT queries and COUNT(*) command against the same table, I get different result sets. I'm pretty new to Cache in general - so I'm trying to understand why these would be different.

Examples (TransID and InvNum should occur in every "row"):

SELECT COUNT(*) FROM ACCT.Services = 1,090,324 WHERE ACCT.Dept = 483

SELECT TransID FROM ACCT.Services = 1,085,776 WHERE ACCT.Dept = 483

SELECT InvNum FROM ACCT.Services = 586,023 WHERE ACCT.Dept = 483

0 8
0 190

Hey Developers,

Enjoy watching this new video on InterSystems Developers YouTube:

⏯ Demo of REST APIs defined in Swagger, built with InterSystems IRIS interfacing with a SQL backend

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

Open Exchange app
2 0
1 649
Article
· May 11, 2021 8m read
IRIS in Astronomy

In this article we are going to show the results of the comparision between IRIS and Postgress when handling Astronomy data.

Introduction

Since the earliest days of human civilization we have been fascinated by the sky at night. There are so many stars! Everybody has dreamed about them and fantasized about life in other planets.

10 7
0 642
Question
· May 12, 2021
Cache sql query

Hi ,

I am trying to retrieve the contact type column data of a patient based on the recent date as shown below.

for instance

contacttype Datefrom

contact 1. 24/03/2020

contact 2. 20/05/2021

i need to retrieve the second contact2 based on the recent date.

i tried like below but not working .

Case max(datefrom) >0 then contacttype

End as contacttype

Can anyone please tell me why this is not working.Any other options?

Thanks

jude

0 3
0 268

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 171

Hi,

I'm getting thousands of errors like the ones below:

2021-05-03 14:11:44 [SQLCODE: <-400>:<Ocorreu um erro fatal>] [Cache Error: <<READ>Dispatch+14^%SYS.BINDSRV>] [Location: <ServerLoop>] [Client info: <Username: KIORAS, Node Name: 3IK0VLS5UU, IP Address: 192.168.1.223, Executable Name: EXTRService.exe, Internal Function: B0>] [%protocol: <52>] $Id: //ce/2018.1.2/kernel/common/src/aclass.c#1 $ 23468 104

0 1
0 478

The boss (reasonable intelligent ;-) wants to connect to the cache database through Excel, but unless I explain what he's looking for (field names DB names etc) then I can see him continually coming back for "what's the link here and how do I get the delivery company name etc etc"

is there a SAFE way I can give him access to our database using an intelligent visual user interface.

I had thought about the SQL Query Builder within the management portal, but the thought of giving him access to all of the other functionality of the management portal.

0 3
0 230

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 177

Hi Community,

See how the new embedded Python feature enables you to write user-defined SQL functions and stored procedures in Python:

⏯ Embedding Python in SQL: Write Your Stored Procedures in Python

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

4 0
0 270

Following instructions on this page https://docs.intersystems.com/latest/csp/docbook/Doc.View.cls?KEY=D2IMP_... I am trying to create a Data Connector to be used as a base class for a cube with update support.
That page suggests that putting an SQL query inside XData block is not suitable for a Data Connector that supports updates.
Later it also suggests that to enable updates your SQL query must include

0 2
0 221

Hello everyone,

First, thanks in advance for your help :-)

I noticed that a query called directly in a method of a class of a business operation does not return the same result as if I apply the same query in the same Caché database from Squirrel !!!

I don't understand why ???

Here is the query :

0 4
0 231

Background: We have our own SQL map that predates InterSystems'. A program writes an XML file for each table map class as $system.OBJ.Export would. $system.OBJ.LoadDir loads the XML files into .cls files.

The reason is a long story, but we need to update parameter EXTENTSIZE (only) in existing classes. This does not seem to happen. As a test I used $system.OBJ.Export to make an XML file and edited EXTENTSIZE in the two places it appears in the XML:

0 4
0 429

Hi,

I'm doing a query in SQL and I need to sort my data by some non-repeated field.

Unfortunately, my data is grouped in a way that I cannot guarantee that any column will not have repeated data, so one solution would be to take the row number.

Also, the Cache is not accepting Row_Number () in my querry and I would like to know if there is another solution to return line numbers or some way to add this function to the Cache.

Best regards.

12 7
0 2.6K

Cache ODBC State S1000 Native Code 400 Illegal Value

This is my query:

select DateTijdSec from
GLPPatTcActie
where pnr = '27085070017' and LTestId->Makey='BLA' and VerzamelDatTijd < '2021-03-04-2021 09:04' and glpactieid->makey in ('TAV','TMA') order by DateTijdSec desc

Most likely there is a wrong date time in the table, how can i get the data?

0 8
0 877

PORTUGUESE

Olá a todos!

Estou com dificuldades de fazer meu select para retornar palavras que contém acentos.

Na minha tabela TESTE, por exemplo, tenho palavras no campo NOME como Fábio e Fabio, porém se eu coloco a instrução:

SELECT * FROM TESTE
WHERE nome LIKE 'FÁBIO'

a instrução só traz FÁBIO.

Como eu faço para trazer todas as palavras: FÁBIO, FABIO, FABÍO, FABIÓ, FÁBÍÓ, FÂBIO, etc...

Conto com sua ajuda!

0 9
0 1.8K

Hi,

Im wondering if its possible to pass a SQL query through to the Power BI IRIS Connector? I can't currently see an option - and have tried through advance editor to use M Query syntax to attempt this with no luck.

Has anyone else in the community had any luck or know if this is supported?

Thanks.

0 2
0 300

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 162

Hi! I have a complex SQL select query which I execute via %ResultSet. It also includes a UNION. The select statement within the union should only be done when an external parameter is set, and I'm not sure of way of doing that within SQL, other than something like

SELECT FOO FROM BAR

WHERE (some conditions)

UNION

SELECT FOO2 FROM BAR2

WHERE :doUnion = 1 AND (some conditions)

...this works, but I don't think it's particularly optimal as I believe it will still execute the second SELECT regardless of :doUnion. It's also sort of hard to read...

1 12
0 426