Hi,

I'm doing a query in SQL and I need to sort my data by some non-repeated field.

Unfortunately, my data is grouped in a way that I cannot guarantee that any column will not have repeated data, so one solution would be to take the row number.

Also, the Cache is not accepting Row_Number () in my querry and I would like to know if there is another solution to return line numbers or some way to add this function to the Cache.

Best regards.

12 7
0 2.7K
Question
· Mar 3, 2023
? %QUERY Wizard in VSCode ?

For my recent tutorial, I used %Query Wizard of Studio.

Could any of the VSCode experts please show me how to generate

Query Statement with input parameters, CONTAINID , ROWSPEC
and the required class methods
- <queryname>Execute()
- <queryname>Fetch()
- <queryname>Close()

I was just not able to do it.
​​​​​​​

3 3
0 175

Hi all,

I'm trying to use LOAD DATA to insert 11k (11,377) rows of data. LOAD BULK DATA is not available for the version of IRIS I am using.

After calling LOAD DATA it says only 5,500 rows has been inserted. The LOAD DATA docs says any error rows are skipped and a count of skipped rows can be found in %SQL_Diag.Result however there are no results here. There are no errors in the xDBC error log either.

Why have over half the rows been skipped?

3 3
0 334

Atelier Users:

Has anyone found an Eclipse plug-in that provides the capability to connect to a Caché server and give the user a way to write SQL queries using the tables from that server? I'm picturing something like a "WinSQL"-client built as an Eclipse plugin.

I've found and tried the following, but I couldn't get it to connect to my local Caché instance.

http://eclipsesql.sourceforge.net/

2 4
0 841

I have a MySQL server with "posts" table.

I also have a Caché server with "downloadedposts" table.

They are connected from Caché to MySQL via SQL Gateway

I want to keep Caché table synced with MySQL one (MySQL "posts" table is a master copy), so periodically Caché queries MySQL server and downloads data. So far so good, and if a record appears or changes in MySQL table, Caché downloads the changes.

The problem I'm encountering is that sometimes rows would be deleted from MySQL "posts" table.

How do I synchronize deletions?

2 4
0 1.1K

hi,

i have two where-clauses:

a) ... WHERE company=1 and product=7

b) ... WHERE product=7 and company=1

with other words the position of the where fields are swapped.

now the question is:

bring the where clauses a) and b) the same performance(queryplan) or do i have to write it in a special order???

in my point of view, there is a parser/optimizer who cares about this, so i don't have to care about.

2 3
0 374

I want to override the Get and Set methods of a class property. The class maps to a pre-existing global. The property is defined like so:

Property Invalid As %Library.Boolean;

with the property mapping to a node like ^GLOBAL(Code,"INVALID")=1
Code is a property in the same class.

The value can be 0 or 1 or the node might not exist. When it doesn't exist I want the value of the SQL field to come out as 0 (false).

2 12
0 1.6K

Is it planned that LOAD DATA takes into account several DATE/DATETIME formats with, for example, a parameter indicating the format used in the source data?

example :

LOAD DATA .../...
USING
{
  "from": {
    "file": {
       "dateformat": "DD/MM/YYYY"
    }
  }
}
2 3
0 179

Hi All,

I have a general query in regards to developers experience on extracting data from cache databases and the most efficient way to do so. I work with a number of clients who have applications with cache databases and require the data off the host system and onto data warehouse platforms for research and analysis. Often they require the data in source state which means the extracts are often simply a table scan of the entire database table without any aggregation or manipulation.

1 2
0 1.6K

During runtime I build an object which is essentially a wrapper over in-memory table:

col1 ... colN
val11 ... valN1
val12 ... valN2

I want to use this object as a part of INSERT or UPDATE queries, based on a value of some column (the main use case one of the columns is an ID value)

What's the best way to expose the object to SQL?

1 13
0 526

Hi! I have a complex SQL select query which I execute via %ResultSet. It also includes a UNION. The select statement within the union should only be done when an external parameter is set, and I'm not sure of way of doing that within SQL, other than something like

SELECT FOO FROM BAR

WHERE (some conditions)

UNION

SELECT FOO2 FROM BAR2

WHERE :doUnion = 1 AND (some conditions)

...this works, but I don't think it's particularly optimal as I believe it will still execute the second SELECT regardless of :doUnion. It's also sort of hard to read...

1 12
0 426

Hi,

When for any particular reason I need to update a record and don't want to pull the triggers, the keyword %NOTRIGGER can be used. But I've been trying to do the same when I change the row using the object approach, but I can't find it. Anyone knows if it's possible to avoid pulling triggers when working with objects?

Sadly, the utility "DISABLE^%NOTRIGGER" doesn't seem to exist ;-)

Thank you,
David

1 2
0 240