#SQL

Hi all,

I have an operation which uses the SQL outbound adapter to query a data source set up using a system DSN on the server which uses the Intersystems ODBC35 driver (this is a cache db hosted elsewhere).

Whenever Ensemble runs the following query:

SELECT lnkdid,
c_fastsearch_code,
c_drugfull
FROM JAC.drug_basic
WHERE c_drugfull LIKE 'Para%'

No data is returned, however running the same query via SQL Server Management Studio, all expected rows are returned.

Also if I run the following via Ensemble:

I have several stored procedures that when I execute them they will only return a single snapshot. In my BP I have been setting this to a Snapshot variable then looping using a WHILE through the snapshot variable just to get that single value.

Since it is only a single row, is there an easier way where I don't have to do a WHILE loop to pull the values out of that row? Can I call First Row or something like that to get me just the row into the Snapshot variable?

Hi

Is this a bug? We came across it on an older version of Cache but I've also just tried it in version "Cache for Windows (x86-64) 2017.2.2 (Build 865U)" and got the same result.

Create a Class like so:

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":

Hi all,

i‘m traying to run a sql LIKE Query on an attribute datatype LONGVARCHAR.

I also try allready with the SUBSTRING(Attribute,1,...), when i run the query, the resultat is empty without errors.

The Query:

select text from table

where text like ‘%abc%‘

many thx for help

BR

Darko

Hi,

I'm trying to ultimately create a function that I can use with HL7 messages that calls a stored procedure held on a SQL Server.

Initially I tried extending the function class to use the sql inbound adapter and/or EnsLib.SQL.Common, but this wouldn't work from the class method for the function.

Hi,

The question is about queries on the System->SQL page. I have a class with a few columns on a global and the test output looks as expected, below. This works as expected as well returning a single row: select * from Utils.RoutineAuditReport where counter=4. However, other columns give an odd error like " Field 'AG' not found in the applicable tables^ SELECT * FROM Utils . RoutineAuditReport WHERE UserR = "AG". As you can see below, both the UserR column and the AG entry in it do exist. What could be the problem?

Thanks in advance,
Anna

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

Hi guys,

My client has a requirement to add a column of random numbers to the query result.

I wrote a function as below:

Class Utils.SqlUtility Extends %RegisteredObject
{

ClassMethod GetSomeNumber(intInput As %Integer) As %Integer [ SqlName = GetNumber, SqlProc ]
{
Return $R(intInput)
}

}

But in the returned sql result, every row share the same value, as below,

SELECT Utils.GetNumber('456'),
ID, Citizenship, DOB, FirstName, Gender, IDNumber, LastName, PatientNumber, PhoneNumber
FROM CDR.Patient

Hi!

I'd like to know if there are any issues if an index is inserted into a table without running the %BuildIndices() method.

It's important to note that data inserted before the index is not important for retrieval, so it's not a problem data inserted before the index don't show up in queries.

The reason why I'm asking this is that I'd like to avoid index reconstruction on big tables which I need to inser such index.

I'm using Cache 2018.1.

Thanks,

José

Hi,

DBA has asked me to configure a working Business Operation using a SQL.OutboundAdapter (connecting to external MSSQL server) to run as TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

I would like help with an example of how this is done.

Thanks

Hi,

Do you know how to export SQL errors to a text file from a terminal session in Caché?

Thanks

Hello Fellow Cache Developers:

Has anyone ever created an index on values of a list property? If so, would you be willing to share an example?

Also, feel free to offer input and suggestions regarding use of indexes on List values.

Here is my database scenario:

Parent Class:

PropertyA - %String

PropertyB - %Integer

Child Class:

PropertyC - %Integer

PropertyD - list of %Integer

Data illustration:

I have the following query which tells me how many documents were retrieved for each customer, but it only works for the "on-demand" customers: