#SQL

Hello all,

I have a Recordset object which contains data from a table "XYZ".

Currently i use this object to extract data using %Get(COL1,COL2...) in a loop and than pass it to a function which inserts the data into another dynamically created Table "ABC" for each record. This takes a lot of time when 100's of records.

Is there a way i can directly copy a RecordSet to a dynamic table without looping through..?

Something like copy Recordset (COL1,COL2..)--> "ABC"

Thanks,

Jimmy

Hello,

I've got a table with many attributes and data. There is 10 000 000 registrations approximatly.

I need to make a research on this table with filters, paging, and order.

You can see an example of my SQL request :

SELECT * FROM ( SELECT TOP ALL * FROM ANCV_Data.Titre WHERE etatTitre = 'Emis' ORDER BY numRemise desc) v WHERE %vid BETWEEN 1 AND 25

Cause there are many data on my table, my SQL req is too long if I don't have index on my attributes BUT there are many associations possible...

Hi,

Do you have any experience in server side pagination with IRIS using Angular on the client side?
Any idea on a typical SQL request on the server side?

Thanks!
Blaise

Is there any way to execute many SQL commands at once as an (anonymous)block?

for example in PostgreSQL there is

DO $$

BEGIN

--here is Inserts/Updates/Deletes

END $$

Or in Oracle

BEGIN

END; 

Thanks for the answers!

Since I'm just starting to use this aspect of IRIS I can't tell if this is a bug or some kind of nuance. If I run a single column query like this, I get results:

However if I try to retrieve using a DISTINCT or GROUP BY, I get no results:

And again except with a GROUP BY, with no results:

I'm wanting to write a query in SQL that will return a row with a count for each day for a given month or year for a specific operation or configname. The following is a start but I'm not finding what I want in the documentation to parse out the TimeLogged field of the table. Nay help is appreciated.

SELECT count(TimeLogged), ConfigName
FROM Ens_Util.Log
where TimeLogged like '2021-07%'
and ConfigName = 'operation_Name'
group by TimeLogged

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?

Hi everyone,

How do you alter primary key on a table that has data?

Any example much appreciated.

Cheers,

Tom

Hi,

I

want to get the values from a serial property because my code depends upon the class serial class.

For example

Serial class

Class Data.Serial Extends %SerialObject

{

Property FirstName as %String;

Property LastName as %String;

}

Persistent class

Hi.

I have a query:

SELECT '['||Material->Sifra||'] '||Material->Opis AS Material,
SUM(MasaBlago) AS MasaBlago
FROM Tehtanje.Dokument
WHERE DatumDokumenta BETWEEN '01/01/2023' AND '04/01/2023'
AND (Material->Sifra %INLIST $LISTFROMSTRING('5,7',','))
GROUP BY Material
ORDER BY %EXACT Material

The query returns all rows where Material->Sifra is 5 or 7. That's OK.

If I want to get all rows where Material->Sifra is NOT 5 or 7 I use query:

Hello Community,

I'm executing the same query with same column name but in different case. An unique cached query generated while query executed first time. The query preparser only normalize the keywords and send to the SQL engine generates the Hash. Eventually use the cached query next use.

Now my question, The hash values are same for both of the queries. Then why it creates two cached queries.

Query1: select * from MyLearn.Test where Name['Kev1'

Query2: select * from MyLearn.Test where NamE['Kev1'

Hello Community,

Hello my Friends,

I have a question, I have a data like this

And I want to create a display like this, which I can put a status column beside the Item Column

The status become new, if there's no previous item, and if there's a same item after the first one, the status become Old

Does anyone could help me ?

We are creating a package (written in Caché Object Script) that will provide access to an external DB (MySQL). Because applications that use our package will be run from machines with various, potentially unexpected, operating systems, we’d like to establish a connection to the external DB without using DSNs (we’ve heard that setting up DSNs on certain non-Windows machines can be cumbersome and problematic).