Executing Dynamic SQL as Execute Querry

I am trying to replace one of our SQL Integration Service jobs with Ensemble and I am running into an issue executing a query against a MS SQL database using JDBC drivers.

My SQL Outbound code looks like this.

 Method SelectPaceartCharges(pRequest As osuwmc.Paceart.DataStructures.SelectPaceartBillingExtract, Output pResponse As EnsLib.SQL.Snapshot) As %Status [ ReturnResultsets ]
{

    set query = "declare @currentYear int=datepart(year,getdate()) "
    set query =query_" declare @secondSundayOfMar datetime = CAST('3/8/' + CAST(@currentYear as varchar) as datetime)"
    set query =query_" declare @firstSundayOfNov datetime = CAST( '11/1/' + CAST(@currentYear as varchar) as datetime)"
    set query =query_" --find first sunday"
    set query =query_" while( DATENAME(WEEKDAY,@secondSundayOfMar) != 'Sunday' )"
    set query =query_" begin"
    set query =query_" set @secondSundayOfMar = DATEADD(day,1,@secondSundayOfMar)"
    set query =query_" end"
    set query =query_" --find last sunday of october"
    set query =query_" while( DATENAME(WEEKDAY,@firstSundayOfNov) != 'Sunday' )"
    set query =query_" begin"
    set query =query_" set @firstSundayOfNov = DATEADD(day,-1,@firstSundayOfNov)"
    set query =query_" end "
    set query =query_" declare @gmt int= 0"
    set query =query_" declare @currentDate datetime = getDate()"
    set query =query_" --for EST"
    set query =query_" if ( @currentDate >= @secondSundayOfMar AND @currentDate < @firstSundayOfNov )"
    set query =query_" set @gmt = -4"
    set query =query_" else"
    set query =query_" set @gmt = -5"
    set query =query_" "
    set query =" SELECT "
    set query =query_" ltrim(rtrim(ISNULL(Paceart.ENCOUNTER.VisitId,''))) as CSN, "
    set query =query_" ltrim(rtrim(ISNULL(Paceart.PATIENT_ID.PatientID,''))) as MRN,"
    set query =query_" ltrim(rtrim(Paceart.PATIENT.LastName)) as LastName, "
    set query =query_" ltrim(rtrim(Paceart.PATIENT.FirstName)) as FirstName,"
    //set query =query_" DATEADD(HH,@gmt,Paceart.ENCOUNTER_SIGNATURE.SignatureDate) as SignatureDate, "
    set query =query_" ltrim(rtrim(ISNULL(Paceart.PROVIDER.ProviderCode,''))) as BillingProv_DoctorNumber,"
    set query =query_" ltrim(rtrim(ISNULL(Paceart.PROVIDER.LastName,''))) as BillingProv_LastName,"
    set query =query_" ltrim(rtrim(ISNULL(Paceart.PROVIDER.FirstName,''))) as BillingProv_FirstName,"
    set query =query_" ltrim(rtrim(Paceart.LOCATION_OF_CARE.Abbreviation)) as Abbreviation, "
    set query =query_" ltrim(rtrim(Paceart.LOCATION_OF_CARE.Name)) as Name,"
    set query =query_" ltrim(rtrim(ISNULL(Paceart.CHARGE_CODE.ExternalCode,''))) as Hosp_ChargeCode,"
    set query =query_" ltrim(rtrim(ISNULL(replace(Paceart.CHARGE_CODE.Description,'&','and'),''))) as Hosp_ChargeCodeDescription,"
    set query =query_" ltrim(rtrim(ISNULL(Paceart.CPT_CODE.Code,''))) AS CPT_Code, "
    set query =query_" ltrim(rtrim(ISNULL(Paceart.CPT_CODE.Description,''))) AS CPT_Description,"
    set query =query_" ltrim(rtrim(ISNULL(CPT2_CODE.Code,''))) as CPT_Code2,"
    set query =query_" ltrim(Rtrim(ISNULL(CPT2_CODE.Description,''))) as CPT2_Description,"
    set query =query_" ltrim(rtrim(ISNULL(CPT3_CODE.Code,''))) as CPT_Code3,"
    set query =query_" ltrim(rtrim(ISNULL(CPT3_CODE.Description,''))) as CPT3_Description,"
    set query =query_" ltrim(rtrim(ISNULL(CPT4_CODE.Code,''))) as CPT_Code4,"
    set query =query_" ltrim(rtrim(ISNULL(CPT4_CODE.Description,''))) as CPT4_Description,"
    set query =query_" ltrim(rtrim(ISNULL(DIAG0.Code,''))) as Diagnosis1, "
    set query =query_" ltrim(rtrim(ISNULL(DIAG0.Description,''))) as Diagnosis1_Description, "
    set query =query_" ltrim(rtrim(ISNULL(DIAG1.Code,''))) as Diagnosis2, "
    set query =query_" ltrim(rtrim(ISNULL(DIAG1.Description,''))) as Diagnosis2_Description, "
    set query =query_" ltrim(rtrim(ISNULL(DIAG2.Code,''))) as Diagnosis3, "
    set query =query_" ltrim(rtrim(ISNULL(DIAG2.Description,''))) as Diagnosis3_Description, "
    set query =query_" ltrim(rtrim(ISNULL(DIAG3.Code,''))) as Diagnosis4, "
    set query =query_" ltrim(rtrim(ISNULL(DIAG3.Description,''))) as Diagnosis4_Description,"
    set query =query_" Case"
    set query =query_" WHEN Ref.ProviderID IS NULL AND Ref2.ProviderID IS NOT NULL THEN Ref2.ProviderID"
    set query =query_" WHEN Ref.ProviderID IS NULL AND Ref2.ProviderID IS NULL AND Ref3.ProviderID IS NOT NULL THEN Ref3.ProviderID"
    set query =query_" WHEN Ref.ProviderID IS NULL AND Ref2.ProviderID IS NULL AND Ref3.ProviderID IS NULL THEN NULL "
    set query =query_" ELSE Ref.ProviderID END as ReferringProvider,"
    set query =query_" Case"
    set query =query_" WHEN Ref.ProviderID IS NULL AND Ref2.ProviderID IS NOT NULL THEN Ref2.LastName"
    set query =query_" WHEN Ref.ProviderID IS NULL AND Ref2.ProviderID IS NULL AND Ref3.ProviderID IS NOT NULL THEN Ref3.LastName"
    set query =query_" WHEN Ref.ProviderID IS NULL AND Ref2.ProviderID IS NULL AND Ref3.ProviderID IS NULL THEN NULL "
    set query =query_" ELSE Ref.LastName END as ReferringProvider_LastName,"
    set query =query_" Case"
    set query =query_" WHEN Ref.ProviderID IS NULL AND Ref2.ProviderID IS NOT NULL THEN Ref2.FirstName"
    set query =query_" WHEN Ref.ProviderID IS NULL AND Ref2.ProviderID IS NULL AND Ref3.ProviderID IS NOT NULL THEN Ref3.FirstName"
    set query =query_" WHEN Ref.ProviderID IS NULL AND Ref2.ProviderID IS NULL AND Ref3.ProviderID IS NULL THEN NULL "
    set query =query_" ELSE Ref.FirstName END as ReferringProvider_FirstName,"
    set query =query_" CASE"
    set query =query_" WHEN Paceart.encounter.inpatientstatus = 1 then 'I'"
    set query =query_" else 'O' end as PatientClass"
    set query =query_" FROM Paceart.ENCOUNTER LEFT OUTER JOIN"
        set query =query_" Paceart.PATIENT ON Paceart.ENCOUNTER.PatientKey = Paceart.PATIENT.Patientkey LEFT OUTER JOIN"
        set query =query_" Paceart.ENCOUNTER_SIGNATURE ON Paceart.ENCOUNTER.EncounterKey = Paceart.ENCOUNTER_SIGNATURE.EncounterKey LEFT OUTER JOIN"
        set query =query_" Paceart.PATIENT_ID ON Paceart.PATIENT.Patientkey = Paceart.PATIENT_ID.Patientkey LEFT OUTER JOIN"
        set query =query_" Paceart.BILLING ON Paceart.ENCOUNTER.EncounterKey = Paceart.BILLING.EncounterKey LEFT OUTER JOIN"
        set query =query_" Paceart.BILLING_CPT_CODE ON Paceart.BILLING.BillingKey = Paceart.BILLING_CPT_CODE.BillingKey and Paceart.BILLING_CPT_CODE.Position = 0 LEFT OUTER JOIN"
        set query =query_" Paceart.BILLING_DIAGNOSIS BILL_DIAG0 ON Paceart.BILLING.BillingKey = BILL_DIAG0.BillingKey and BILL_DIAG0.Position = 0 LEFT OUTER JOIN "
        set query =query_" Paceart.CPT_CODE ON Paceart.BILLING_CPT_CODE.CPTCodeKey = Paceart.CPT_CODE.CPTCodeKey LEFT OUTER JOIN "
        set query =query_" Paceart.DIAGNOSIS as DIAG0 ON BILL_DIAG0.DiagnosisKey = DIAG0.DiagnosisKey LEFT OUTER JOIN "
        set query =query_" Paceart.LOCATION_OF_CARE on Paceart.ENCOUNTER.LocationOfCareKey = Paceart.LOCATION_OF_CARE.LocationOfCareKey LEFT OUTER JOIN "
        set query =query_" Paceart.BILLING_DIAGNOSIS BILL_DIAG1 ON Paceart.BILLING.BillingKey = BILL_DIAG1.BillingKey and BILL_DIAG1.Position = 1 LEFT OUTER JOIN "
        set query =query_" Paceart.DIAGNOSIS as DIAG1 ON BILL_DIAG1.DiagnosisKey = DIAG1.DiagnosisKey LEFT OUTER JOIN "
        set query =query_" Paceart.BILLING_DIAGNOSIS BILL_DIAG2 ON Paceart.BILLING.BillingKey = BILL_DIAG2.BillingKey and BILL_DIAG2.Position = 2 LEFT OUTER JOIN "
        set query =query_" Paceart.DIAGNOSIS as DIAG2 ON BILL_DIAG2.DiagnosisKey = DIAG2.DiagnosisKey LEFT OUTER JOIN"
        set query =query_" Paceart.BILLING_DIAGNOSIS BILL_DIAG3 ON Paceart.BILLING.BillingKey = BILL_DIAG3.BillingKey and BILL_DIAG3.Position = 3 LEFT OUTER JOIN "
        set query =query_" Paceart.DIAGNOSIS as DIAG3 ON BILL_DIAG3.DiagnosisKey = DIAG3.DiagnosisKey LEFT OUTER JOIN "
        set query =query_" Paceart.CHARGE_CODE ON Paceart.BILLING.ChargeCodeKey = Paceart.CHARGE_CODE.ChargeCodeKey LEFT OUTER JOIN "
        set query =query_" Paceart.PROVIDER on Paceart.BILLING.ProviderKey = Paceart.PROVIDER.ProviderKey LEFT OUTER JOIN "
        set query =query_" Paceart.BILLING_CPT_CODE as CPT2 ON Paceart.BILLING.BillingKey = CPT2.BillingKey and CPT2.Position = 1 LEFT OUTER JOIN "
        set query =query_" Paceart.CPT_CODE CPT2_CODE ON CPT2.CPTCodeKey = CPT2_CODE.CPTCodeKey LEFT OUTER JOIN "
        set query =query_" Paceart.BILLING_CPT_CODE as CPT3 ON Paceart.BILLING.BillingKey = CPT3.BillingKey and CPT2.Position = 2 LEFT OUTER JOIN "
        set query =query_" Paceart.CPT_CODE CPT3_CODE ON CPT3.CPTCodeKey = CPT3_CODE.CPTCodeKey LEFT OUTER JOIN "
        set query =query_" Paceart.BILLING_CPT_CODE as CPT4 ON Paceart.BILLING.BillingKey = CPT4.BillingKey and CPT4.Position = 3 LEFT OUTER JOIN "
        set query =query_" Paceart.CPT_CODE CPT4_CODE ON CPT4.CPTCodeKey = CPT4_CODE.CPTCodeKey INNER JOIN "
    set query =query_" Paceart.PATIENT_ID_TYPE as IDType on Paceart.PATIENT_ID.PatientIDTypeKey = IDType.PatientIDTypeKey LEFT OUTER JOIN "
    set query =query_" (SELECT Patient.Patientkey,ProviderID,RefProv.LastName, RefProv.FirstName "
    set query =query_" FROM Paceart.PATIENT as Patient INNER JOIN Paceart.PATIENT_PROVIDER as Referring on Patient.Patientkey = Referring.Patientkey INNER JOIN "
    set query =query_" Paceart.PROVIDER_TYPE on Referring.ProviderTypeKey = Paceart.PROVIDER_TYPE.ProviderTypeKey and Paceart.PROVIDER_TYPE.Code = 'REFERRING1' INNER JOIN "
    set query =query_" Paceart.PROVIDER as RefProv on Referring.ProviderKey = RefProv.ProviderKey) as Ref on Paceart.PATIENT.Patientkey = Ref.Patientkey LEFT OUTER JOIN "
    set query =query_" (SELECT Patient.Patientkey,ProviderID,RefProv.LastName, RefProv.FirstName "
    set query =query_" FROM Paceart.PATIENT as Patient INNER JOIN Paceart.PATIENT_PROVIDER as Referring on Patient.Patientkey = Referring.Patientkey INNER JOIN "
    set query =query_" Paceart.PROVIDER_TYPE on Referring.ProviderTypeKey = Paceart.PROVIDER_TYPE.ProviderTypeKey and Paceart.PROVIDER_TYPE.Code = 'REFERRING2' INNER JOIN "
    set query =query_" Paceart.PROVIDER as RefProv on Referring.ProviderKey = RefProv.ProviderKey) as Ref2 on Paceart.PATIENT.Patientkey = Ref2.Patientkey LEFT OUTER JOIN "
    set query =query_" (SELECT Patient.Patientkey,ProviderID,RefProv.LastName, RefProv.FirstName "
    set query =query_" FROM Paceart.PATIENT as Patient INNER JOIN Paceart.PATIENT_PROVIDER as Referring on Patient.Patientkey = Referring.Patientkey INNER JOIN "
    set query =query_" Paceart.PROVIDER_TYPE on Referring.ProviderTypeKey = Paceart.PROVIDER_TYPE.ProviderTypeKey and Paceart.PROVIDER_TYPE.Code = 'REFERRING3' INNER JOIN "
    set query =query_" Paceart.PROVIDER as RefProv on Referring.ProviderKey = RefProv.ProviderKey) as Ref3 on Paceart.PATIENT.Patientkey = Ref3.Patientkey " 
    set query =query_" WHERE (IDType.Description = 'OSUMRN')AND (Paceart.ENCOUNTER_SIGNATURE.SignatureUserId IS NOT NULL) and SUBSTRING(Paceart.PATIENT_ID.PatientID,1,1) like '[0-9]%'and SignatureDate > '2017-10-28 00:00:00.000' and SignatureDate < '2017-10-29 00:00:00.000' "
    set query =query_" order by Paceart.ENCOUNTER_SIGNATURE.SignatureDate"
    
    $$$LOGINFO(query)
    $$$LOGINFO("Query Length = "_$Length(query))
    
    set tSC = ..Adapter.ExecuteQuery(.rs,query)
    
    $$$LOGINFO("tSC = "_tSC)
    
    set tSC = rs.GetSnapshot(.pResponse)
    quit tSC
}

have set the query property to a max length of 10000.

Property query As %String(MAXLEN = 10000);

This query executes fine in SQL , and SQuirrel SQL Client(JDBC) Client without any issues, however when Ensemble executes it nothing is returned. Am I hitting a limitation with ExecuteQuery that I do not know about?

Thanks

Scott Roth

Ohio State University Wexner Medical Center