Thanks Marc,

that was exactly what I was trying to say, you just explained it much better now

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 

spObservationOS(maxDate As String(MAXLEN=1))

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.