New post

#SQL

13

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

I am very new to IRIS. We are developing a PHP application that connects to an IRIS instance of one of our vendors. I am having trouble with a specific table. When I need to query this specific table for specific fields, I am receiving an error. However, if I change the call to get all columns (`*`), there is no error. I get 37K records returned.

Error:

0 4
0 500
Question
· Feb 1, 2023
NEED NUMBER ROWS IN SQL SELECT

Hi guys,

I'm trying find a way to create a row with the number of each row.

For example:

In the SQL above, i want a way to create a collum with the number of corresponding row, in this case:

1

2

3

In SQl SERVER i can use FOR, RANK or ROM_NUMBER to do this, but, i can't find a way to do the same in cache SQL.

Can someone help me?

Thank you.

0 4
0 498

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 493
Question
· Dec 10, 2017
SQL IN OPERATOR

Hi,

I'm trying to use the IN operator in SQL.

does not work, can anyone tell me why that could be?

for example:

SELECT * FROM TafnitCore_Logistics_TransferPackage_DB.PackageData WHERE ID IN ('1||1||9852553062' , '1||1||9852553061' ) ORDER BY Building

And I get the following error:

0 3
0 492

Hello everyone,

I am looking for the syntax or the way to use a class created in the "BNA" Namespace (my application) from the %SYS Namespace.

Here is the context:

I have a "BNA" application contained in the "BNA" NS, this application provides a user creation functionality. This feature creates both the user in a table in the application and in the Iris system.

0 9
0 490
Question
· Nov 19, 2017
Force inner select to be executed

I have a query that I am attempting to run, but inner query is being rolled up into the overall query - per the optimizer (and jobexam observation). While I know that if the inner query runs first, I will eliminate a number of records (making the result set smaller, and return quicker).

Just an example of what I am attempting to do:

0 8
0 488
Question
· Oct 25, 2018
Healthshare Health Insight

Hello,
I need help in health insight. I am trying to generate reports on deep see but i am not able to pull in the patient ids as these are our requirements for the project. Can anyone help me in unlocking this feature.
Can anyone help me with link to correct documentation on how to access the edge gateways of multiple facilities to access the clinical data on sql explorer.

0 3
0 482

I have an SQL text index defined like this

Index TextIndex On (Text) As %iFind.Index.Basic(IGNOREPUNCTUATION = 1, INDEXOPTION = 0, LANGUAGE = "en", LOWER = 1);

If I run a query like this:

SELECT
Text
FROM Post
WHERE %ID %FIND search_index("TextIndex",'ABC')

I get 20 results with posts containing the term ABC.

But each post can contain the term ABC several times.

0 13
0 481

I experience this constantly with Cache SQL. Especially when querying the ATNA log.

SELECT TOP 400000 * FROM HS_IHE_ATNA_Repository.Aggregation ORDER BY ID DESC

That took 12 seconds. I then upped the number to 500,000 and it took 185 seconds.

Shouldn't the execution time scale proportionately?

If I run the 500,00 query again it takes 2.4 seconds.

0 4
0 479

Hi,

I am trying to input an entire xml into a sql server table. I am doing this via an operation in Ensemble.

I keep getting the following error

[SQL Server]XML parsing: line 0, character 0, unrecognized input signature

I have setup the table with a column parameter XML.

Here is the request object that is being sent to the store procedure

0 0
0 477

Hello,

I need to create a query that return the amount of heath related information from a patient.

I created one using the Analitics but, once our Analitcs database is update once a day this information is not reliable during the current query day.

Part of my Analitcs query is shown below. Where can I find the equivalent tables in Health Share? Any help is appreciate.

Tanks In Advance.

--Analitcs Query

1 6
0 477
Question
· Jun 14, 2018
Single Row SQL.Snapshot

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?

0 1
0 472

Hi,

The following sql statement is working through the SMP but couldn't make it work through the code.

The table is a linked table to oracle db.

Tried to change the date property in the linked table class from %Date to %String and also tried using %SQL.Statement and %Library.ResultSet

this work through SMP:

INSERT INTO PACKAGE.SOME_TABLE_NAME

(EFFECTIVE_START_DATE,MORE,DATA,HERE)

VALUES (to_date ('2018-04-01','yyyy-mm-dd'),123,456,789)

In the code i'm using '?' in the statement for the parameters.

0 14
0 469

Hi All

I'm looking for the a simple-quick-easy solution to monitor a SQL table thought Ensemble.

I have a process that update a DataBase ,a scheduled task that runs every night (Not Ensemble)

In the end it updates a table (replica_status) with a new recored with two fileds: Id, DateTime

I looked around the community but didn't find an answerd case.

I'm thinking on a Task that will run a sql outboud adapter BO that checks that table and send a alert if no new record was created yesterday

is this the right approach or is there's a better solution?

0 5
0 465

I have a tablePane ZEN Component and I am trying to get a filter running on the Specimen Id / Lab Number. The SQL is fairly complex with 3 UNION ALL statements joining 4 tables and a couple of lookup tables.

How would I get the filter on SpecId to work for my tablePane? It's not automatically applying the filter for me so I think I need code something.

0 1
0 463

Greetings to all!!! Suppose there is a table Mother (ID, Name) and Child (ID, Name, Mother), Mother in the table Childis a relationship. Let's say the task is to deduce the names of all the children whose their moms' names start with the letter 'A', I can do this in two ways in sql, and I can not understand the difference, the pros and cons that when to use:

0 1
0 463