SQL

Syndicate content 32 

Hi All,

We have few queries which are simple selects . For simplicity let's say there is a query that joins two tables and gets few columns and both tables have no indexes.

Select Tab1.Field1, Tab2.Field2
From Table1 Tab1
Join Table2 Tab2
On Tab2.FK = Tab1.PK

When we do query plan for this it shows approx 6 million,  however if we make a simple adjustment to the query

0   0 2
121

views

0

rating

cache 2017.2.1 (Build 801_3)

when I look into the "sql statements" ( sql page of the management portal ) I have lots of old statements with a lot of them frozen. many of them are %sqlcq routines (I believe these are the SLQ's I run when testing/running random SQL queries in the sql page. )

I can clearly see where I can click on an individual query and untick an individual query to unfreeze it. or delete it. but thats a painful slow process. I can't see anywhere to delete/change multiple queries

Last reply 15 January 2020
0   0 1
90

views

0

rating

Hello, I need to use IRIS to connect to an MSSQL base.
It has to be done via ODBC, I can't use JDBC at this time by client option.

I am trying to use Microsoft Driver
libmsodbcsql-13.1.so.9.2

But I can't, my attempts result in:
Connection failed.
SQLState: () NativeError: [11001] Message:

I have done all DSN configuration, and my configuration is listed in SQL Gateway Connections. I know it's working, because when I run a test with isql I have the information that connects to the bank.

0   0 1
119

views

0

rating

I am working on a BPL to take data from a MS SQL database and create an HL7 Materials Message for our EMR.  I have done this plenty of times in the past however I am running into an error.

"Remote Gateway Error: JDBC Gateway SP execute(0) error 0: Access to the remote server is denied because no login-mapping exists."

What is confusing is that this BPL doesn't differ from any of my other BPLs in connecting to MS SQL Server. I know I am missing something..

Last reply 10 January 2020
0   0 2
120

views

0

rating

I have a case where I have an external table that lives out in MS SQL. Using Ensemble Security functions has anyone created a csp or cls page that acts like a portal to allow users to Update a multidimensional table without knowing SQL? I could clone the EnsPortal.LookupSettings but that was made for LUT which are only 2 columns.

 

 

I know its a long shot but trying to see if there was anything off the shelf that I can use to help meet the requirements of this project.

Last reply 7 January 2020
+ 1   0 2
98

views

+ 1

rating

What is %SQLRESTRICT

%SQLRESTRICT is a special %FILTER clause for use in MDX queries in InterSystems IRIS Business Intelligence. Since this function begins with %, it means this is a special MDX extension created by InterSystems. It allows users to insert an SQL statement that will be used to restrict the returned records in the MDX Result Set. This SQL statement must return a set of Source Record IDs to limit the results by. Please see the documentation for more information.

Why is this useful?

This is useful because there are often times users want to restrict the results in their MDX Result Set based on information that is not in their cubes. It may be the case that this information may not make sense to be in the cube. Other times this can be useful when there is a large set of values you want to restrict. As mentioned before, this is not a standard MDX function, it was created by InterSystems to handle cases were queries were not performing well or cases that were not easily solved by existing functions.

+ 5   1 1
0

replies

215

views

+ 5

rating

Is there a way to get a good performing index on a date field? I have tried various date property indexes and the query plan is always in a pretty high range. Below are query plan result values I have observed:

   StartDate > '2019-12-01'  --cost = 699168
   StartDate = '2019-12-21'  --cost 70666
   StartDate between '2019-12-21' and '2019-21-28' --cost = 492058

 

The query plans above were for type %TimeStamp.

 

 


 

Last reply 2 January 2020
0   0 4
165

views

0

rating

HI,

I am planning to build a WebApp that will have tons of data to display in tables, ability to add comments on that table rows and may be some few more small features as we move on.

1. It has to be Secure

2. It has to be fast

3. It has to be Non CSP / Non Zen

4. Will have Cache DB to pull records from but should be flexible to do that from any odbc resource

5. Version 2018 Ensemble

My first step would have been to investigate different options for technologies that would make it work and best suited with Ensemble 2018

Last reply 23 December 2019
0   1 5
288

views

0

rating

I configured a SQL inbound adapter to monitor my local Cache database.

But I got "ERROR <Ens>ErrOutConnectFailed: ODBC Connect failed for 'SAMPLES' /
'' with error ERROR #6022: Gateway failed: DSN/User Connect."

Same error if the DSN setting is blank or set to my namespace.

Question is whether SQL inbound adapter is for external database server ONLY?
 

Last reply 16 December 2019
0   0 3
123

views

0

rating

Hello,

I've got a table with many attributes and data. There is 10 000 000 registrations approximatly.

I need to make a research on this table with filters, paging,  and order.

 

You can see an example of my  SQL request : 

SELECT * FROM ( SELECT TOP ALL  * FROM ANCV_Data.Titre WHERE etatTitre = 'Emis' ORDER BY numRemise desc) v WHERE %vid BETWEEN 1 AND 25

Cause there are many data on my table, my SQL req is  too long if I don't have index on my attributes BUT there are many associations possible...

0   0 1
67

views

0

rating

BridgeWorks is pleased to announce a VDM, v9.1.0.1.  This release includes the following updates:

Updates

  • Historical Linking is now based off connection profile name
  • Saved Formatting is now based off connection profile name
  • Tables and Fields column headers no longer hide based on connection type

Bug Fixes

+ 1   1 1
0

replies

95

views

+ 1

rating

Hello Community,

I would like to work in SQL developer with the tables from Caché.

Is it possible with JDBC tool?

What is the whole process in order to work in SQL developer?  I have the access information to Caché, but i can only choose in SQL Developer software only Oracle or MySQL database type, therefore i think, that have to install any  other tool.

 

Thank you in advance for your help!

 

br,

bálint


 

0   0 0
214

views

0

rating

HI,

I have made a query with class definitions and all their properties and put them in a view.

All is good besides Parameters is showing junk characters. Is there a way to do it cleanly besides getting into the code??

 

SELECT 
CC.ID As CompiledClass, 
CC.SqlSchemaName,
CC.SqlTableName,
CP.Name As PropertyName, 
CP.SqlFieldName, 
CP.Type,
PD.Parameters

FROM %Dictionary.CompiledProperty CP
JOIN %Dictionary.CompiledClass CC
ON CP.Parent = CC.ID

Last reply 29 November 2019
0   0 2
129

views

0

rating

Hi I am experienced with Ensemble but working in my first DeepSee project, so forgive me if this seems elementary. 

My question is, how do I clear  the data out? Every time I build I am adding more and more data. But I would like to revert my cube to being 0 so I can reload it and start over? Set my row count back to 0?

I am unable to remove/purge through the management portal

-I've tried to kill my cube Do ##class(%DeepSee.Utils).%KillCube("MyCube.Cube")  and then Rebuild it. 

 

Thanks In advance

Last reply 23 November 2019
0   0 1
93

views

0

rating

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!

Last reply 22 November 2019
+ 15   1 11
4,738

views

+ 15

rating

Hello InterSystems Community,

We're excited to announce that we've completed our first Open Exchange submission for InterSystems platforms.

BridgeWorks VDM is an ad hoc reporting and graphical SQL query builder application that was designed for any user who needs access to their SQL projections in InterSystems Caché, InterSystems IRIS, InterSystems IRIS for Health databases as well as access to InterSystems DeepSee and InterSystems IRIS BI Cubes with minimal SQL scripting experience.  

VDM features:

+ 2   1 2
0

replies

150

views

+ 2

rating

Hello everyone,
It is my first post in the community. Very recently I started working using IRIS, creating services with Cache.
Today they gave me a new task and I'm really lost.

How can I use an oracle database to create services using iris?

Throughout the day I was looking for a guide to use a remote Oracle database. but unfortunately I only managed the connection in SQL Gateway

Last reply 15 November 2019
0   0 3
208

views

0

rating

I am trying to make architecture for my project. And for it, it needs to make list property in which there is list of objects of another table, the data must be unique, therefore I make sql-query to check objects of another table if it is used or not, but I can't make sql-query to check elements in list in property with elements of another table. Can you help me? In property ID of object is used.

Last reply 13 November 2019
0   0 3
661

views

0

rating

I have a database which needs to be synced to another system and considering to use the SQL Inbound adapter to 'watch' the database. The other system, however, has a bulk API so I'd prefer to send multiple SQL rows in a single message to that system. The SQL Inbound adapter seems to trigger a single call (to a process or business operation) per SQL-row.

Last reply 14 November 2019
0   0 2
338

views

0

rating

An exciting position has arisen for an Application Support Analyst 3rd Line, in an established support team. The role will suit candidates looking to work in the software industry who want to provide good customer service, enjoy finding solutions for problems and are enthusiastic about software and technology.  http://codas.com

+ 2   0 1
0

replies

175

views

+ 2

rating

An exciting position has arisen for a Junior Application Support Analyst in an established support team. The role will suit candidates looking to enter, or are at the start of their career in, the software industry who want to provide good customer service, enjoy finding solutions for problems and are enthusiastic about software and technology. http://codas.com

+ 2   0 1
0

replies

154

views

+ 2

rating