All
Accepted answers
Bharathsimha Reddy Jakka · Apr 18, 2018 go to post

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 ! } }

Bharathsimha Reddy Jakka · Oct 30, 2018 go to post

Hi,

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

}

Bharathsimha Reddy Jakka · Sep 28, 2021 go to post

Thank you for the details.. we have only one global that has node specific mapping while most of them are whole globals and the solution suggested above works.

For the single node since our data part varies across namespace it is evident of being sourced locally.

Thank you so much.