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 = COLUMNAR
This 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 ( SELECT
It 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
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