Based in the US for a fully remote, permanent role. Applicants must have development/programming experience with Cache Object Script language.
Contact:
Matthew Churchill, matt.churchill@arcaresourcing.com
Create a local copy of a linked table
As foreign tables are behind a paywall (booo); we have a external cache system using the intersystems ODBC driver or usually a .jar CacheDB.jar.
The requirement is :
"Create a copy of the external table once a day to perform comparisons to detect changes"
We could go full code and this is what we will do but trying the following should ideally work
CREATE TABLE Sample.YoungPeopletwo AS
SELECT *
FROM Pennine_TIE_Clinicom_Link.PMISPECIALREGNCA
WITH STORAGETYPE = COLUMNARThis resulted in
[SQLCODE: <-400>:<Fatal error occurred>]
[%msg: <Exception caught during dSQL statement %Execute: <UNDEFINED> SQLCODE>]if we created a table and tested inserting the values '1','2','3' and it worked
But if we tried at once with a single statment with a select into (1 value) it fails
INSERT INTO Sample.YoungPeople (PASReligionCode) values (SELECT internalPatientNumber FROM Pennine_TIE_Clinicom_Link.PMISPECIALREGNCA where InternalPatientNumber=100)with 1 or all 3 fields popluated we get
SQLCODE: -12
Message: A term expected, beginning with either of: identifier, constant, aggregate, $$, (, :, +, -, %ALPHAUP, %EXACT, %MVR %SQLSTRING, %SQLUPPER, %STRING, %TRUNCATE, or %UPPER^ INSERT INTO Sample . YoungPeople ( PASReligionCode ) VALUES ( SELECTIt is frustrating linking a cache db doens't seem to work right out of the box and the fact linked tables which seems a linked tables but actually work is behind a paid feature.
Any ideas of anything to work around the issues other than the plan to go to (complete dummy code i know none is syntax correct)
TStart
//Get resultset
set rs=....
While rs.%Next{
set sc-&sql (insert into copy table () values rs.1,rs.2...)
if sc not ok TRollback.... quit
}
if sc is ok TCommit