New post

#SQL

13

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

I would like to start a discussion regarding Caché Objects and Caché SQL.

It is my understanding that the creators of Caché Objects see Caché SQL as the reporting arm of Objects and as such SQL is essential to Caché Objects.

I once met a Caché Objects programmer who was writing code to $Order through the Globals because that person thought that Caché SQL was too slow and inefficient. I attempted to convince the person otherwise.

So, what say you? Is SQL essential to Caché Objects?

Or

0 29
0 879

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,

0 5
0 816
Question
· Dec 10, 2017
Cache and Database

Hi, I'm a student, weak programming. I ask for your help, I write a program in C #, there are two tables, but I do not understand how to organize the connection between them many to many. Table in DataGridView1 from DataSet1 on Form1 key connection in DataGridView1 from DataSet1 to Form3

I do this on Form1

0 1
0 435
Question
· Feb 15, 2018
The Index on a String field

I noticed that the subscript of the index map is actually the collated field (%SQLUPPER).

Is String the only data type going through this transformation? Any other data type would also go through this transformation?

Thanks.

0 1
0 333

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 1
0 1.5K

I am trying to fetch the data from cache database. But i got the error like "CSP application closed the connection before sending a responce".

Below is the query.

SELECT
CallbackComment
FROM SQ.CBPhoneResult_View Where PhoneDateODBC = '2018-04-09'

I have investigated and found that "CallbackComment" contains the special character single quotes " ' " for one result and due to this i got this error.

In this field data is enter by customer. so we cannot restrict them like Do Not use single quotes.

0 30
0 1.7K

I can refer to the TracerName for a given ImageFile object with the following syntax: obj.Study.Injection.GetAt(obj.InjKey).RadioTracer.TracerName

Is it possible to write an SQL statement to search the PET.ImageFile table to find a match based on the RadioTracer.TracerName?

0 1
0 244

Hello everyone,

I try excuting a SQL Query like this SELECT ID, CompanyName FROM Company WHERE CompanyName LIKE 'condition'

But if I want to search CompanyName have special char %, ex: '100% Co' , 'Group of Mr.%', 'The %TaxProp'...

How can I take % to query, with

0 5
0 954

Is there a way or can it be done to use conditional logic in sql like so

 Query Q1(formal as %String) As %SQLQuery [ Final ]
{
    
    SELECT patientnumber,ID, CASE
    WHEN ID = 50 THEN "The is 50"
    WHEN ID = 30 THEN "This is 30"
    ELSE "The quantity is under 30"
END FROM Audit.Table WHERE ID = :formal AND EndDate is null}

}

0 2
0 386

I'm having trouble accessing the snapshots EnsLib.SQL.Snapshot in %Library.ListOfObjects that are returned from method ExecuteProcedure in EnsLib.SQL.OutboundAdapter. The Microsoft SQL stored procedure I am executing returns multiple resultsets.

The issue I am having is that my code works fine when executed in the business operation (commented out in the code below) but when it is executed in the business process it errors. Any ideas as to why this happens? The error I get is:

0 2
0 644

When calling the below stored procedure using the management portal (Run Query) no data is being retrieve, but if it is directly executed management portal data will show.

"IN" does not work changing it to "=", "like" the stored procedure will work. Does any one know how to fix this ?

Pass values to code is 'AB','TS','SK','GM'

0 1
0 618
Question
· Dec 26, 2019
Performant index on date field

Is there a way to get a good performing index on a date field? I have tried various date property indexes and the query plan is always in a pretty high range. Below are query plan result values I have observed:

StartDate > '2019-12-01' --cost = 699168
StartDate = '2019-12-21' --cost 70666
StartDate between '2019-12-21' and '2019-21-28' --cost = 492058

The query plans above were for type %TimeStamp.

0 7
0 599
Question
· Feb 20, 2020
Sql request

Hi All,

I need to build a search using a lot of filters, what's the best way to implement my sql requests.

1 sql request per property, or only one "big" sql request ?

what's the disadvantage of using lot of indexes ? Thank you

KR,

Amine

0 5
0 309

Does anybody know if there is an easy way to know if the execution of the command $System.SQL.PurgeForTable went well/wrong?

The documentation describes that a string is returned, but in fact there is a nice Quit "" in the code.

Maybe inside PurgeForTable^%apiSQL there is any process variable set when it goes well/wrong?

Thank you very much!

0 5
0 229

Cache ODBC State S1000 Native Code 400 Illegal Value

This is my query:

select DateTijdSec from
GLPPatTcActie
where pnr = '27085070017' and LTestId->Makey='BLA' and VerzamelDatTijd < '2021-03-04-2021 09:04' and glpactieid->makey in ('TAV','TMA') order by DateTijdSec desc

Most likely there is a wrong date time in the table, how can i get the data?

0 8
0 1.1K

I have created a custom role in IRIS for users to provide limited view-only access for querying tables in the HSANALYTICS namespace. WinSQL has been installed on a Windows server (WinSQL Lite version 14.0.244.784) and an ODBC connection entry has been created in WinSQL for the users to log in and run SQL queries. The custom role provides the roles/resources included in the attached file. The WinSQL System DSN entry defined is:

Health Insight UAT HSANALYTICS 64-bit InterSystems ODBC35

The ODBC35 driver is defined as:

0 7
0 673

I want to INSERT a record in a database using JDBC in OBJECTSCRIPT. At the same time, I want to obtain the insert ID. Is there a way to achieve this using the SQL Outbound adapter?

My code is something like this now:


Property Adapter As EnsLib.SQL.OutboundAdapter;

set sql = " INSERT INTO Prenotazioni_CUP "_
" (ID, cf
" VALUES (SEQTAB.NextVal, ?) "
set status = ..Adapter.ExecuteUpdate(.rs, sql, pRequest.cfAssistito)

0 5
0 494
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 593

If I had:
________________________________________
Class MN.Student Extends %Persistent
{
Property Name As %String;
________________________________________
Class MN.Course Extends %Persistent
{
Property TeacherName As %String;
Relationship StudentName As MN.Student [ Cardinality = one, Inverse = Name ];
________________________________________

For "Cardinality = one" the SQL line below would be sufficient for archiving
INSERT INTO MN.Course (TeacherName, StudentName) VALUES ('Pierre','3')"

0 1
0 145
Question
· Nov 29, 2023
Cloud SQL

I want to connect the app to Cloud SQL but when I registered and created the deployment (trial) I got the error:

Did anybody face such an error?
During the creation of an account, there was a message like 'username is taken' but the account have been created anyway. Maybe it could cause this problem.

0 5
0 232