New post

#SQL

16

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

Let Say I have
Class Carrier Extends %Persistent

Property Employee As Array of Class Employees

Class Employees Extends %SerialObject

Property Name As %String

Property Address As Array of Class AddressDetail

Class AddressDetail Extends %SerialObject

Property Street1 As %String

Property Street2 As %String


I can get value for Employee Name as Employee_Name.

But I want to Get Value of Address Street1 and Street2 of Class AddressDetail using SQL Query
How can I get that?

0 3
0 370 

Class ICT.Experiments.A Extends %Persistent
{
Property Name As %String;
Property Collection As list Of ICT.Experiments.B;
Property Collection2 As array Of ICT.Experiments.C;
}

Class ICT.Experiments.B Extends %Persistent
{
Property Name As %String;
}

Class ICT.Experiments.C Extends %Persistent
{
Property Name As %String;
}

I have the classes above and I can select columns from the array collection by using two joins as follows:

0 9
1 1.8K

Hi Community!

Please welcome the new video on InterSystems Developers YouTube Channel:

Optimizing Your SQL Queries

https://www.youtube.com/embed/Gx6-_fLrsjw
[This is an embedded link, but you cannot view embedded content directly on the site because you have declined the cookies necessary to access it. To view embedded content, you would need to accept all cookies in your Cookies Settings]

0 0
0 259

I have a persistent class.

I want to store one of the properties there as a stream or a string depending on a size.

99% of values would be strings (less than $$$MaxStringLength characters) so I don't want to store everything as streams.

What do you think of this approach?

What's the best architecture to implement in this situation?

0 4
0 541

I know &SQL returns only one result but is

&SQL(SELECT ID FROM Cinema.Film ORDER BY ID DESC)

and

&SQL(SELECT TOP 1 ID FROM Cinema.Film ORDER BY ID DESC)

the same in terms of processing required?

0 7
0 354

Hello everyone,
It is my first post in the community. Very recently I started working using IRIS, creating services with Cache.
Today they gave me a new task and I'm really lost.

How can I use an oracle database to create services using iris?

Throughout the day I was looking for a guide to use a remote Oracle database. but unfortunately I only managed the connection in SQL Gateway

0 5
0 874
Question
· Mar 12, 2019
String to date

I am trying to convert a string to date but can not get it to work I have function that I would like to take in a date string and covert it to date object

here is the ezample so far can not get it to work any help appreciated 

set p="12/03/2019"
 
w $System.SQL.TODATE(p,"YYYY-MM-DD")
 
<ILLEGAL VALUE>todate+32^%qarfunc

if I try this still get the wrong value returned

set p="12/03/2019" 
w $ZDATE(p,3)
1841-01-12
0 6
0 2.8K

here is my trigger:

CREATE TRIGGER "SQLUser".SYM_ON_U_FOR_SYM_CHNNL_125123 AFTER 
UPDATE
   ON "SQLUser"."sym_channel" REFERENCING OLD ROW AS OLD NEW ROW AS NEW FOR EACH ROW 
   DECLARE @var_row_data VARCHAR(16336);
WHEN
   (
      1 = 1 
      and 1 = 1
   )
   -- I will use OLD/NEW alias here set @var_row_data = ''; LANGUAGE sql insert into mytable (id, data) values(1, @var_row_data);

here is error message:

0 1
0 227

I've setup ODBC connection so I can access Cache data within SQL Server.

I want to be able to write SQL queries for internal monitoring purposes, similar to what's possible with SQL Server. Specifically I want to be able to check mirroring status (i.e. check which is the current primary mirror member), check the status of any Ensemble productions (started/stopped), check the status of business hosts etc. I want to do all of this from SQL Server to go with our other system monitoring solutions.

0 1
0 276

Hi All, I am a Data Architect and am trying a method of deploying alter statements onto Cache DB.

My Data Modeling tool generates alters as this.. So question is if the Cache has a way to rename table? If so what is it?

ALTER TABLE <SchemaName>.GROUP_TYPE_NODES RENAME TO GROUP_TYPE_10082019140110000
;

This is giving error.

0 4
0 718
Question
· May 7, 2019
Linked Tables and Dialects

