SQL is a standard language for storing, manipulating and retrieving data in relational databases.
Kindly help me on the below points
1)Concatenate the Date column and Time column.
2)Get the current date only without time.
3)Find the current age using DOB column.
Hi
I'm trying to add multiple rows at a time to a SQL table for a persistent object, but I can't seem to get the syntax correct. When I try the below it doesn't work.
INSERT INTO Sample_Table.Accounts (Account, AccountName, Bank)
VALUES
('123456', 'AccountNameOne', 'BANK1'),
('654321', 'AccountNameTwo', 'BANK2')Thanks
Peter
Hi, we are trying to run a dynamically generated Oracle Insert SQL script using Do $SYSTEM.SQL.DDLImport("Oracle",""," sql.txt",[Error Log]) to insert multiple rows into a Linked Table within HealthConnect to an Oracle database.
An interesting pattern around unique indices came up recently (in internal discussion re: isc.rest) and I'd like to highlight it for the community.
As a motivating use case: suppose you have a class representing a tree, where each node also has a name, and we want nodes to be unique by name and parent node. We want each root node to have a unique name too. A natural implementation would be:
Class DC.Demo.Node Extends %Persistent
{
Property Parent As DC.Demo.I'm getting wrapped around the axle with CAST and CONVERT and can't seem to find a way to do this (short of adding a stored procedure wrapping $zdt($zdth(posix,-2),3), which I'm refusing to do on principle, because there has to be some way to make this work).
Any ideas?
I am calling a stored procedure over an ODBC connection and every time I call it there are several warnings written to the log event {Found no Parameter 1 (used as 1) for query}.I seem to be getting this on every query executed and that seems to happen a number of times the query parameters are per query and its filling up my disc.
1) Is there a way to suppress these warnings as the query seems to be executed and data written to the database?
Hi,
I'm trying to load some data into IRIS using LOAD DATA in the IRIS terminal however I am getting blocked by this error. I have read the documentation on this page trying to set up a gateway connection but get the same Connection cannot be established error.
Say I have a persistent class in IRIS with an optional property EmailOptIn:
Class Person Extends %Persistent
{
Property Name As %String;
Property EmailOptIn As %Boolean;
}
I later realize that I'm doing a lot of null-checking on this property where I shouldn't need to. The solution is to make this a required property:
Class Person Extends %Persistent
{
Property Name As %String;
Property EmailOptIn As %Boolean [ Required ];
}
When I make this change I'll need to update all the existing data to set a reasonable default where it is null.
Hey Community,
I have an scenario, please advise or suggest possible ways to solve it.
So i have a persistent class to test results of a diagnostic laboratory. The properties are
Hi I've got a service setup to query a sql server via ODBC.
I've got all the parameters etc setup and everything is ok
My Query is
SELECT TOP 1
CI.ClientID,
Gender,
DateOfBirth,
MaritalStatus AS MartialStatusCode,
(SELECT CodeDescription FROM GenMaritalStatus WHERE Code = MaritalStatus) AS Martial,
Ethnicity AS EnthnicityCode,
(SELECT CodeDescription FROM GenEthnicity WHERE Code = Ethnicity) AS Ethnicity,
Nationality AS NationalityCode,
(SELECT CodeDescription FROM GenNationality WHERE Code = Nationality) AS Nationality,
DateOfDeath,
CN.Surname AS Surname,
ISNULL (CN.GivenName1, '')
+ ISNULL( CN.
I have a table with a Varchar(max) column that I have created via the HealthShare SQL portal, and I see that in it's underlying class that column corresponds to a
When I try to do a text search on that column (referenced as cd.Code in the example below) I get an error
select *
from DevTools_CodeAnalysis_tables.Code cd
inner join DevTools_CodeAnalysis_tables.MethodData mtd
on charindex( mtd.qualifiedRef, cd.Code) > 0It produces the error
Scalar function CHARINDEX (arg2) not supported for stream fields
How do I search it?
Hi all
I'm struggling to insert into a table, and it is baffling me. The insert statement is now very simple (I was using a complex INSERT SELECT statement but I'm trying to narrow down the problem).
Insert statement:
INSERT INTO Phu_Replay_Schema.
Hey Community,
Don't miss the latest videos on InterSystems Developers YouTube channel:
In the good old days (tm) determining the size of the data, streams, and indices for a class/table was easy - you just ran %GSIZE and check D, S, and I globals respectively.
However, nowadays sharding, optimized global names, and indices in separate globals produce %GSIZE output looking like this:
Global Size Display of /irissys/data/IRIS/mgr/irisshard/
1:35 PM Dec 02 2020
IRIS.Msg 1 IRIS.MsgNames 1 IRIS.SM.Shard 1
IS.DGoWeK.1 24359 IS.DGoWeK.2 3 IS.DGoWeK.3 2810
IS.DGoWeK.My personal problem is the use of DBeaver as a migration tool.
Especially as one of the strengths of IRIS ( and also Caché) before is the availability of the
SQLgateways that allow access to any external Db as long as for them an access usinig
JDBC or ODBC is available. So I extended the package to demonstrate this.
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.
Is this possible?
Thanks
Scott
Hi Guys,
So I've been following this guide in using a %Library.ResultSet with a ClassName / QueryName as described in the first example.
https://docs.intersystems.com/irislatest/csp/documatic/%25CSP.Documatic…
The code I've got so far doesn't work and is as follows:
set rs=##class(%ResultSet).%New()
set rs.ClassName="GMECC.DocmanConnect.Tables.vwNewGPs"
set rs.QueryName="GetRows"
set sc=rs.Execute("a") If $$$ISERR(sc) Do DisplayError^%apiOBJ(sc) Quit
while rs.%Next() { do rs.%Print() } And the referenced class is
Class GMECC.DocmanConnect.Hi, what’s the best way to check if your query return 0 records?
If $$$ISOK(sc)
{
Set tResult = sqlStatement.%Execute()
WHILE tResult.%Next()
{
set tRecords = tResult.%ROWCOUNT
}
if (tRecords = "")
{
write "no records"
}
}
Client: Northwell Health
Role: Senior Developer
Location: Remote
Duration: 6+ Months
Description:
Team Overview:
The FHIR Platform team is tasked with providing the core infrastructure in providing access to Northwell HIE patient data complying with HL7 FHIR and USCDI standards.
Position Summary:
The primary purpose of this role is to provide technical design, coding, testing and documentation for multiple components in the FHIR solution.
Responsibilities:
• Develop end-to-end solutions, participate in code reviews, unit test and deploy.
Hey Developers,
See how you can get high-performance relational access using SQL to manage data within your InterSystems products:
Hi,
We need a web-based SQL tool to connect to Ensemble/Cache, that will offer more functionality than what the Management Portal offers.
It needs to be web based, so that we can host it on a machine that has connectivity to the server via the superserver port. We (the devs and support) only have browser access to the environment via Remote Desktops, hence the requirement.
Has anyone gotten OmniDB to connect to Ensemble/Cache? If so, can you please advise on how to configure it?
Any other suggestions for such a tool are also welcome.
Kind regards,
Stefan
Hi folks!
Sometimes we need to import data into InterSystems IRIS from CSV. It can be done e.g. via csvgen tool that generates a class and imports all the data into it.
But what if you already have your own class and want to import data from CSV into your existing table?
There are numerous ways to do that but you can use csvgen (or csvgen-ui) again! I prepared and and example and happy to share. Here we go!
Recently our team have been getting requests to pickup a large amount of data from API nightly (e.g. using ODATA to loop through pages) and placing that into MS SQL databases.
My question is, what is considered the best practice to get data from API and route to external SQL database WITHOUT persisting any messages/traces etc?
At the moment my thinking would be to write a service that triggers an action once a day to an EnsLib.HTTP.OutboundAdapter, then map the HTTP response into a linked table (via method to keep the operation clean that managed the gateway on the way in and out of the method).
Hi,
Is there a way to find the median in Intersystems Cache SQL? I know it is not available as an aggregate function. Also in SQL Server I could try something like:
SELECT ( (SELECT MAX(Score) FROM (SELECT TOP 50 PERCENT Score FROM Posts ORDER BY Score) AS BottomHalf) + (SELECT MIN(Score) FROM (SELECT TOP 50 PERCENT Score FROM Posts ORDER BY Score DESC) AS TopHalf) ) / 2 AS Median
However, there is no PERCENT Keyword in Cache as well. Any suggestions?
Thanks
Hi everyone,
How do you alter primary key on a table that has data?
Any example much appreciated.
Cheers,
Tom
Hi everyone, how do you run multiple quires?
I have tried couple of different ways, but not working.
SET sql = 2
Set sql(1) = "UPDATE QUERY"
Set sql(2) = "UPDATE QUERY"
Set sqlStatement=##class(%SQL.Statement).%New()
Set sc1=sqlStatement.%Prepare(.sql)
If $$$ISOK(sc1) {
Set tResult = sqlStatement.%Execute()
}
else{
$$$LOGERROR("Failed")
}
I have a linked procedure class and SQL gateway setup and I can't seem to get any response, status or error from using the stored procedure, I think it must be something big I'm missing to not even get a status.
Can someone see what my problem is please, any help would be very much appreciated.
Class generated by linked procedure wizard:
Include %occInclude /// Class dbo.
A question has come up that I am not finding the answer for. Does the daily purge process re-index EnsLib.HL7.SearchTable or other SQL tables? In looking at the purge process documentation I am not seeing anything that mentions EnsLib.HL7.SearchTable. Do we have to manually constantly re-index tables that we create? For example I created another search table based off of EnsLib.HL7.Search table, will I need to constantly watch this as it grows?
Hi
I'm using an embedded SQL statement with a a cursor-based Embedded SQL query that uses host variables in the where clause, however, what I'm doing doesn't seem to work. Can anyone help?
The code I'm using is
set tMessageName = "AssessmentsMessage"
set tIdentifier = "SectionCode"
set ID = 0
&sql(
Declare IDs Cursor For
select %ID
INTO :ID
from GMECC_DocmanConnect_Tables.I have a %Persistent class with properties that are of %SerialObject. I want to add an index to a property of the %SerialObject class.
Is this possible?