SQL

Syndicate content 33 
Question
Scott Beeson · Feb 12, 2016
Get columns dynamically?

In MSSQL I think you can do something like this:

select *
from HS_IHE_ATNA_Repository.COLUMNS
where TABLE_NAME='Aggregation'

 

How can I do this in Cache SQL?

[%msg: < Table 'HS_IHE_ATNA_REPOSITORY.COLUMNS' not found>]

00
0 11 1,577
Article
Robert Cemper · Nov 21, 2020 3m read
ObjectScript over ODBC
This is a coding example working on IRIS 2020.1 and on Caché 2018.1.3 
It will not be kept in sync with new versions      
It is also NOT serviced by InterSystems Support !   
Every now and then you may encounter a situation where for various reasons
ODBC is the only option to access a remote system. Which is sufficient as long as you need to examine or change tables.
But you can't directly execute some commands or change some Global.

 
Special thanks @Anna Golitsyna for inspiring me to publish this.

100
0 0 72

We use ExecureProcedure() to execute a stored procedure which returns a result set. But we see lot of "Invalid cursor state" errors when the result set is empty.  The connection to SQL server database is made through ODBC. 

ERROR #6022: Gateway failed: Fetch. + ERROR <Ens>ErrGeneral: SQLState: (24000) NativeError: [0] Message: [Microsoft][ODBC Driver 11 for SQL Server]Invalid cursor state 

Here is the code snippet from the business operation class which uses EnsLib.SQL.OutboundAdapter  

00
0 2 203

Hi,

InterSystems IRIS has long supported the obvious translation functions required to for converting to upper or lowercase to enforce case-insensitive string comparison (e.g. in ObjectScript with $zconvert) and sorting (e.g. with SQL collation functions, not to be confused with NLS collation). Customers in international contexts have at times used custom workarounds to also treat accent insensitivity or even more advanced normalization duct tape. We’re looking to address such use cases at the system and SQL level to increase convenience for this international audience, which is well represented on the Developer Community.

00
0 5 76
Question
Daniel Aguilar · Nov 4, 2020
Avoid $c(0) in globals

Good Morning,

I have this question for a long time. When I make an insert by SQL from an external application in the fields of type %String, if they are empty, it writes the character $c(0) in the global.

Is there a way so that if an Insert is received in a field of type% String with null value instead of the $ c (0) it leaves it empty?:

00
0 7 140
Article
Eduard Lebedyuk · Feb 5, 2016 11m read
Class Queries in InterSystems IRIS

Class Queries in InterSystems IRIS (and Cache, Ensemble, HealthShare) is a useful tool that separates SQL queries from Object Script code. Basically, it works like this: suppose that you want to use the same SQL query with different arguments in several different places.In this case you can avoid code duplication by declaring the query body as a class query and then calling this query by name. This approach is also convenient for custom queries, in which the task of obtaining the next row is defined by a developer. Sounds interesting? Then read on!

150
3 17 5,007

Hey, 

I have a value in epoch time in which I need convert in order to be able to lookup a table that has dates in YYYY-MM-DD HH:MM:SS format

I thought the below would work but it is not.

select COUNT ('Arrival Time') FROM dbo.table where 'Arrival Time' < DATEADD(ms, CONVERT(int,LEFT(1603173432000, 20)), '1970-01-01 00:00:00')

00
0 8 71

Presenter: Anton Umnikov
Task: Identify your slowest SQL queries and tune them for better performance
Approach: Use InterSystems’ query profiling and analysis tools. Discuss how system configuration can affect performance
 

This session will show you how you identify the weakest link in your application SQL and introduce you to the fine art of tuning those queries. To do this we will take a look at InterSystems query profiling and analysis tools, as well as how system configuration can impact SQL performance.

Problem: Obscurity on how our SQL engine works

 

Content related to this session, including slides, video and additional learning content can be found here.

00
0 2 196

In MS SQL Server i can do this:

SELECT 
Category, CrewNumber, MedicalCertificationDate, Seat, SeatbeltUsed, Sex, ShoulderHarnessUsed, ToxicologyTestPerformed, childsub
INTO #tempfemale
FROM Aviation.Crew
WHERE Sex = 'F'

00
0 8 230

I noticed when creating Record Map's within Ensemble that it is creating a Persistent cache table as it translates the file and puts it into the Record Map data structure.

I was wondering if there was a way to add a column to the cache table that is Hidden from the record map, but is a calculated date value on when that record was inserted? 

00
0 4 81
Question
MARK PONGONIS · Jul 17, 2018
Cache Dynamic SQL Pagination

Would like to know if there is an alternative or better way to paginate through a dataset using dynamic SQL than what I am using below. The problem is that as the potential pool of data gets larger, this code slows down to the point of not being useable. In analyzing each line of code below, it appears the slow down is related to the initial rset.%Next() iteration. Is there anything available which does not require a subquery/%VID such as a simple LIMIT/OFFSET?

My code is similar to :

s sql=##class(%SQL.Statement).%New()

10
1 21 1,175

I am trying to create a Procedure in Caché, but this message is showing:

<UNDEFINED>frmit+118^%qaqpsq *mt("v",1)

This is the procedure:

CREATE PROCEDURE testebi.sp_cargainicial()
BEGIN
    INSERT INTO testebi.Fato_Atendimentos (
    PK_OsProcedimento )
    SELECT ID
     from dado.TblOsProcedimento    ;    

00
0 2 94
Question
David Foard · Sep 16, 2020
More than 1 cursor in a class

I have a class that has 2 different cursors for different queries, audit1 and audit2. which are in 2 different methods. The first query runs fine, but the second one generates a 102 error.

 

Is there an issue with having more than 1 cursor in a class?

Has anyone seen this before?

00
0 2 76

Hi guys!!

In the system that I work, I came across an iterator pattern that uses the %Resultset library without performing the close after executing the query. Does anyone know how to say what are the impacts of not performing such a procedure?

If you have any model of iterator pattern made in caché to recommend as a good example, I will be grateful hehe  :D

10
0 5 130
Question
Joao Palma · 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...

00
0 4 100

Date range queries going too slow for you?  SQL Performance got you down?  I have one weird trick that might just help you out! (SQL Developers hate this!)*

If you have a class that records timestamps when the data is added, then that data will be in sequence with your IDKEY values - that is, TimeStamp< TimeStampif and only if ID1 < IDfor all IDs and TimeStamp values in table - then you can use this knowledge to increase performance for queries against TimeStamp ranges.  Consider the following table:

170
1 8 14,926

Keywords:  IRIS, IntegratedML, Machine Learning, Covid-19, Kaggle 

Purpose

Recently I noticed a Kaggle dataset  for the prediction of whether a Covid-19 patient will be admitted to ICU.  It is a spreadsheet of 1925 encounter records of 231 columns of vital signs and observations, with the last column of "ICU" being 1 for Yes or 0 for No. The task is to predict whether a patient will be admitted to ICU based on known data.

20
1 1 239