That is helpful, thank you.

I just find it strange that it is quite non-standard compared to other compilers/interpreters

Hi,

There is an item on the InterSystems Ideas portal for this. You can go vote for it if you want to.

But currently, it is not a built-in feature.

Not currently that I am aware of.

There is the Installer Manifest you can use to automate a lot of this during deployment, but there is no export to create such a manifest.

Maybe add this as an Idea on InterSystems Ideas...

If you want to use the group by, then you should probably do the count where the group by is being done, and use distinct by as you had it.

select distinct by(serial,hq) hq, count(1)
from thetable
group by hq

If you want it per that grouping.

There are no filters in it, so it is going to do a full table scan and compare and calculate values for each row. Taking the amount of time it takes, it is actually fast.

Maybe look into Bitslice indexes. It might help but at a cost of performance on insert and update:
InterSystems Documentation

For a start:

select distinct by (VISIT_SERIAL_NO,HQ_ORG_CODE) VISIT_SERIAL_NO,HQ_ORG_CODE
can be changed to
select distinct VISIT_SERIAL_NO,HQ_ORG_CODE

It will do the same.

Secondly:
Will you please remove the %parallel and click on "Show Plan". Post that plan here. It will help to determine where the query is slow. It might be using the wrong index. There are many.

Lastly:
Have you tuned the table and checked the result after that?

Thank you for the clarification.

If rollback and commit is supported, then verifying the results is just the step of doing a select before committing in order to verify the update/insert was correct and as expected.

Nothing special to it or automated in any way.

This is great. Thank you.

I updated it like that last week. Apparently, I did not send it for approval, which I thought i did.

This is a great tool.

I am wondering if it will work for everyone. In the world of finance, you do not get SSH access to servers.
Most of the times the super-server port is also closed off for everything except the web gateway.

If the web version can be run on it, it is great - but in banking environment, not everyone is on the "containerised" buzz yet, so this will not be allowed.

Sure, I can probably install and configure the package and set up the web application.

Now there are two things left I want to raise:

  1. Multi-line SQL without having SSH access. Also do not have SCP or SFTP access.
    1. If this is present, and I have missed it, I apologise.
  2. Database transactions.
    1. I have a SQL shell I built a long time ago, which worked with db transactions.
    2. Doing DML, you may want to verify the results before committing it to the DB and have to option to rollback.
      1. This will be really great if the app can handle it.