Hi! I've been fiddling with linked tables to get data from other servers, and I encountered a problem that I'm curious about. Maybe I'm not using these tools as intended or there's more going on, so I'm asking here.

I'm running a query on linked table A, something simple like this:

select name from A where id = 5983658923646

And I get this error:

[SQLCODE: <-400>:<Fatal error occurred>]

[%msg: <>]

0 6
0 504

At LifeLabs, we are focused on our vision of building a healthier Canada! We are the largest community diagnostics laboratory in Canada with over 350 collection centers, 21 laboratories and service over 19 million patients each year. As the Software developer, you will be concerned with all facets of the software development process. You will be responsible for the design of application modules, maintain and deploy software applications to meet user and business needs.

0 0
0 407
Question
· Jan 22, 2019
SQL select to a list

I need to select my result into a list and be able to loop through the list when query finished any help appreciated here is where I am

##sql(SELECT %ID INTO :IDArray() FROM MergeHyland.TypeTwoDimesionCollection WHERE GUID = :Key AND EndDate IS NULL)
for I=1;1:$LISTLENGTH(IDArray)

{

w $Data(IDArray),i

 


}
0 2
0 1.2K
Discussion
· Mar 19, 2019
Rule to validate SQL syntaxis

Hi! We have received a request to create a new rule on CachéQuality to identify when a developer uses double quotes (" ") within any SQL statement.

We have been asked many times about SQL validation rules, and we would like to open a debate to allow everyone discuss what would you like to be checked on a SQL statement.

Current examples are for basic situations:

0 13
0 544

Hi, we are a veterinary lab and we use both the LAB and FIN systems of Antrim. Now we are looking to expose the data in a SQL/Object compatible way so we were wondering if same / similar things had been done by other community members already? If so, could you please share your approach / experience / gotchas with us and we are all ears. I can be reached at yang.jiao@antechmail.com . Thank you!

0 1
0 251

Problem

I'm working on exporting data from an Intersystems Cache database through the Cache ODBC Driver. There is a particular table that is giving me an error message. The ODBC Driver crashes and reports an error from the Cache system. I think I was able to trace down where the error is coming from, but I do not know how to debug or fix the error.

The table I am trying to extract is called SEDMIHP.

Here's the Error:

0 2
0 1.2K
Question
· Mar 20, 2019
Query Response from MSSQL

I am having an intermittent issue that when I make a call to MSSQL from a BPL that the response does not come back in the amount of time required. Since the call from the BPL is synchronous I tried changing the timeout to 60 but it has not helped (see below). Is there anyway to guarantee that the call waits long enough for a response before continuing on?

Thanks

0 4
0 342

I am experiencing a problem with an EDI process that uses a SQL Batch Service to connect to our DEV environment. However, when we point the EDI service to our TEST server, it errors out.

I have checked every single property on our TEST and DEV servers as well as the properties on the associated tables. They are identical. Nothing has changed in the SQL either.

0 1
0 264

HI,

I have made a query with class definitions and all their properties and put them in a view.

All is good besides Parameters is showing junk characters. Is there a way to do it cleanly besides getting into the code??

SELECT
CC.ID As CompiledClass,
CC.SqlSchemaName,
CC.SqlTableName,
CP.Name As PropertyName,
CP.SqlFieldName,
CP.Type,
PD.Parameters

FROM %Dictionary.CompiledProperty CP
JOIN %Dictionary.CompiledClass CC
ON CP.Parent = CC.ID

0 11
0 344

Hi All,

I have two tables LB_TestSet and LB_Transfer

LB_Transfer has a list if TestSet row IDs, so i am doing the following join

SELECT 
LBTS_RowID,
,LBTS_CollectedDate
FROM SQLUser.LB_TestSet 
JOIN SQLUser.LB_Transfer ON ($LISTBUILD(LBTS_RowID) %INLIST LBTR_TestSetList)
WHERE LBTS_CollectedDate BETWEEN '2019-01-01' AND  '2019-05-10'

Without the date filter the query returns data, but if i add the date filter no data is returned

I also tried using %INTERNAL, %EXTERNAL TO_DATE('2009-01-01','YYYY-MM-DD')

0 2
0 479