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 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
I have several stored procedures that when I execute them they will only return a single snapshot. In my BP I have been setting this to a Snapshot variable then looping using a WHILE through the snapshot variable just to get that single value.
Since it is only a single row, is there an easier way where I don't have to do a WHILE loop to pull the values out of that row? Can I call First Row or something like that to get me just the row into the Snapshot variable?
I have been trying to pull data through a linked server in SSMS from an InterSystems Cache Database for a while, this is to enable us to join the data to other source systems in our Data Warehouse.
I have set up an ODBC connection and a linked server to the database and can execute queries through OPENQUERY in Management studio, but the data is huge (> 100million rows). So when I execute a SQL query with a WHERE clause the query just spins.
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:
I am trying to create a Cache Trigger on a FACS table using the SQL CREATE TRIGGER command but I am getting the message [SQLCODE: <-300>:<DDL not allowed on this table definition>].
I am not creating a persistent class but using the SQL CREATE TRIGGER command.
In the Inbound SQL Adapter settings, is it possible to specify more than 1 field as the Key Field Name?
Because of the way the Query is being index in Ensemble by the Key Field Name, sometimes transactions get missed and I would like to see if we can add an additional key to the mix to ensure all the transactions are picked up. In this case the InterfaceTrigger is an ID that is auto generated by the table, and I would like to use that as well to ensure we don't miss transactions, and it does not throw any warning messages when it executes the Delete Query.
I am trying to use Dynamic SQL because I need to supply data at runtime. The generated query returns 0 rows for some reason. If I copy/paste the query into Monitor, it works correctly. I am suspecting it has something to do with dates being the wrong format (I am supplying them in 'YYYY-MM-DD' format). Is that the cause? And if so, how do I supply dates in correct format?
I need to show the absence of data, so I have to join the list of predefined values with a result of a select statement. However, it seems like Table Value Constructors in JOIN are either not supported, or I do not understand the syntax. Basically, I am going for something like this:
Select v.valueId, m.name
From (values (1), (2), (3), (4), (5)) v(valueId)
left Join otherTable m
on m.id = v.valueId
The index we want to use is called "iFilter". Currently we use the following technique of ignoring all other indices because the automatically chosen index is always too slow.
I running a query and get the results inside a result set. Now I have to iterate through the result set many times. From the doc I've seen only the Next() method. Is there a way to reset the cursor? Otherwise what is a good data structure to save multiple rows of a table?
My code in this case is something like this:
set sql = "SELECT * FROM MY_TABLE WHERE X= '"_Y_"'" set status = ..Adapter.ExecuteQuery(.rs, sql) // somehow iterate the rs more than one time
What is the SQL table name for it? How can I obtain it via ObjectScript?
A quick search doesn't show any methods and properties. Documentation is a bit "wrong" here saying that the SQL table name is the same. It will be at least 'x_y.z'.
We are trying to track down the source of Orphaned messages and noticed that we are unable to query EnsLib.HL7.Message with any kind of WHERE or ORDER BY clauses in our SQL statement.
I know EnsLib.HL7.Message is a system table, but is there a way we can add additional Indexes to the table to make the query run better/faster without affecting the system?
I would like to capture any NACK's that is sent back to the Operation. The Operation is already setup to "Save Replies/IndexNotOK's", but I would like to see if we can query Cache and pull those NACK's into an extract.
Hello, new user here ! My admin granted me all access to the domain I'm working on. Nonetheless, I'm unable to perform simple SQL queries such as creating a table or an index.
What I have tried :
Creating a table : within the namespace I've been granted access to, I'm trying to create a table with 2 methods :
Hi all, I am using ExecDirect() method to execute my SQL query which is something like "SELECT * from Account where AccountNumber = ? " But when I am providing a accountnumber that don't exists it doesn't return anything as aspected. while I am trying to check resultset.%SQLCODE in this case it is giving me 0 not 100. What's wrong with my SQL query and Also I there any other Property through which I check for if my resultset is null or it has some values.
So last night I was doing a deployment which had a query to select from a large dataset of joined tables, to iterate through and populate data in a new table.
This was done in a classmethod using embedded SQL and the %PARALLEL keyword. On a testing environment with the same dataset, this worked perfectly a few days earlier.
Need to make ODBC connection to fetch data from SQL Server to IRIS 2021.1. Once we are able to query the data from SQL server, need to Pass it in ensemble production