New post

#SQL

17

SQL is a standard language for storing, manipulating and retrieving data in relational databases.

Since now i have been working from external connection but i want to work with the SQL utility of Management Portal

i dont know how to do with several instructions like in other editors like this example

update Prod.Articulos set Alto = 1646, Ancho = 16, Fondo =  80 where CodigoNum = '100' and Empresa = 'CO'
update Prod.Articulos set Alto = 1646, Ancho = 16, Fondo =  400 where CodigoNum = '101' and Empresa = 'CO'
update Prod.Articulos set Alto = 1646, Ancho = 16, Fondo =  400 where CodigoNum = '102' and Empresa = 'CO'

it returns

0 11
0 631

Hi All,

I have a general query in regards to developers experience on extracting data from cache databases and the most efficient way to do so. I work with a number of clients who have applications with cache databases and require the data off the host system and onto data warehouse platforms for research and analysis. Often they require the data in source state which means the extracts are often simply a table scan of the entire database table without any aggregation or manipulation.

1 2
0 1.5K

This tells me that there is no timezone offset on this table/field:

Select TOP 1 GETDATE() as Now, TimeCreated FROM ens.messageheader ORDER BY TimeCreated DESC;
Now TimeCreated
------------------- -------------------
2016-10-18 16:16:49 2016-10-18 16:16:31
0 13
0 770

I experience this constantly with Cache SQL. Especially when querying the ATNA log.

SELECT TOP 400000 * FROM HS_IHE_ATNA_Repository.Aggregation ORDER BY ID DESC

That took 12 seconds. I then upped the number to 500,000 and it took 185 seconds.

Shouldn't the execution time scale proportionately?

If I run the 500,00 query again it takes 2.4 seconds.

0 4
0 411

I have to connect to an external database and I'd like to use the JDBC SQL Gateway.

I followed this tutorial [1] to create a JDBC SQL Gateway and after configured it works as expected, but I don't know

how use this connection into source code. Maybe these other tutorial is related [2] and [3].

[1] http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

1 5
0 800

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 4K

Hi,

I'm trying to perform a SELECT with parameters using the EnsLib.SQL.OutboundAdapter. The SELECT returns results but seems to discard the parameters I try to send. I have tried two methods.

First:

Set par(1) = "20160630"
Set par(1,"SqlType") = 12
Set sql = "SELECT Cod, Ing, score FROM [bbdd].[dbo].[vw_Test] WHERE MyParam >= '?'"
Set tSC = ..Adapter.ExecuteQueryParmArray(.QueryResultSet,sql,.par)

Second:

0 4
0 1.2K

Hi -

I'm trying to rewind a cursor back to the first row after looping part of the way through the implied result set, but I'm not finding a way to make this happen, is there some such iterator variable or directive that I can leverage to accomplish this?

I could code around it by pulling identifiers and/or values into a local array, and then hand code up an iterator over my local results copy, but this feels like a "redesigning of a wheel" approach, and I thought I would check before I start down this path.

Thanks

0 2
0 427

This is a bit weird. I'm trying to use `DATEDIFF()` to calculate the time since the last message on an interface. For some reason, when no messages are received, the number steadily decreases. This is the opposite of what should happen. Here is my current query and a few sequential result sets:

SELECT getDate() as now, max(TimeCreated) as latest, DATEDIFF (s,getdate(), max(TimeCreated)) as difference
FROM EnsLib_HL7.Message
0 4
0 605

Hello,

In my project, I am calling a store proc on third party database. I am using SQL Gateway and ODBC. Everything is working fine until the store proc changed. In store proc they have changed the return alias name for a column. In Ensemble Snapshot I am not seeing the modified alias name until I restarted the whole Healthshare instance.

Here is my question, is there any other way? We do not want to take the instance down if it happens after we go live.

Thanks

Raghu

0 1
0 527

What is the recommended approach to handle the following scenario: Large number of HL7 messages are serviced via Ensemble. Those messages are then transformed and routed to an Operation which calls a web service which does a number of different activities. If will do address normalization, location geo coding, MPI lookup and resolution, and insert into a SQL Server database to stage for an analytics product. Unfortunately all of this work takes time and the first thing that happens in the web service is a lock taken out on the Patien in SQL Server.

1 2
0 636