SQL | InterSystems Developer Community

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

#SQL #Caché

0 2

0 886

Question

Lucas Fernandes · Nov 27, 2019

Recursion with SQL

Does Caché have any features similar to PostgreSQL 'WITH RECURSIVE'? Something that allows recursion in SQL.

#SQL #Caché

0 3

0 597

Question

Neerav Verma · Nov 28, 2019

Getting Parameters cleanly from Property Definition

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

#SQL #Caché

0 11

0 407

Question

Abbad Minhas · Nov 11, 2019

Creating a view that uses customized class query stored procedure results in compile error

I have the following class:

Class P.RA.SII.F
{

#Error Handling #SQL #Caché

0 3

0 236

Question

Oliver Wilms · Nov 28, 2019

InterSystems Cache DSN

Hello,

#Business Operation #ODBC #SQL #Ensemble

0 3

0 843

Announcement

Tony Coffman · Nov 20, 2019

Ad Hoc Reporting for InterSystems Platforms now on Open Exchange

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:

#Analytics #MDX #SQL #Caché #Ensemble #InterSystems IRIS #InterSystems IRIS for Health #InterSystems IRIS BI (DeepSee) #Open Exchange #Partner Directory

Open Exchange app

2 0

1 505

Question

Matías Peña · Nov 15, 2019

Connect Oracle with InterSystems

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

#SQL #InterSystems IRIS

0 5

0 1.1K

Question

Yana Rchks · Oct 30, 2019

SQL-query: Select item from table field of list of objects of another table

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.

#SQL #Caché

0 12

0 3.2K

Question

Tom Philippi · Jan 8, 2018

Processing multiple SQL-rows at the same time using SQL Inbound adapter

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.

#SQL #Ensemble

0 3

0 776

Question

Ruslan K · Oct 7, 2017

Class parameter in class query

Is it possible to use a class parameter in class query?

Example:

Class SomeClass
{

Parameter SOMENAME = "Name1";

Query SomeQuery() As %SQLQuery
{
SELECT ID || ..#SOMENAME  || Name FROM Table
}

}

#Object Data Model #SQL #Caché

1 3

0 595

Question

Hieu Dien Nguyen · Nov 29, 2018

Special Char % in SQL Query

Hello everyone,

I try excuting a SQL Query like this SELECT ID, CompanyName FROM Company WHERE CompanyName LIKE 'condition'

But if I want to search CompanyName have special char %, ex: '100% Co' , 'Group of Mr.%', 'The %TaxProp'...

How can I take % to query, with

#Caché #SQL

0 5

0 936

Question

Daniel Lee · Nov 8, 2019

Class compile error 5475

I have a task that has been working successfully for some time now but when I attempt to recompiled I get this error:

#SQL #Studio #Caché

0 7

0 982

Announcement

Anastasia Dyubaylo · Nov 8, 2019

New Video: Dynamic Data with JSON

Hi Community,

New video, recorded by @Stefan Wittmann, is already on InterSystems Developers YouTube:

🗣 Dynamic Data with JSON

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

#Data Model #JSON #Multi-model #SQL #InterSystems IRIS

1 0

1 469

Question

Yunier Gonzalez · Oct 31, 2019

Working with Data: System in production

Greetings community. I would like to know how to migrate a BD in production to a local environment. When I have a system in production (BD Sql Server) what we do is mount a local copy to do the analysis with the data and not occupy resources of the system in production. My question is: How do you do it with Intersystems technology?

#Backup #Big Data #Databases #SQL #Caché #InterSystems IRIS #InterSystems IRIS for Health

0 2

0 319

Question

Sergey Madiev · Oct 30, 2019

SQL query index using

Hi guys,
I ran into a strange (for me) situation, when I run same query but change the WHERE clause the plan is different and is not connected to the additional condition.
Query that doesn't use the necessary index:

#SQL #Caché

0 9

0 628

Question

Alistair Purse · Oct 28, 2019

A simple running total in Cache SQL

Can anyone tell me how to add a simple running total in Cache SQL.

I'm selecting a quantity in the first column and want a running total in the second:

9 , 9

#SQL #Caché

1 9

0 514

Question

Luk De Voght · Oct 24, 2019

SQL query to list all items in a production and their status

Hello,

Is there a way to list all items in a production (incl their status/color) by using a sql query? This is for monitoring purposes.

Thank you in advance!

Regards,

Luk

#SQL #Ensemble

0 5

0 773

Question

Ahncel Lalu · Oct 25, 2019

SQL QUERY STORED PROCEDURE "IN" NOT WORKING

When calling the below stored procedure using the management portal (Run Query) no data is being retrieve, but if it is directly executed management portal data will show.

"IN" does not work changing it to "=", "like" the stored procedure will work. Does any one know how to fix this ?

Pass values to code is 'AB','TS','SK','GM'

#.NET #SQL #Caché

0 1

0 605

Question

Yone Moreno · Oct 13, 2019

Using class queries to select all rows and return an array: unexpected behaviour

We have in DB two books, first is loaned because it has the Friend ID, and the other is in the shelf.

I execute a class query to get all loaned books:

Query BooksLoaned() As %SQLQuery
{
SELECT *
FROM Library.Book
WHERE Friend IS NOT NULL
}

Which I have tested throught the portal:

#Code Snippet #SQL #Caché #Ensemble

0 6

0 426

Question

Yang Jiao · Oct 9, 2019

Anyone familiar with class mapping of Antrim LAB/FIN systems?

Hi, we are a veterinary lab and we use both the LAB and FIN systems of Antrim. Now we are looking to expose the data in a SQL/Object compatible way so we were wondering if same / similar things had been done by other community members already? If so, could you please share your approach / experience / gotchas with us and we are all ears. I can be reached at yang.jiao@antechmail.com . Thank you!

#Mapping #SQL #Caché

0 1

0 315

Job

Tamsin Holland · Oct 8, 2019

Career Opportunity - LifeLabs (Burnaby, BC, Canada)

At LifeLabs, we are focused on our vision of building a healthier Canada! We are the largest community diagnostics laboratory in Canada with over 350 collection centers, 21 laboratories and service over 19 million patients each year. As the Software developer, you will be concerned with all facets of the software development process. You will be responsible for the design of application modules, maintain and deploy software applications to meet user and business needs.

#.NET #HL7 #SQL #SVN #Caché #Job Opportunity

0 0

0 454

Question

Nicki Vallentgoed · Oct 7, 2019

&SQL and ORDER BY ID DESC

I know &SQL returns only one result but is

&SQL(SELECT ID FROM Cinema.Film ORDER BY ID DESC)

and

&SQL(SELECT TOP 1 ID FROM Cinema.Film ORDER BY ID DESC)

the same in terms of processing required?

#SQL #Caché

0 7

0 387

Announcement

Anastasia Dyubaylo · Oct 4, 2019

New Video: Optimizing Your SQL Queries

Hi Community!

Please welcome the new video on InterSystems Developers YouTube Channel:

Optimizing Your SQL Queries

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

#Global Summit 2018 #SQL #Video #InterSystems IRIS

0 0

0 299

Announcement

Anastasia Dyubaylo · Oct 2, 2019

New Video: SQL and Object Access

Hi Everyone!

New video, recorded by @Stefan Wittmann, is already on InterSystems Developers YouTube:

SQL and Object Access

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

#API #Java #ObjectScript #SQL #Video #InterSystems IRIS

4 0

2 281

Question

just fig · Sep 27, 2019

How can i go through the full result once?

When i use ##class(%SQL.Statement).%New() -> .%Execute() and then .%Next() to go through the result set, i could only go through part of the result. And then, i have to call

#SQL #Caché

0 2

0 297

Question

Scott Roth · Mar 9, 2018

Incrementing Session ID through BPL

I have several BPL's that act like SQL Server Integration Services (SSIS) where they are transferring data. When I do this they are all being written under 1 session id, is it possible to make the BPL create multiple session ID's as it is cycling through a Snapshot, so it is easier to read in the trace?

Thanks

Scott Roth

#Business Process (BPL) #Object Data Model #SQL #Ensemble

0 6

0 946

Question

reinhard lebensorger · Sep 5, 2019

CACHE SQL: Is the order of the WHERE conditions relevant

hi,

i have two where-clauses:

a) ... WHERE company=1 and product=7

b) ... WHERE product=7 and company=1

with other words the position of the where fields are swapped.

now the question is:

bring the where clauses a) and b) the same performance(queryplan) or do i have to write it in a special order???

in my point of view, there is a parser/optimizer who cares about this, so i don't have to care about.

#SQL #Caché

2 3

0 419