SQL | InterSystems Developer Community

Question

James Woo · Jan 17, 2020

How to find special characters in SQL field?

I like to use something like this:

select * from MyTable where MyFieldName like ‘%[^a-zA-Z0-9 !”%#$&”()*+,-./:;<=>?@]%’

to find field that contains special characters that are only allowed.

#SQL #Caché

0 4

0 61K

Question

Laura Cavanaugh · Jul 17, 2017

Need variable number of query parameters in dynamic query

I have a query string that I am creating programmatically, based on some user inputs. The user might search on 5 fields, or 8 fields, or no fields.

In my sql statment, some of these fields require parameters in the %Execute statement.

For example:

if user picks lastname, sql = "select * from person where lastname = ?"

if user also picks age, sql = "select * from person where lastname=? and age > ?"

I then have these lines of code to create my result set:

set statement = %SQL.Statement

statement.%Prepare

#SQL #Caché

0 28

0 22.6K

Question

Jo Ellen Laansma · May 13, 2020

SQL Server - How to write column with empty string instead of NULL

I have a class that writes to an external SQL Server database. When the field is empty, it does not write the column and the column has the value NULL. In some cases, I want the column to be the value of the empty string instead of NULL.

#JDBC #ObjectScript #SQL #Caché #HealthShare

0 5

0 16K

Question

Paul Riker · Nov 16, 2017

SQL Convert yyyymmdd string to mm/dd/yyyy

Any suggestions on the most efficient way to convert yyyymmdd string to mm/dd/yyyy?

#SQL #Caché

0 7

0 13.5K

Question

Ramesh Ramachandran · Sep 23, 2020

ODBC Invalid cursor state Error

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

#Business Operation #SQL #Ensemble

0 2

0 9.5K

Question

Scott Beeson · Feb 16, 2016

Concatenate columns in results with GROUP BY?

Here is my original query:

SELECT EventType, InitiatedAt, COUNT(*) as cnt
FROM HS_IHE_ATNA_Repository.Aggregation
WHERE EventType = 'LOGIN'
AND LocalDateTime > '2016-02-16 11:00:00'
GROUP BY EventType, InitiatedAt

This gives me data like this:

#Code Snippet #Databases #SQL #Caché

0 3

0 7K

Question

Hao Ma · Feb 24, 2020

%Startswith or LIKE?

Could anyone please explain why Caché has a %STARTSWITH function while it supports ANSI SQL "LIKE"?

thank you very much.

#Performance #SQL #Caché #InterSystems IRIS

1 26

0 5.3K

Question

Scott Beeson · Feb 12, 2016

Help with SQL error

Error:

[SQLCODE: <-400>:<Fatal error occurred>]
[Cache Error: <<SUBSCRIPT>%0AmEdun+4^%sqlcq.HSREGISTRY.cls966.1 ^||%sql.temp(1,"")>]
[Location: <ServerLoop - Query Fetch>]
[%msg: <Unexpected error occurred: <SUBSCRIPT>%0AmEdun+4^%sqlcq.HSREGISTRY.cls966.1 ^||%sql.temp(1,"")>]

Here is an example of a query that gives the error:

SELECT COUNT(DISTINCT Criteria) as Relevance FROM HS_IHE_ATNA_Repository.Aggregation
WHERE EventType = 'CROSS GATEWAY QUERY'

#SQL #Caché

0 10

0 4.7K

Question

Neeraj Mehta · Sep 15, 2016

ERROR - SQL Error '[Cache ODBC][State : S1000][Native Code 29]

I have been using the query below and it was working fine but now it's giving a SQL error. There were no changes made that could cause this to stop working. There is no Field 'APPLICATIONID' in the table.

QueueSQL=select distinct (convert(char(5),SkillsetID)+'='+Skillset) from iagentbySkillsetStat where ApplicationID > 10000
QueueMappingSQL=SELECT DISTINCT (convert(char(5),SkillsetID)+'='+Skillset), SkillsetID FROM iagentbySkillsetStat iagentbySkillsetStat WHERE (iagentbySkillsetStat.ApplicationID>10000)

DB- Intersystems Cache

#Object Data Model #SQL #Caché

0 6

0 4.2K

Question

Scott Roth · Aug 8, 2018

Connecting to MS SQL Database using JDBC and Service Account

We have a new requirement being push down by our Data Security to no longer use Local SQL Accounts to access our Databases. So they asked me to create a Service Account that is on the Domain for our connections to each database.

I tried just changing my JDBC connection to using this Service Account and Password but I am not having any luck trying to connect to the database.

" Connection failed.
Login failed for user 'osumc\CPD.Intr.Service'. ClientConnectionId:ade97239-c1c8-4ed1-8230-d274edb2e731 "

#Databases #JDBC #Security #SQL #Ensemble

1 4

0 4K

Question

Andrei Luiz Nenevê · Feb 24, 2017

Is there any way of paginate SQL Query in Caché?

Hi,

My doubt is about the SQL Query in Caché:

I don't want to take all results from the table and orgainze them manually, for example: I have a table with 50 records, but I only want to select 10 records, being from tenth until the twentieth and this without knowing their IDs.

So, how can I do this, without losing performance, there is any way?

#Caché #ObjectScript #SQL

1 8

1 3.3K

Question

Vivek Nayak · Sep 6, 2019

How to execute multiple query or script in sql management studio?

I want to insert multiple records in the table using below queries, but it gives an error.

#Management Portal #SQL #Caché #InterSystems IRIS

0 3

0 3.3K

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()

#Object Data Model #SQL #Caché

2 30

3 3.2K

Question

Anderson Negreli · Mar 25, 2021

How to get the row number in an SQL query?

Hi,

I'm doing a query in SQL and I need to sort my data by some non-repeated field.

Unfortunately, my data is grouped in a way that I cannot guarantee that any column will not have repeated data, so one solution would be to take the row number.

Also, the Cache is not accepting Row_Number () in my querry and I would like to know if there is another solution to return line numbers or some way to add this function to the Cache.

Best regards.

#SQL #Caché

12 7

0 3.2K

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

Thembelani Mlalazi · Mar 12, 2019

String to date

I am trying to convert a string to date but can not get it to work I have function that I would like to take in a date string and covert it to date object

here is the ezample so far can not get it to work any help appreciated

set p="12/03/2019"
 
w $System.SQL.TODATE(p,"YYYY-MM-DD")
 
<ILLEGAL VALUE>todate+32^%qarfunc

if I try this still get the wrong value returned

set p="12/03/2019" 
w $ZDATE(p,3)
1841-01-12

#Databases #SQL #Caché

0 6

0 3.1K

Question

reach.gr G · Aug 4, 2021

How to find the Primary & Foreign Keys & How to access tables with String as Parameter

Trackcare 2021.2

Please may I know how to access SQL parameter with string as datatype with NO reference to SQL Table and SQL Column.

Hi Experts,

I have these two clarifications to make:

#Databases #Object Data Model #SQL #InterSystems IRIS for Health

0 6

0 3K

Question

Arcady Goldmint... · Oct 3, 2017

Querying a list property with SQL

I have a class that has a list property, which contains a list of other objects, and I want to join against it in SQL.

Class Foo Extends %Persistent { Property MyBars As list Of Bar; }

#Object Data Model #SQL #Caché

0 7

0 2.8K

Question

ANDERSON FARIAS · Apr 11, 2017

How use REGEX (Regular Expression) IN SQL statement?

Hi guys.

I need validate a field string in a table. It needs have a specific mask

I want use regex + CASE expression

There is any function to this purpose in SQL?

#SQL #Caché

0 5

0 2.7K

Question

Roger Beeman · Apr 20, 2017

Enhancing SQL Query for Date Range in WHERE clause

Hi All -

I was wondering if the below query could be enhanced to automatically look back 24 hours from the current datetime the query is run. As is now I, of course, have to update the date range in the WHERE clause manually.

The query is just getting all MessageBodyClassNames, counting them and then doing an AVG on TimeCreated and TimeProcessed. Nothing too complex.

SELECT MessageBodyClassName, count(ID) as Count_Of_Messages, avg(datediff(ss, TimeCreated, TimeProcessed)) as avg_processing_time_in_seconds

FROM %PARALLEL Ens.MessageHeader

#SQL #Caché

0 1

1 2.6K

Question

Laura Cavanaugh · Feb 15, 2017

Property as Simple list of string values

I have a property, Emails, that needs to contain a list of comma-delimited values, or "". Is there a benefit to making this property a list of %String, a Collection[list] (or however you do it), an array, or anything other than a simple %String type?

#Object Data Model #SQL #Caché

0 3

0 2.6K

Question

Mike Kadow · Jul 18, 2016

How do I execute a "Basic Class Query?"

I have a NewBie Question.

I have been playing around with "Basic CLass Queries."

I have defined a very simple "Basic Class Query." (see below)

However, I cannot find in the I/S documentation how to execute this query.

Any help is appreciated.

#Caché #SQL

0 8

0 2.5K

Question

CM Wang · Jul 3, 2017

How to escape the single quote in the single quoted string

I have a stored procedure like below:

Call USER.SP('select * from Sample.Person where SSN='aaaaa'','0','S')

How could I escape the single quotes for aaaaa? I try double single quotes but in vain.

Thanks you.

#Object Data Model #SQL #Caché

0 11

0 2.4K

Question

CJ H · Nov 10, 2017

Best way to run a SQL script and output the result to a file

I have a Cache-compatible sql script file and each query is separate by white space.

How could I ask Cache to execute all the queries in this file and dump the result to a specified file?

Thanks.

#SQL #Caché

0 2

0 2.4K

Question

Jonathan Harris · Mar 3, 2022

Command line script to get data from database table using sql

I want to have a script that can run from the usual unix, linux, or aix command line. It has to be able to get into an irissession and use set statements to get data using sql. It seems like I'm in a catch-22. When I use the irissession SERVER command at the command line, I can't run a script. When I put the irissession SERVER command in a script, it won't run anything in the script after that.

My goal for this script is to get this information and put it into a file which I can then parse.

#SQL #InterSystems IRIS

0 10

0 2.3K

Question

Evgeny Shvarov · Jul 8, 2016

Top from the group SQL query in Caché

Hi!
There is interesting question in Stackoverflow.
This query works for MS SQL:

SELECT * 
FROM 
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY date_updated DESC) AS rn
    FROM client_address
) a
WHERE a.rn = 1

