Question
· Nov 3, 2017

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

Discussion (4)1
Log in or sign up to continue

Scott

Not that I have seen a lot of outbound adaptor code, but I have never see TSQL and a query sent over the wire like this.

If you just send the SELECT does it work?

are you getting any info back from the execute you are logging?

$$$LOGINFO("tSC = "_tSC)

THe next set would be to look at Java Gateway log or any logging SQL Server might have to see what we are sending over to SQL Server.

If none of this point to the issue I would suggest opening a WRC issue and getting one of our Ensemble  to have a look.