New post

#SQL

13

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

Running TuneTable accounts among other the parameter named Block Count. In documentation, we see that this is an approximate numbers of 2K-blocks in which SQL-maps are stored. Databases in recent Cache doesn't support 2K-physical blocks so SQL-blocks are not physical blocks as it seems. So two questions:

- what are these blocks?

- how knowledge about count of blocks can help in SQL optimization?

0 4
0 553
Question
· Mar 22, 2017
Query

Hi All, In Cache Table i have stored the data value as horlog format ,by query how to retrive the data when i give the data field as date format.

0 4
0 549

Hi there,

Does anyone have an example of the method used to run an SQL query and generate the view in a Mumps routine. I have several SQL queries created from a vendor that need to be converted and the corresponding view referenced. I cannot seem to find any examples in IDX routines or in the IRIS/Cache documentation. Any help is appreciated.

-Alicia

0 5
0 549
Question
· Aug 21, 2020
IRIS SQL Linked Table

Hi everyone

Anyone knows how can we add a column to an existing linked table in Cache to reflect the external SQL table?

I've tried different queries. It does not fail but does not add the column either...

0 4
0 548
Article
· May 25, 2017 2m read
The Interns are Coming!

The Data Platforms department here at InterSystems is gearing up for this year's crop of interns, and I for one am very excited to meet them all next week!

We've got folks from top technical colleges with diverse specialties from hard core engineers to pure computer scientists to mathematicians to business professionals. They come from countries around the world like Vietnam, China, and Finland and they all come with impressive backgrounds. We're sure they will do very well this summer.

6 0
0 546

I have a list of about 100 MPI IDs that I would like to run a report on. I want to list times that any data for these patients were accessed. Currently in "Managed Reports" we have a "Disclosure Report" which I think was a custom development effort, but it is per-patient.

I have a SQL query for the ATNA log but I'm not confident in its accuracy, so I thought I'd reach out and see how other Information Exchange's might get this data.

0 7
0 542

I have a class which defines a property as array of %String. Is it possible to index values of this property and use this property in SQL?

I have tried 'Index idx On prop(ELEMENTS)' and then a select from the generated collection table, but this is still orders of magnitude slower than queries to the containing class.

1 2
0 541

I have been following the online Zen Quickstart Tutorial using the lastest release documentation. In addition to playing around with the styling and making a few minor functionality tweaks, I wanted to add an additional column that shows a count of the number of phone numbers for that Contact (as shown in the image below).

0 4
0 539

I've asked a lot of questions leading up to this, so I wanted to share some of my progress.

The blue line represents the number of messages processed. The background color represents the average response time. You can see ticks for each hour (and bigger ticks for each day). Hovering over any point in the graph will show you the numbers for that period in time.

This is super useful for "at a glance" performance monitoring as well as establishing patterns in our utilization.

5 2
0 539

Hi Community!

New session recording from Global Summit 2017 is already on DC YouTube Channel:

SQL – Things You Should Know

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

1 2
0 538

Hey everyone,

I'm currently running into a very weird issue to where I am trying to connect with a 64 bit version of SQL Server Management Studio (SSMS) to a HealthShare instance. I have created a System DSN using the Drivers (image below) that were downloaded with the Client version of the install and I'm able to successfully connect using my credentials.

0 2
0 538

Hi -

I'm trying to rewind a cursor back to the first row after looping part of the way through the implied result set, but I'm not finding a way to make this happen, is there some such iterator variable or directive that I can leverage to accomplish this?

I could code around it by pulling identifiers and/or values into a local array, and then hand code up an iterator over my local results copy, but this feels like a "redesigning of a wheel" approach, and I thought I would check before I start down this path.

Thanks

0 2
0 535

Hi, folks!

When you deploy DeepSee solutions you often do not want grant a User %All Role to work with a particular Dashboard.

Consider a Dashboard 'Dash' with a few widgets where listings are being used.

If you manage a Role to get access to the Dash you need to grant access to %DB_DBNAME resource to have a database access, grant access to a Dashboard resource (if any) and ... grant SELECT accesses to all the tables involved in SQL queries being used in all the listings of widgets.

1 3
0 530

Hi All,

I have two tables LB_TestSet and LB_Transfer

LB_Transfer has a list if TestSet row IDs, so i am doing the following join

SELECT 
LBTS_RowID,
,LBTS_CollectedDate
FROM SQLUser.LB_TestSet 
JOIN SQLUser.LB_Transfer ON ($LISTBUILD(LBTS_RowID) %INLIST LBTR_TestSetList)
WHERE LBTS_CollectedDate BETWEEN '2019-01-01' AND  '2019-05-10'

Without the date filter the query returns data, but if i add the date filter no data is returned

I also tried using %INTERNAL, %EXTERNAL TO_DATE('2009-01-01','YYYY-MM-DD')

0 2
0 523

"SELECT %SYSTEM.SQL_TableExists('table name') "could work as expected under SQL shell,

but for "CALL %SYSTEM.SQL_TableExists('table name')" does not work (not any error reported, it just show nothing).

Is there any reason why CALL could not be applied to a stored procedure?

Thanks.

0 7
0 520

Hello,

searching messages in our Message Bank is quite slow, often runs into timeout.

I wanted to perform a tune table on Ens_Enterprise_MsgBank.MessageHeader because this apparently has not been done yet - the Tune Table utility shows no entries for selectivity, etc.

I tried

 w $SYSTEM.SQL.Stats.Table.GatherTableStats("""Ens_Enterprise_MsgBank"".MessageHeader")

and got this error message

1 6
1 519

Hi Community!

Please welcome a new video on InterSystems Developers YouTube Channel:

Getting Sharded with InterSystems IRIS

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

0 1
0 519
Article
· Mar 29, 2023 1m read
Named Parameter In SQL with Python

Quick Tips: Total Productive Maintenance

Named parameters can be achieved with SQLAlchemy :

from sqlalchemy import create_engine, text,types,engine

_engine = create_engine('iris+emb:///')

with _engine.connect() as conn:
    rs = conn.execute(text("select :some_private_name"), {"some_private_name": 1})
    print(rs.all())

or with native api

from sqlalchemy import create_engine, text,types,engine

# set URL for SQLAlchemy
url = engine.url.URL.create('iris', username='SuperUser', password='SYS', host='localhost', port=33782, database='FHIRSERVER')

_engine = create_engine(url)

with _engine.connect() as conn:
    rs = conn.execute(text("select :some_private_name"), {"some_private_name": 1})
    print(rs.all())

6 0
0 517