SQL

Syndicate content 31 

HI,

I have a query like below but its syntax is not accepted by Cache.

I would like to perform a left join on two tables first and then make a inner for this result with another table.

Given the constraint that we only allow one SELECT in the query, it is possible to achieve this semantics ?

Thank for your help.

 

Select * 
FROM ( sample . employee e 
LEFT JOIN  sample . company c 
on c . id = e . id ) g
JOIN sample . vendor v
on v . %id = g . attr 

Last answer 14 October 2017 Last comment 15 October 2017
0   0 1
545

views

0

rating

There are three things most important to any SQL performance conversation:  Indices, TuneTable, and Show Plan.  The attached PDFs includes historical presentations on these topics that cover the basics of these 3 things in one place.  Our documentation provides more detail on these and other SQL Performance topics in the links below.  The eLearning options reinforces several of these topics.  In addition, there are several Developer Community articles which touch on SQL performance, and those relevant links are also listed.

There is a fair amount of repetition in the information listed below.  The most important aspects of SQL performance to consider are:

  1. The types of indices available
  2. Using one index type over another
  3. The information TuneTable gathers for a table and what it means to the Optimizer
  4. How to read a Show Plan to better understand if a query is good or bad
Last comment 18 January 2019
+ 8   3 2
531

views

+ 8

rating

We don't often use SQL within our org, which is mostly due to the performance issue we experience due to the quantity of data we are reviewing.

Aside from the standard performance measures for non-Caché databases, are there any recommended approaches when querying large tables?

The table would have roughly 50M records, but there are not a finite amount of sub-nodes.

Last answer 2 December 2016 Last comment 6 December 2016
0   0 4
525

views

0

rating

Greetings,

I am working on the first of many triggers which will have identical code upon row insertion or update of a single column.  According to the document I should be able to define a multiple-event trigger using Cache SQL/DDL.

Here is a link to the current CREATE TRIGGER documentation.  Within the description section is the following paragraph:

Last answer 11 July 2017
0   0 0
0

comments

517

views

0

rating

When working with a large query executed though an ODBC connection what is the best way to allow the paging of the results at the client side.  I have tried some methods using %VID and similar methods, but these really don't seem to work as the value returned is related to the ID of the data and not the position in the results set.  What would be ideal is if the value seen in the management portal when you check of "Row Number" was available to external queries through ODBC.  I have not seen a way to return this however.

Last answer 10 January 2017 Last comment 9 January 2017
0   0 1
504

views

0

rating

Hi all,

I am new in Cache. I have to update a record with a long text field, for that,  I am using ODBC but the issue is that it is returning an error when I execute the ODBC  SQL statement because the field contains some commas, colon, double quotes, single quotes, and CR LF characters.

