SQL | InterSystems Developer Community

Ambiguous sort column error

SQL, Caché

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:

Alex Kogan 5 September 2019

Last answer 5 September 2019 Last comment 5 September 2019

0 0 3

answers

comments

views

rating

Filtering MDX results with SQL using %SQLRESTRICT

Analytics, Analyzer, Dashboards, MDX, Performance, SQL, Tips & Tricks, InterSystems IRIS, IRIS Analytics (DeepSee)

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.

Peter Steiwer 6 January 2020

+ 5 0 1

comments

views

+ 5

rating

[Ensemble] Req table with big data

SQL, Ensemble

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...

Guillaume Lepretre 4 December 2019

Last answer 4 December 2019

0 0 1

answers

comments

views

rating

EDI SQL error when changing SSMS servers

API, Debugging, ODBC, SQL, Ensemble, HealthShare

I am experiencing a problem with an EDI process that uses a SQL Batch Service to connect to our DEV environment. However, when we point the EDI service to our TEST server, it errors out.

I have checked every single property on our TEST and DEV servers as well as the properties on the associated tables. They are identical. Nothing has changed in the SQL either.

Warren Grob 10 October 2019

0 0 1

answers

comments

views

rating

MSSQL ODBC Connection Error (NativeError: [11001] )

ODBC, SQL, InterSystems IRIS

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.

Rodolfo Moreira 10 January 2020

Last answer 11 days ago

0 0 1

answers

comments

views

rating

sql statements - how to clear multiple sql statements and Frozen Flags

Code snippet, SQL, Caché

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

Kevin Furze 10 days ago

Last answer 10 days ago Last comment 10 days ago

0 0 1

answers

comments

views

rating

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

Business Operation, Business Process (BPL), Callout, ObjectScript, SQL, Ensemble

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..

Scott Roth 10 January 2020

Last answer 10 January 2020 Last comment 10 January 2020

0 0 2

answers

comments

views

rating

Uploading gif/image into CSP page

Beginner, CSP, CSS, HTML, SQL, Studio, HealthShare

Ok so I am way outside of my comfort zone, and had to build an application using CSP to give users the ability to access SQL configuration tables. These SQL Configuration tables will affect the data that is sent to the downstream system.

I saw in the examples where we were able to import GIF's/IMAGES into the CSP folders to use as a reference in our CSP pages. My question is how do you do that? If I try to import through studio, it tells me the file is invalid.

Just trying to make it a little more user friendly then blocks on a page.

Scott Roth 2 days ago

Last answer 2 days ago

0 0 2

answers

comments

views

rating

Oscillating Between Objects and Relational:The Impedance Mismatch

Caché, SQL

Introduction

Impedance mismatch is a term commonly used to describe the problem of an object-oriented (OO) application housing its data in legacy relational databases (RDBMS). C++ programmers have dealt with it for years, and it is now a familiar problem to Java and other OO programmers.

Community Manager 21 October 2015

+ 2 0 0

comments

views

+ 2

rating

Fighting Impedance Mismatch at the Database Level

Caché, SQL

Introduction

With the maturation and wide acceptance of Java, object-oriented programming has moved to the foreground of the application development landscape. Because of their rich data models and support for productivity-enhancing concepts such as encapsulation, inheritance, and polymorphism, object technologies like Java, C++, and COM, are favored by today's application developers.

Community Manager 21 October 2015

+ 1 0 0

comments

views

+ 1

rating

20X Faster SQL. How Does Caché Do That?

Caché, SQL

Customers who switch to Caché from relational databases report that their average performance is up to 20 time faster, running on the same hardware, with no changes to the application. What is it about Caché that lets applications run so fast?

Community Manager 21 October 2015

0 0 0

comments

views

rating

Caché SQL - Videos

Caché, SQL

Overview:

Programming, security and connectivity with Caché SQL is discussed in these videos.

Community Manager 1 October 2015

0 0 0

comments

views

rating

Understanding and Optimizing SQL Performance

Caché, SQL

This webcast provides an overview of all of the different components of optimizing SQL performance. We address some key concepts to utilize while developing a relational application.

Community Manager 2 October 2015

0 0 0

comments

views

rating

Part 3 - SQL Security

Caché, SQL, Security

How to set up SQL security including creating users and roles, creating views, and setting privileges.

Community Manager 2 October 2015

0 0 0

comments

views

rating

Part 4 - SQL Connectivity

Caché, SQL

This module will cover how to connect to a Caché SQL server via ODBC and JDBC.

Community Manager 2 October 2015

0 0 0

comments

views

rating

Part 2 - Programming with SQL

Caché, SQL

How to use SQL in your Caché Object Script Programs. This module covers Dynamic SQL and Embedded SQL.

Community Manager 2 October 2015

0 0 0

comments

views

rating

Part 1 - Introduction to SQL

Caché, SQL

The SQL basics and the first steps to using Caché SQL.

Community Manager 2 October 2015

0 0 0

comments

views

rating

Caché Objects and SQL

Caché, SQL

A look at Unified Data Architecture and how a class is projected as a table. Compare a class and a table and how different components are accessed via Objects vs. SQL.

Community Manager 2 October 2015

0 0 0

comments

views

rating

SQL Gateway

Caché, SQL

This screencast covers how to use SQL Gateway.

Community Manager 2 October 2015

0 0 0

comments

views

rating

Caché and SQL Performance and Debugging

Caché, SQL

An introduction into peformance tuning and debugging.

Community Manager 2 October 2015

0 0 0

comments

views

rating

Management Portal - Authorization: SQL

Caché, SQL, Security, Security›Authorization, System Administration, System Administration›Management Portal

In addition to resources, roles and users, SQL privileges can be assigned to users for the tables in a namespace. These privileges can be assigned to columns and rows in a table as well as their views and procedures.

Community Manager 2 October 2015

0 0 0

comments

views

rating