.png)
InterSystems IRIS 2020.1 brings a broad set of improved and new capabilities to help build important applications. In addition to the many significant performance improvements accrued through 2019.1 and 2020.1, we are introducing one of our biggest changes in recent SQL history: the Universal Query Cache. This article provides more context on its impact to SQL-based applications at a technical level.
SQL
Hi ,
I have a code written in cache sql and trying to understand it, below is the code , can anyone help me understand what does that mean
ex - !! dosage_unit !!
SELECT (CASE WHEN (order_description IS NULL OR (order_description='')) THEN '' ELSE (order_description) END) !! (CASE WHEN (dosage IS NULL OR dosage_unit IS NULL OR (dosage='') OR (dosage_unit='')) THEN '' ELSE (', ' !! dosage !! ' ' !! dosage_unit !!
Thank You in advance.
Last reply 19 March 2020
Is there a way to query the database structure? In SSMS there are queries for finding tables with a column with a certain name (using LIKE). And there is the redgate tool SQL Search. But I'm not sure how to go about looking for columns that have say a value of 'PATID' and returning all tables that match. Does anyone know?
Last reply 15 March 2020
Temporary tables are tables available for a current process only (and destroyed when process ends).
What are you approaches to creating temporary tables?
Last reply 13 March 2020
Hi All,
I need to build a search using a lot of filters, what's the best way to implement my sql requests.
1 sql request per property, or only one "big" sql request ?
what's the disadvantage of using lot of indexes ? Thank you
KR,
Amine
Last reply 12 March 2020
Where can i get the latest odbc providers from SQL Server 2012/2016?
I see some posts on line to an ftp site out there but I am unsuccessful at getting access to it. I have a version loaded on my server but get errors when querying cache'.
"[Cache ODBC][State : 22005][Native Code 22005]"
Any help would be appreciated.
the one we have might be version 2014.01.05851 64 bit (would that make sense?)
Last reply 4 March 2020
A good writer is supposed to draw you in with the title and bury the answer somewhere in the article. I suppose this makes me a bad writer – don’t think less of me, my whole feeling of self-worth comes from the opinions of internet strangers!
Last reply 3 March 2020
Let's say I start with a date range of '1-5-2019' to '5-25-2019' that occurs on one row. I'd like to ultimately have this show as 5 rows in Crystal Reports as shown below
Result
1-5-2019 1-31-2019
2-1-2019 2-28-2019
3-1-2019 3-31-2019
4-1-2019 4-30-2019
5-1-2019 5-25-2019
I found a result that worked in T-SQL, but I'm not sure how to translate it to Cache SQL. The T-SQL code is
Last reply 3 March 2020
In T-SQL, I can create a recursive CTE to create a subquery or a view that will hold a whole lot of numbers. Is there a way to do this in Cache SQL? I'd like the numbers from 0 to 10000 if possible.
Thanks!
Last reply 3 March 2020
Could anyone please explain why Caché has a %STARTSWITH function while it supports ANSI SQL "LIKE"?
thank you very much.
Last reply 3 March 2020
Hi,
Do you have any experience in server side pagination with IRIS using Angular on the client side?
Any idea on a typical SQL request on the server side?
Thanks!
Blaise
Last reply 3 March 2020
Hi,
I created a BS with a SQL Inbound Adapter.
Query = "SELECT rowid, fname ,lname,datesent
FROM labs
WHERE datesent IS NOT NULL"
DeleteQuery = " update labs set datesent = Getdate() where rowid = ?"
KeyFieldName = "rowid"
i am getting the following errors.
"NOT assuming network disconnect error based on: Gateway failed: DropStatement."
"Skipping previously errored row '235'"
Last reply 2 March 2020
Analytics, Analyzer, Dashboards, Roles, Security, SQL, Tips & Tricks, InterSystems IRIS, IRIS Analytics (DeepSee), Worldwide Response Center (WRC)
This error is sometimes seen while viewing a listing in InterSystems IRIS Business Intelligence:
ERROR #5540: SQLCODE: -99 Message: User <USERNAME> is not privileged for the operation (4)
As the error suggests, this is due to a permission error. To figure out which permissions are missing/needed, we can take a look at the SQL query that is generated. We will use a query from SAMPLES as an example.
I have a code block in a BPL. I have the below SQL and code. All variables have been declared and set. When I run the SQL, if there are no values returned, I get 100 for the value of SQLCODE when I perform the FETCH, which is correct. If values are returned, I get a -400 error when I perform the first FETCH. I've investigated, but cannot find the reason for a -400 error. Hoping someone out here understands this and knows what's wrong.
Last reply 28 February 2020
In Episode 4 of Data Points, we welcome @Benjamin De Boe to the pod to discuss some of the things you can do to optimize your SQL queries in InterSystems IRIS. We've all heard — either from ourselves or from others — the "this runs too slowly" complaint. I thought Benjamin did a great job walking through many of the things within IRIS you can look at with your queries to see what can be improved.
Hi Developers,
Enjoy watching the new video on InterSystems Developers YouTube:
⏯ SQL Performance: Need for Speed
Hi all,
i‘m traying to run a sql LIKE Query on an attribute datatype LONGVARCHAR.
I also try allready with the SUBSTRING(Attribute,1,...), when i run the query, the resultat is empty without errors.
The Query:
select text from table
where text like ‘%abc%‘
many thx for help
BR
Darko
Last reply 20 February 2020
HI I am using below code to retrieve the last inserted ID of the table. It works when we do not set any custom column as IDKey. If we define any column as
IDKey below code does not return any IDKey. I know when i am defining any column as IDKey its not auto generated , but whats the best way to get the ID column value
Last reply 19 February 2020
HI All,
It seems pretty straight forward in theory and I assumed it will be too, but may be I am missing some simple step in implementation.
Here is the scenario of our business process
Last reply 19 February 2020
I'm using Cache SQL and want the ability to choose a specific index.
I've boiled the problem down to one table and simplified the query down to
SELECT *
FROM Registration.PatResp
WHERE SchedApptNum=8450022
SchedApptNum is indexed, but instead of using that column, "Show Plan" indicates that it's looping through the entire Registration.PatResp table on Id (the primary key for the table).
I've done a tune-table with no change.
Last reply 18 February 2020
Hello Community,
Thank you all for your continued feedback and support of our ad hoc reporting platform, VDM. There's been some questions around setting up a non-ODBC connection for InterSystems platforms. We published a new YouTube video showing the steps necessary to connect to InterSystems Caché and InterSystem IRIS with BridgeWorks VDM.
How do you cancel a query that is executing in the Management Portal > System > SQL?
Does closing the browser cancel the query or does it continue to run in the background?
Is there any added benefit to using the Shell over the Portal? Perhaps there is a way to cancel/stop a query here?
$SYSTEM.SQL.Shell()
Thanks
Last reply 6 February 2020
Hello,
I have created my first table in Ensemble:
And I have added a row using the Production:
We would like to query it, and we would expect to get a row wih all the previous' image data.
I have tried, inside a code block in a Bussiness Process:
Last reply 5 February 2020
Hi,
we're planning some work on our SQL Query Plan functionality for a future release of InterSystems IRIS and are interested to hear how you're using them today, or what'd keep you from using them. Rather than try and fit everything in a rigid survey, I thought a simple thread on our beloved DC might also reveal some use patterns that we support or could do a better job on.
Last reply 4 February 2020
I am trying to get a list of all settings for all the config items of a given production using SQL . When I run the following sql as a dynamic query I am unable to access the setting names and values. Settings is a list of Ens.Config.Setting
Last reply 4 February 2020
A quick question regarding to SQL Query Plan:
Why these two requests have different plans, in particularly, why second request needs temp file? To me, temp file is a bad thing which should be avoided, right?
- select * from Test.Log where cdate = 1
- select * from Test.Log where cdate > 1
Plan for 1:
Read index map Test.Log.cdateIndex, using the given %SQLUPPER(cdate), and looping on ID.
For each row:
Read master map Test.Log.IDKEY, using the given idkey value.
Output the row.
Plan for 2:
Last reply 30 January 2020
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.
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.
Last reply 20 January 2020