There is a way to escape this chars? I have seen I can save the field when I replace the single quotes with a double single quote (it's -> it''s) but I can not save the information when the field contains double quote chars. What about the other chars?

Last answer 13 March 2018 Last comment 13 March 2018
0   0 3
503

views

0

rating

How to convert SQL persistent classes to JSON output - I have tried 1 option but appears to be very lengthy process.

I have many other SQL storage's defined and have to expose them as well to JSON. Can anyone please suggest a better approach.

My column names usually match with my Json properties.

My User.TestClass is the class with GetJsonList() as my method to return /output JSON formatted data.

The Code tried and SQL storage structure are as follows -

Last answer 19 April 2018 Last comment 18 April 2018
+ 1   0 3
496

views

+ 1

rating

I'm trying to learn how To use SQL in CACHE, so I hope I don't bore you with "Dumb" questions.... 

I'm getting "ERROR #6022: Gateway failed" message following this line of code. 

s sc=gc.Prepare(hstmt,pQuery) 

This line of code comes from an example I found in the documentation.  pQuery is the "Select" statement setting up the variables and tables I'm trying to pull information from. 

What does that error indicate? 

Thank you. 

Last answer 14 February 2018 Last comment 12 February 2018
0   0 4
485

views

0

rating

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 

 

Last answer 19 October 2016 Last comment 20 October 2016
0   0 2
480

views

0

rating

No doubt bitmap indexing, if used with a suitable property, performs just impressive!
But there is a major limit: ID key has to be a positive integer.
For modern class definitions working with CacheStorage this is a default.

BUT: There are hundreds (thousands ?) old applications out in the field that
are still using composite ID keys.
Or - to name the origin - work on Globals with 2 subscript levels (or more).
They are by construction excluded from our "Bitmap Wonderland".

Last comment 17 February 2018
+ 7   1 0
475

views

+ 7

rating

Hello! So, my knowledge on the Cache database is extremely limited, and I was hoping I could find some assistance here. I'm connecting to the DB via ODBC. The table(s) I'm interested in are named as such nameYYYYMMDD. So each day, a new table is created with logs. We'd like to grab these records each day, for the previous day's logs. 

Last answer 19 July 2017 Last comment 19 July 2017
0   0 2
470

views

0

rating

Hi all,

I have a non objectscript application connecting to a cache instance via ODBC and one column is a list of serial objects. The output from the query contains a lot of special characters and I'm hoping there's a better way to get this data back so I won't have to perform extensive parsing on the application side.

I've tried using the $ListToString() function, but that didn't help much, probably because the list contains complex objects rather than primitives. 

Any help will be greatly appreciated!

Last answer 19 June 2017 Last comment 18 June 2017
+ 1   0 3
467

views

+ 1

rating

Quotes (1NF/2NF/3NF)ru:

Every row-and-column intersection contains exactly one value from the applicable domain (and nothing else).
The same value can be atomic or non-atomic depending on the purpose of this value. For example, “4286” can be
  • atomic, if its denotes “a credit card’s PIN code” (if it’s broken down or reshuffled, it is of no use any longer)
  • non-atomic, if it’s just a “sequence of numbers” (the value still makes sense if broken down into several parts or reshuffled)

This article explores the standard methods of increasing the performance of SQL queries involving the following types of fields: string, date, simple list (in the $LB format), "list of <...>" and "array of <...>".

+ 6   0 3
0

comments

461

views

+ 6

rating

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=...

Last answer 5 October 2016 Last comment 7 October 2016
+ 1   0 0
459

views

+ 1

rating

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.

Last comment 4 March 2016
+ 1   0 4
0

answers

458

views

+ 1

rating

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

Last answer 20 March 2017
0   0 2
0

comments

458

views

0

rating

In writing some code on:

Cache for Windows (x86-64) 2015.1 (Build 429U) Fri Feb 13 2015 14:37:23 EST

I noticed an unexpected "ROLLFAIL" error when a Unique Index fail was generated in a %Save() call.

The object SHOULD fail to save, since the Unique criteria is not met, but I didn't expect to see a ROLLFAIL error as the "Last Error".

When running the following class code:

Last answer 17 November 2016 Last comment 28 November 2016
0   0 2
458

views

0

rating

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.

 

 

 

Last answer 12 November 2016 Last comment 8 July 2019
0   0 2
444

views

0

rating

I am currently working on a issue with WRC on one of my Inbound SQL Adapters not returning all the records it should be. If I looked at the count of the records in Ensemble and compare it to that of a Microsoft SQL View, Ensemble seems to be off by a few records here and there. I am using a full dynamic select statement in my settings of the adapter.

SELECT Text, PhysicianLastName, PhysicianFirstName, PhysicianAddress1, PhysicianAddress2, PhysicianCity,

Last answer 20 July 2017 Last comment 14 August 2017
0   0 1
435

views

0

rating

Hi,

I have a class with around 400k lines and 60 columns. Class storage is Cache SQL storage (Mapped from a global).

 I want to create multiple indices on certain fields.

I am familiar with two approaches:

1. Create a new map (Index type) on a pointer global.

2. Create a bitmap index

Which approach is more recommended to be used in the case I described? If there are any other approaches, I will be happy to hear.

Thanks :)

 

Last answer 11 October 2017 Last comment 19 October 2017
0   0 2
435

views

0

rating

I  have generated a class using the linked procedure wizard however  I can't get it to work if the  datatype  of one of the parameters is VARCHAR(MAX).  It works fine if I change it to say VARCHAR(500) and  rerun the  stored procedure wizard. 

I get the following  error returned.:

ErrorMsg:   SQLState: (07002) NativeError: [0] Message: [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error

Last answer 10 May 2018 Last comment 1 June 2018
0   0 4
431

views

0

rating

I have a lookup table and record batch Table I would like to do a count on the records stored in that batch by counting the number of records in that batch that have  a certain key on the responseKey column. This column keys are stored in the look up table for comparison. So I would like to do a join sql pivot that will use my keys stored in the lookup as columns and count as values

so far I have managed to do this but this is not efficient I would like to fire that sql once not on every count

0   0 2
0

answers

424

views

0

rating

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

 

Last answer 13 May 2016 Last comment 13 May 2016
0   0 1
423

views

0

rating