Performance is number one for us. With properly defined and tuned tables, I've never seen a database run its queries faster than IRIS.

Also just a lot of native capabilities. It's pretty rare that we have to reach outside of what comes in the box. And now with embedded Python, when we do need to add something, we have a lot of options.

Aside from Python's technical benefits, there's also a personnel consideration. More and more younger developers are learning Python, and according to the PYPL index Python passed Java in popularity among learners in 2018 and is far beyond Java today. That means having Python as an option opens up your hiring pool more than any other language to the next generation of developers.

Yes, you can do that, but I have another recommendation.

You generally want to be careful with what you do in %SYS, and you might not want a user to have permission to access that stuff all the time. You could create a new security role that gives the right permissions to access whatever you're accessing, then assign it in that method, run the code that needs it, and remove it. So let's say your new security role is called MyRole:

set $ROLES = MyRole
set oldns = $NAMESPACE
new $NAMESPACE
set $NAMESPACE = "%SYS"
// Do your stuff here.
new $NAMESPACE
set $NAMESPACE = oldns
new $ROLES

The $ROLES special variable is used to manage roles that are added and removed programatically during the execution of code, but does not affect roles assigned to the user in the management portal.

If you don't have any other criteria you can use in your where clause, that will happen. Based on the data you've shown us, you'd need either ProvId or ProviderType to get it down to 1 row.

If you just want the first row returned and don't really care which one it is, you could do

SELECT TOP 1 IdentityId 
FROM PhysTable 
WHERE ProviderName = 'DOE, JOE' AND Type = 'NPI' 
ORDER BY IdentityId

But that's only if you always want the lower number, and I doubt that's the case, right?

Do you mean you want to know how to get just that column?

SELECT IdentityId
FROM PhysTable
WHERE ProviderName = 'DOE, JOE' AND type = 'NPI' AND ProvID = '8252'

Or if this is in SQL embedded in ObjectScript:

&sql(SELECT IdentityId INTO :IdentityId FROM PhysTable WHERE ProviderName = 'DOE, JOE' AND type = 'NPI' AND ProvId = '8252')

That will get the Identity ID and put it into the variable IdentityId for you to use in further code.

Are you trying to get all of the classes that a class extends? You can do that like this:

	set classname = $CLASSNAME($THIS)
	&sql(select primarysuper into :super from %dictionary.compiledclass where id=:classname)
	w super,!

If you do that, super is a ~ separated list of the class and everything that it inherits from, which is probably actually more information than you need.

Glad you got an answer to your main question. To answer your other question about how to check a status, the Execute and Prepare methods of the %ResultSet class return a %Status. Where you have do Rs.Prepare(SQL) and do Rs.Execute(), you could use:

set sc = Rs.Prepare(SQL)
if $$$ISERR(sc) {$$$ThrowStatus(sc)}
set sc = Rs.Execute()
if $$$ISERR(sc) {$$$ThrowStatus(sc)}

Then if there was an error doing either of those things, it would get passed off to your catch block. In your case, there would've been an error indicating your permissions issue.

No, it doesn't run as soon as the previous task ends. It runs at the next scheduled time. So in my example above, if your task runs at 12:00:00 and doesn't finish until 12:01:30, the next scheduled time is 12:02:00, so that's when it'll run next. I'm not sure how far back in versions this goes, but you might be able to change that behavior so it's more like what you're expecting.

A task expires if it can't run when it was scheduled. This could be because your Ensemble instance was down when the task started, or it could be because a previous instance of the task was still running when it was supposed to start again. For example, if you have a task set to run every minute but it takes the task 90 seconds to complete, then when it run at 12:00, it will be supposed to start again at 12:01, but since it will still be running, the 12:01 task will expire and the task will run again at 12:02.