Not sure if this could help - Just adding here to see if this was tried -

Hope you have the SQL Gateway connections setup from Management portal.

In STUDIO -> Create a class and then try executing the following inside a Class Method / method

ClassMethod  DataWarehouseFetch() As %Status


Set Status=$$$OK

Set SQL= 7

Set SQL(1)= "SELECT pt.Column1, pt.Column2, pt.Column3,  pt.Column4,  pt.Column5,  pt.Column6,  pt.Column7,  pt.Column8,  pt.Column9,  pt.Column10,  pt.Column11,  tr.Column12,  tr.Column13,  tr.Column14,  te.Column15,  te.Column16,  te.Column17,  te.Column18,  te.Column19,  te.Column20,  rs.Column21,  rs.Column22,  rs.Column23,  re.Column24,  re.Column25,  re.Column26,  tr.Column27,  tr.Column28,  re.Column29 "

Set SQL(2)=         "FROM Database1.Table1 tr "

Set SQL(3)=           "LEFT JOIN Database1.Table2 te on te.Column16 = tr.Column13"

Set SQL(4)=           "LEFT JOIN Database1.Table3 rs on rs.Column23 = tr.Column28"

Set SQL(5)=            "LEFT JOIN Database1.Table4 re on re.Column25 = rs.Column22"

Set SQL(6)=            "LEFT JOIN Database1.Table5 pt on pt.Column6 = re.Column26"

Set SQL(7)=         "WHERE pt.Column10 = '2018-10-30'"


Set Status= Statement.%Prepare(.SQL)
If $$$ISERR(Status) $$$ThrowOnError(" PREPARE issue")
Set tResults=Statement.%Execute()

While (tResults.%Next())

      //w tResults.Column1,!

      //w tResults.%Get("Column2"),!

      //w tResults.Get("Column2"),!


Quit Status


Thank you Robert that did help me..
I also found these options below -

s Status=##class(%ZEN.Auxiliary.jsonSQLProvider).%WriteJSONFromSQL("json","SELECT ID, Column1, Column2, DataID FROM SQLUser.SampleData")
-- or --
    s Status=##class(%ZEN.Auxiliary.jsonSQLProvider).%WriteJSONStreamFromSQL(.pStream,"SELECT ID, Column1, Column2, DataID FROM SQLUser.SampleData")

    While 'pStream.AtEnd { Write pStream.ReadLine(,.sc,.eol) If $$$ISERR(sc) { Write "Error" Quit } If eol { Write ! } }