#SQL

I have created store procedure using management portal.

how to alter or modify SP using SQL in management portal.

Please refer below screen.

SQLCONNECT1 = tableUsers

SQLCONNECT2=tableLogEntries

select * from tableusers u, tablelogentries e where e.userid = u.userid

I keep getting the error References to an SQL Connection must constitute a whole subquery

Hello community! I have to work with queries using all kinds of methods like embedded sql and class queries. But my favorite is dynamic sql, simply because of how easy it is to manipulate them at runtime. The downside to writing a lot of these is the maintenance of the code and interacting with the output in a meaningful way.

The select query here "select Settings from Ens_Config.Item" in the SQL Query of management portal returns this:

ValidationHost 7ActOnTransformErrorHost1 ReplyCodeActionsHostE=D AlertOnErrorHost1 9 AlertGroupsHost$LabAlertGroup,1-CriticalAlertGroup BusinessPartnerHost

How do I isolate just one of the settings e.g. ReplyCodeActions or LocalInterface

For example, i want to search for all entries that have a value for ReplyCodeActions

I realize i can open the production in studio and search for those setting values.

Hi guys!

As you know there are two (at least) ways to get the stored value of the property of InterSystems IRIS class if you know the ID of an instance (or a record).

1. Get it by as a property of an instance with "Object access":

ClassMethod GetPropertyForID(stId As %Integer) As %String

{

set obj=..%OpenId(stId)

return obj.StringData

}

2. Get it as a value of a column of the record with "SQL access":

I tryinfg to alter a column definition data type with a user that seems that doesnt have priviledges, but the user has this priviliedge.

Documentation:

Privileges and Locking

Hello all,

I have a Recordset object which contains data from a table "XYZ".

Currently i use this object to extract data using %Get(COL1,COL2...) in a loop and than pass it to a function which inserts the data into another dynamically created Table "ABC" for each record. This takes a lot of time when 100's of records.

Is there a way i can directly copy a RecordSet to a dynamic table without looping through..?

Something like copy Recordset (COL1,COL2..)--> "ABC"

Thanks,

Jimmy

End users sure love Excel tables. So, how can you deliver the second to the first? Read this post to find out.

Hi All,

I have two tables LB_TestSet and LB_Transfer

LB_Transfer has a list if TestSet row IDs, so i am doing the following join

SELECT 
LBTS_RowID,
,LBTS_CollectedDate
FROM SQLUser.LB_TestSet 
JOIN SQLUser.LB_Transfer ON ($LISTBUILD(LBTS_RowID) %INLIST LBTR_TestSetList)
WHERE LBTS_CollectedDate BETWEEN '2019-01-01' AND  '2019-05-10'

Without the date filter the query returns data, but if i add the date filter no data is returned

I also tried using %INTERNAL, %EXTERNAL TO_DATE('2009-01-01','YYYY-MM-DD')

Have some free text fields in your application that you wish you could search efficiently? Tried using some methods before but found out that they just cannot match the performance needs of your customers? Do I have one weird trick that will solve all your problems? Don’t you already know!? All I do is bring great solutions to your performance pitfalls!

As usual, if you want the TL;DR (too long; didn’t read) version, skip to the end. Just know you are hurting my feelings.

Hi!

I was trying to create a query that can be exposed as a stored procedure (function actually) that would return a resultset with a random number of columns.

Unfortunately, it seems that unless I specify the ROWSPEC annotation on the Query method, I won't get any columns exposed. I was hoping to implement QueryNameGetInfo method and specify the names and number of columns I would be returning dynamically. But it seems that GetInfo information is simply ignored.

Here is my code:

Class ICT.Experiments.A Extends %Persistent
{
Property Name As %String;
Property Collection As list Of ICT.Experiments.B;
Property Collection2 As array Of ICT.Experiments.C;
}

Class ICT.Experiments.B Extends %Persistent
{
Property Name As %String;
}

Class ICT.Experiments.C Extends %Persistent
{
Property Name As %String;
}

I have the classes above and I can select columns from the array collection by using two joins as follows:

I have two classes:

Referencing this post:

https://community.intersystems.com/post/producing-json-sql

I'm not sure how to actually interact with the result set I get from doing something like this. I want to return something like:

[{"field1":1, "field2":2}, {"field1":2, "field2":10}]

I'm finding it very difficult to get it in this format, since %Print appends a newline onto the end of the {} object it prints.

Here's the closest I've gotten: