Hi Kevin,
here is an example of my procedure
create procedure bidata.spObservationOS(IN maxDate VARCHAR) RESULT SETS
LANGUAGE OBJECTSCRIPT
{
do $system.SQL.Execute("TRUNCATE table bidata.ObservationsStg")
do $system.SQL.Execute("INSERT %NOINDEX into bidata.ObservationsStg (ID, IssuedTime, Key, ObservationCategoryCode, ObservationCategoryDisplay, ObservationCode, ObservationCodeText, ObservationDisplay, ObservationEncounterReference, ObservationIdentifier, ObservationLastUpdated, ObservationSpecimenReference, ObservationStatus, ObservationSubjectReference, ObservationValueCode, ObservationValueDisplay, ObservationValueText, PerformerDisplay, PerformerIdentifier) select ID, IssuedTime, Key, ObservationCategoryCode, ObservationCategoryDisplay, ObservationCode, ObservationCodeText, ObservationDisplay, ObservationEncounterReference, ObservationIdentifier, ObservationLastUpdated, ObservationSpecimenReference, ObservationStatus, ObservationSubjectReference, ObservationValueCode, ObservationValueDisplay, ObservationValueText, PerformerDisplay, PerformerIdentifier FROM BIProjection.Observation where ObservationLastUpdated > maxDate")
do $system.SQL.Execute("BUILD INDEX FOR TABLE bidata.ObservationsStg")
do $system.SQL.Execute("UPDATE bidata.Observations set p.IssuedTime = s.IssuedTime, p.ObservationCategoryCode = s.ObservationCategoryCode, p.ObservationCategoryDisplay = s.ObservationCategoryDisplay, p.ObservationCode = s.ObservationCode, p.ObservationCodeText = s.ObservationCodeText, p.ObservationDisplay = s.ObservationDisplay, p.ObservationEncounterReference = s.ObservationEncounterReference, p.ObservationIdentifier = s.ObservationIdentifier, p.ObservationLastUpdated = s.ObservationLastUpdated, p.ObservationSpecimenReference = s.ObservationSpecimenReference, p.ObservationStatus = s.ObservationStatus, p.ObservationSubjectReference = s.ObservationSubjectReference, p.ObservationValueCode = s.ObservationValueCode, p.ObservationValueDisplay = s.ObservationValueDisplay, p.ObservationValueText = s.ObservationValueText, p.PerformerDisplay = s.PerformerDisplay, p.PerformerIdentifier = s.PerformerIdentifier from bidata.Observations p join bidata.ObservationsStg s on p.ID = s.ID")
do $system.SQL.Execute("INSERT %NOINDEX into bidata.Observations (ID, IssuedTime, Key, ObservationCategoryCode, ObservationCategoryDisplay, ObservationCode, ObservationCodeText, ObservationDisplay, ObservationEncounterReference, ObservationIdentifier, ObservationLastUpdated, ObservationSpecimenReference, ObservationStatus, ObservationSubjectReference, ObservationValueCode, ObservationValueDisplay, ObservationValueText, PerformerDisplay, PerformerIdentifier) SELECT ID, IssuedTime, Key, ObservationCategoryCode, ObservationCategoryDisplay, ObservationCode, ObservationCodeText, ObservationDisplay, ObservationEncounterReference, ObservationIdentifier, ObservationLastUpdated, ObservationSpecimenReference, ObservationStatus, ObservationSubjectReference, ObservationValueCode, ObservationValueDisplay, ObservationValueText, PerformerDisplay, PerformerIdentifier FROM bidata.ObservationsStg where ID not in (select ID from bidata.Observations)")
do $system.SQL.Execute("BUILD INDEX FOR TABLE bidata.Observations")
do $system.SQL.Execute("UPDATE BIData.seqRunLog set LastRunTime = (select max(ObservationLastUpdated) from BIData.ObservationsStg) where processName = 'Observation'")
}
in the studio it looks like
I had to manually change the maxlen
Hi Vitaliy,
I'm not using the TRUNCATE parameter, the class is created automatically with the create table.
I'm trying to raise an awareness to the different behavior between the JDBC/ODBC and the SQL GUI.
Thanks Marc,
that was exactly what I was trying to say, you just explained it much better now