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é

0 8
0 290
Question
· Mar 18, 2022
EnsLib.HL7.Message Querying

We are trying to track down the source of Orphaned messages and noticed that we are unable to query EnsLib.HL7.Message with any kind of WHERE or ORDER BY clauses in our SQL statement.

I know EnsLib.HL7.Message is a system table, but is there a way we can add additional Indexes to the table to make the query run better/faster without affecting the system?

0 4
0 489
Question
· Mar 15, 2022
How to use LOAD DATA

How do I use the LOAD DATA command as described here.

I have the following LOAD DATA statement:

LOAD DATA FROM FILE 'E://Temp/Values.txt'

into PARIS.UAGU_MNDOOUT1 (AHSU_ID, AHSU_UAG_ID)

Where do I run it? It doesn't work in the SQL Gateway, in the terminal, in an embedded SQL command.

What am I missing?

0 2
0 363

I'm new to cache, come from an oracle and sql server background. In oracle and sql server I could write basically a stored procedure like script and pass it in as text to the command to execute.

Example:

Below would be the _sqltext

DECLARE @Id INT;

select id into @Id from something;

if @Id = 9

BEGIN

do something

END

The _sqltext would work in sql server, what would be the equivalent for cache for it work?

I get a generic error when I try it with cache

0 2
0 228

I want to have a script that can run from the usual unix, linux, or aix command line. It has to be able to get into an irissession and use set statements to get data using sql. It seems like I'm in a catch-22. When I use the irissession SERVER command at the command line, I can't run a script. When I put the irissession SERVER command in a script, it won't run anything in the script after that.

My goal for this script is to get this information and put it into a file which I can then parse.

0 10
0 2K
Question
· Aug 20, 2021
SQL proxy to HL7 query/response

Use case: small in-house hospital systems that query patient demographics via SQL. The new PAS being implemented in the near future will only support query/response via HL7. SQL access is available but intended for reporting so up to 24 hours behind.

Probably the easiest option is to set up a database in Ensemble and keep it up to date via standard ADT feed. This is going to have quite a large footprint and has a risk of getting out of sync.

1 2
0 338

I have a SQL query that I want to run against MS SQL from cache ensemble using the SQL outbound adapter. If I run this query direct from MS SQL Studio it take about 7 seconds MAX and returns about half a million rows. The row only contains one column it is a number all same size and if I run this query in the production in ensemble it takes for ever the production finishes without getting the response back. The same query run in cache outside the ensemble environment writing results to a file it returns results taking about 3minutes to complete.

0 1
0 249

I am experiencing a problem with an EDI process that uses a SQL Batch Service to connect to our DEV environment. However, when we point the EDI service to our TEST server, it errors out.

I have checked every single property on our TEST and DEV servers as well as the properties on the associated tables. They are identical. Nothing has changed in the SQL either.

0 1
0 277

SQL gateway. 'Locking' problem.

while debugging, is there in Caché a command to 'unlock' the instance of a class ?

close class, and kill class is not enough.

same problem in %Activate link

for each debug I have to exit (Halt) the terminal,

make debug in C#

coming back , and re-load again in Caché.

Regards,

Emanuel

0 1
0 313

Hi everyone, i cant understand what is wrong in my query:

ALTER TABLE MyNamespace.MyTable ALTER COLUMN CurrentColumnName RENAME NewColumnName

I just want to rename column name using sql.

I could not find any life example using this syntax.

SQLCODE for this query is -25:

-25 Input encountered after end of query

Quote from documentation:

0 12
0 593

II try to explore the new SQL LOAD DATA feature in SQL comparing it to SQL INSERT

I'm stuck at this point: INSERT INTO <table> (columns...) VALUES (.....)
allows having not just simple column references but also ALL Standard SQL FUNCTIONS (at least)
example:

INSERT INTO Test (ShortName,DOB) VALUES (SUBSTRING(Name,1,4),TO_DATE(displayDate,'MM-DD_YYY'))

This works perfectly.
BUT the same VALUE clause applied to LOAD DATA fails in various ways:

1 3
0 207
Question
· Jan 8, 2022
SQL LOAD DATA date values

I have csv date file with date values like this "4/10/2021" for April 10, 2021. I defined a table with this property: Property TranDate As %Library.Date.

I capture error

[SQLCODE: <-104>:<Field validation failed in INSERT, or value failed to convert in DisplayToLogical or OdbcToLogical>] [Location: <ServerLoop>] [%msg: <Field 'dc_data_finance.transact.TranDate' (value '4/10/2021') failed validation Field ...

I do not really want to change TranDate to %String. How can I import "4/10/2021" into %Date property?

0 10
0 447