There is one answer there, which solves the task:

#SQL #Caché

2 5

0 2.3K

Question

Augie Turano · Oct 9, 2020

How to Create a Cache SQL Temp Table?

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'

#SQL #Caché

0 11

1 2.3K

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=&#39;Aggregation&#39;

How can I do this in Cache SQL?

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

#Beginner #Caché #SQL

0 11

1 2.3K

Question

Fábio Campos · Mar 18, 2021

How to make a select return accented characters

PORTUGUESE

Olá a todos!

Estou com dificuldades de fazer meu select para retornar palavras que contém acentos.

Na minha tabela TESTE, por exemplo, tenho palavras no campo NOME como Fábio e Fabio, porém se eu coloco a instrução:

SELECT * FROM TESTE
WHERE nome LIKE 'FÁBIO'

a instrução só traz FÁBIO.

Como eu faço para trazer todas as palavras: FÁBIO, FABIO, FABÍO, FABIÓ, FÁBÍÓ, FÂBIO, etc...

Conto com sua ajuda!

#SQL #Caché

0 9

0 2.2K

Question

Mike Kadow · Jun 15, 2017

Ways and methods of Accessing Relationships with SQL

I am experimenting with Relationships, both Parent to Child and One to Many.

I have done some SQL look-ups and have searched through the documentation, however not in a lot detail, but wonder if there are more and better ways to access both sides of Relationships through SQL?

Thank you in advance for any help provided.

#Caché #SQL

1 25

0 2.1K