New post

#SQL

17

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

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 273

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 321

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 134
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 266

Hi there,

Does anyone have an example of the method used to run an SQL query and generate the view in a Mumps routine. I have several SQL queries created from a vendor that need to be converted and the corresponding view referenced. I cannot seem to find any examples in IDX routines or in the IRIS/Cache documentation. Any help is appreciated.

-Alicia

0 5
0 328

Hi members,

I have these data :

and need to exploit the first character of cot field (Library CDU classification) like this :

I get this result... it's approximatively what I want... but I need it in just one line !

With mySQL I get it with just GROUP BY rcddate

0 3
0 242

I've been accessing Cache tables from a developer/reporting side, but am now involved in a project to create a data warehouse for our application. I'm trying to find a query I can use to return the sizes of all the tables in the database, so we can identify the largest tables and handle those individually. Can someone give me a query I can run against our Cache database to return the sizes of all the tables from largest to smallest?

Thanks for the help

0 8
0 424

When i use &sql(SELECT ......) in Cache I can watch the generated code
In the generated .int code and see what is happening.
Just with my normal rights

Now in IRIS I have just 4 line calling some class %sqlcq.***
With enough rights i find there is no such class but the generated .int routine
%sqlcq.IRISAPP.xEZgUjdXCCgQdZQPpRdOye1Ci2ue.1
That holds the code that i had in my .int on Cache

Can i switch this back somehow ?

0 7
0 407

Hi Dev Community,

I have a persistent Document class that has a FileName string property and another Question class that has an optional one-to-many relationship with Document.

I'm trying to add a SqlComputed property to the Question class (docFileName) where docFileName = Document.FileName if there is a related Document or an empty string if there isn't one.

I'd prefer the property to be SqlComputed so that if Question.Document changes, Question.docFileName will automatically update.

0 12
0 320

Hi Guys

I'm rather new to HealthConnect, and I've been doing a lot of work with the browser based SQL portal and don't particularly like it. Is there an alternative application that I can use, something like SQL Express or VScode with a plugin?

Andy

0 4
0 352

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 267

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

0 6
0 733

I need to execute multiple DELETE statements in a single query like this

DELETE FROM TableName WHERE ID = 2;
DELETE FROM TableName WHERE ID = 3;
DELETE FROM TableName WHERE ID = 4;

However It does not work when there're more than 2 statements and gives me an error

Expected FROM found WHERE^DELETE FROM TableName WHERE

Using IN is not an option.

0 9
0 291

Hello, I need to use IRIS to connect to an MSSQL base.
It has to be done via ODBC, I can't use JDBC at this time by client option.

I am trying to use Microsoft Driver
libmsodbcsql-13.1.so.9.2

But I can't, my attempts result in:
Connection failed.
SQLState: () NativeError: [11001] Message:

I have done all DSN configuration, and my configuration is listed in SQL Gateway Connections. I know it's working, because when I run a test with isql I have the information that connects to the bank.

1 2
0 578