Question
· Oct 3, 2018
CreateProperty with curl

Hello,

I have a question about creating properties with curl.

I already did create properties in Java with the following command.

<DO db.%CreateProperty("TotalSteps","%Integer","$.TotalSteps")>

It created the property TotalSteps with the type %Integer and the data path $.TotalSteps (since the header of my data source is also TotalSteps).

Now I would like to create the same property in curl with the following command

0 2
0 503

I am trying to use %INLIST in SQL query using a cursor and the query fails to return results. It appears that the variable I use against %INLIST returns an empty string. All the examples I have seen use result sets and build the query as a string. Is it possible to use %INLIST in a cursor-based query? Below is a a snippet of the code I am using:

Property mylist as %String (MAXLEN="") [InitialExpression = "route1, route2, route3"];

set routeList = $LISTBUILD(mylist)

&sql(DECLARE MyReport CURSOR FOR

SELECT

ProcessStartDate,

0 3
0 703

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')

0 2
0 523

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.

0 1
0 988
Question
· Feb 28, 2020
How to get a Numbers View

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!

0 3
0 335
Question
· Jun 22, 2020
Amending timestamp

Hi, I have a timestamp of 201906192359 with a HL7 and I need to add a minute to it to get 201906200000. Is there an easy way within Healthshare to do this?

It seems easy enough within SQL but I cannot get the SQL to work within Healthshare, this is what I have for SQL which does the job in SQL Server.

SELECT REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16),DATEADD(mi,1,STUFF(STUFF(@test,11,0,':'),9,0,' ')),120),'-',''),' ',''),':','');

0 3
0 338

Hi:

I have an sql outbound adapter. Sometimes we have trouble connecting to the database.

The timeout in develpment is set to 15 seconds in live it is 150 seconds as it is an always connected Buisness operation.

I thought adding E=S or/and X=S would suspend the message. Why does it not?

Is the only way around then adding something to the buisness operation itself/ creating a customised SQL buisness operation rather than EnsLib.SQL.OutboundAdapter?

Thanks

Mark

0 5
0 513
Question
· May 27, 2021
JDBC counts don't match

I'm using a JDBC driver to connect PGSQL -> Cache. I'm noticing when I run SELECT queries and COUNT(*) command against the same table, I get different result sets. I'm pretty new to Cache in general - so I'm trying to understand why these would be different.

Examples (TransID and InvNum should occur in every "row"):

SELECT COUNT(*) FROM ACCT.Services = 1,090,324 WHERE ACCT.Dept = 483

SELECT TransID FROM ACCT.Services = 1,085,776 WHERE ACCT.Dept = 483

SELECT InvNum FROM ACCT.Services = 586,023 WHERE ACCT.Dept = 483

0 8
0 280

Hi,

I'm very new to InterSystems Health Connect so may be doing something silly...

I'm trying to link to an Oracle DB view using the Link Table Wizard in the Management Portal. The wizard finds the view, but when I select it and click the 'next' button I get an error on the 'Select Columns' screen: 'ERROR #5534: Columns error' (and no columns show in the wizard). I've tried on several views and tables in the same DB but keep hitting the same error.

0 2
0 468

Newbie here...

I am looking to use fhir to gather patients in our end point that have a certain diagnosis and or certain insurance.

Is this what fhir can be used for?

In the past I have done the normal SQL query/extract to CSV file. Looking to use fhir for this for future.

Thanks in advance for your time

Dean-O

0 1
1 458

I have the need to query an external database and write the result set/snapshot to an internal %Persistent [ DdlAllowed ] table that I built. I have built inbound SQL Services before and write them externally to replace SSIS jobs, but how would querying a database via a Service and writing the data to an internal table work?

Can I just take the inbound query structure and write it to the class file of the internal table in a DTL? If so, what would be the Target? Or does this need to be done within a BPL as a Code block?

0 2
0 63

I have a NewBie Question.

I have been playing around with "Basic CLass Queries."

I have defined a very simple "Basic Class Query." (see below)

However, I cannot find in the I/S documentation how to execute this query.

Any help is appreciated.

0 8
0 2.6K

I have been using the query below and it was working fine but now it's giving a SQL error. There were no changes made that could cause this to stop working. There is no Field 'APPLICATIONID' in the table.

QueueSQL=select distinct (convert(char(5),SkillsetID)+'='+Skillset) from iagentbySkillsetStat where ApplicationID > 10000
QueueMappingSQL=SELECT DISTINCT (convert(char(5),SkillsetID)+'='+Skillset), SkillsetID FROM iagentbySkillsetStat iagentbySkillsetStat WHERE (iagentbySkillsetStat.ApplicationID>10000)

DB- Intersystems Cache

0 6
0 4.3K

I am trying to return the maximum of the value of 2 fields: LastViewed and LastDownloaded AS a local variable -LastAccessed for each row, using a SQL query . These values are stored as $ H format. Is there an existing SQL command that compares two column values ? I could not find one, so I tried using a $Select statement . I got an error that said A term expected beginning with either of: identifier, constant, aggregate, $$,(,:,+....)

Here is the SQL Query I am trying to run:-

0 5
0 956