New post

#SQL

14

SQL is a standard language for storing, manipulating and retrieving data in relational databases.

All time

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 5
0 260

cache 2017.2 windows 10

Is there any way to change the sql Shell interface to output a <TAB> between the fields instead of outputting one or more spaces between columns

I needed to run an sql ( that took a while to run) and then cut and paste the generated output into excel, trouble was the paste into excel was not very good for columns when there's spaces used between the columns

kev

0 3
0 261

In Cache 2018, we were using a macro in a query that looked like this:

select $$GetExtraSQL^GetExtra('B',bddtl.odnumb,bddtl.odsnum,bddtl.oddsc1) as "Description", * from sqluser.bddtl

We could save that query as a view, and there was no problem with it.

In IRIS, if we put that query into SQL in the management portal, it still works, but if we save that query as a view, when we try to run a query on that view, we get a big error message:

0 1
0 262

Hey Developers,

Enjoy watching the new video on InterSystems Developers YouTube:

⏯ Projecting Data into InterSystems IRIS with Foreign Tables

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

2 0
0 262

Hi Developers,

Enjoy watching the new video on InterSystems Developers YouTube:

⏯ Importing Relational Data Using a DDL Script

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

2 0
0 262

Dear all,

In our application made in Caché object script, when we deploy some classes modified we have the option of calling "Purge queries" feature of Caché.

We only were doing this call when a %Persistent class definition (table) was modified, as we assume that no other changes affect the cached queries and we don't want to purge them by default, as the first run of a query becomes slow.

We had a case where no table definition was changed but a purge queries was the solution.

0 3
0 258

Hi,

We need a web-based SQL tool to connect to Ensemble/Cache, that will offer more functionality than what the Management Portal offers.
It needs to be web based, so that we can host it on a machine that has connectivity to the server via the superserver port. We (the devs and support) only have browser access to the environment via Remote Desktops, hence the requirement.

Has anyone gotten OmniDB to connect to Ensemble/Cache? If so, can you please advise on how to configure it?

Any other suggestions for such a tool are also welcome.

0 1
0 258

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]

4 0
2 259

Hi Developers!

Here're the technology bonuses for the InterSystems Vector Search, GenAI, and ML contest 2024 that will give you extra points in the voting:

  • Vector Search usage - 5
  • IntegratedML usage - 3
  • Embedded Python - 3
  • LLM AI or LangChain usage: Chat GPT, Bard, and others - 3
  • Questionnaire - 2
  • Docker container usage - 2
  • ZPM Package deployment - 2
  • Online Demo - 2
  • Implement InterSystems Community Idea - 4
  • Find a bug in Vector Search, or Integrated ML, or Embedded Python - 2
  • First Article on Developer Community - 2
  • Second Article On DC - 1
  • First Time Contribution - 3
  • Video on YouTube - 3
  • Suggest a new idea - 1

See the details below.<--break->

3 0
0 255

Hi all,

We are doing a SQL server upgrade to SQL server v.15 from a legacy server which had v.10 . I have our Cache server as a linked server int order to pull data from our system. When building out the ROWSPEC, I have my fields typed as %String with various lengths with one field having a max length of 15000. The SQL server is trying to interpret this as a text data type instead of varchar(n). Text is a depracated data type since SQL server 2008. The error below is produced when trying to query from this view

0 1
0 254
Question
· Aug 31, 2023
SELECT command within CSP

The program below works perfectly when I call it directly from the Terminal, however when I call it from within a CSP it does not work (It does not do the SELECT).

In the USER namespace, the program works both in the Terminal and on the CSP , but in another namespace it only works when called directly in the Terminal.

0 4
0 249
Question
· Jun 1, 2022
Cache Query for HL7 NACK

I would like to capture any NACK's that is sent back to the Operation. The Operation is already setup to "Save Replies/IndexNotOK's", but I would like to see if we can query Cache and pull those NACK's into an extract.

Is this possible?

Thanks

Scott

0 4
0 249
Question
· Dec 4, 2020
SQL Query returns x00

Hey Intersystems Community-Member,

Following Issue: When I update a SQL table like (update OnlineV3Admin.ParameterApp set popuptext = '' where ID = '1') and then read it for my Json WebService there is a /"x00" in my Json.

What is the best option to disable that? I need here a empty String and not /x00.

Any ideas?

Best regards,

Florian Hansmann

0 1
0 252

I have a SQL query that I want to run against MS SQL from cache ensemble using the SQL outbound adapter. If I run this query direct from MS SQL Studio it take about 7 seconds MAX and returns about half a million rows. The row only contains one column it is a number all same size and if I run this query in the production in ensemble it takes for ever the production finishes without getting the response back. The same query run in cache outside the ensemble environment writing results to a file it returns results taking about 3minutes to complete.

0 1
0 251

Hello everyone!
I need to have a ResultSet of type % SQL.Statement show its contents when it is trafficked in a message property by Business Process.
I tried to use the % XML.DataSet type that inherits properties of type % XML.Adaptor, but did not work.
Is there any other way to traffic as an object, other than within a Stream?

Note: I can not traffic Streams and I will not be able to use Correlate in this case.

0 3
0 249

Hi,

When for any particular reason I need to update a record and don't want to pull the triggers, the keyword %NOTRIGGER can be used. But I've been trying to do the same when I change the row using the object approach, but I can't find it. Anyone knows if it's possible to avoid pulling triggers when working with objects?

Sadly, the utility "DISABLE^%NOTRIGGER" doesn't seem to exist ;-)

Thank you,
David

1 2
0 249

When using "IDENTITY" as my primary key, I can select the last inserted ID with

SELECT LAST_IDENTITY() FROM %TSQL_sys.snf;

Actually this is how Hibernate + Iris' Driver acquires the inserted ID when mapping with

@GeneratedValue(strategy = GenerationType.IDENTITY)

Now, considering that I am using the type "SERIAL" as my primary key instead, how can I get the last inserted ID?

Note that with "SERIAL" I can forcefully insert any value for this ID, from which Iris will continue generating values...

0 1
0 250

Hi all,
I am using ExecDirect() method to execute my SQL query which is something like "SELECT * from Account where AccountNumber = ? "
But when I am providing a accountnumber that don't exists it doesn't return anything as aspected. while I am trying to check resultset.%SQLCODE in this case it is giving me 0 not 100. What's wrong with my SQL query and Also I there any other Property through which I check for if my resultset is null or it has some values.

0 2
0 247