i am trying to embed a dynamic SQL into Objectscript code , but the %NEXT() will return 0 , however when i copy the same query and run it in the SQL bit it will give me the result i want
the code :
set myquery = "SELECT"
set myquery = myquery_" $PIECE(Substring(M.Rawcontent,Charindex('PD1|',M.Rawcontent),1000),'|',4),"
set myquery = myquery_" H1.TargetConfigName,"
set myquery = myquery_" H.Timecreated,"
set myquery = myquery_" H.MessageBodyId"
set myquery = myquery_" FROM"
set myquery = myquery_" Ens.MessageHeader H"
set myquery = myquery_" left outer join Enslib_HL7.Message M ON (H.MessageBodyId=M.ID)"
set myquery = myquery_" left outer join Ens.MessageHeader H1 ON(H.SessionID=H1.SessionID AND H1.TargetBusinessType='BusinessOperation')"
set myquery = myquery_" left outer join Enslib_HL7.Message M1 ON (H1.MessageBodyId=M1.ID)"
set myquery = myquery_" WHERE"
set myquery = myquery_" H.Timecreated >= '2024-10-14 00:00:00.000' AND"
set myquery = myquery_" H.Timecreated <= '2024-10-29 00:00:00.000' AND"
set myquery = myquery_" H.SourceConfigName IN ('Epic ADT_Router') AND"
set myquery = myquery_" H1.TargetConfigName IN ('EMS - Outgoing ADT/Scheduling/Orders') AND"
set myquery = myquery_" LEN($PIECE(Substring(M.Rawcontent,Charindex('PD1|',M.Rawcontent),1000),'|',4)) > 4"
set myquery = myquery_" GROUP BY"
set myquery = myquery_" H1.TargetConfigName"
$$$LOGINFO(myquery)
set tStatement = ##class(%SQL.Statement).%New()
set qStatus = tStatement.%Prepare(myquery)
if qStatus'=1 {write "%Prepare failed:" do $System.Status.DisplayError(qStatus)}
set rset = tStatement.%Execute()
if (rset.%SQLCODE '= 0) {write "%Execute failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}
$$$LOGINFO(rset.%SQLCODE)
Do rset.%Display()
//$$$LOGINFO(rset.%Display())
$$$LOGINFO(rset.%Print("|"))
$$$LOGINFO("Next "_rset.%Next())
$$$LOGINFO("GETDATA "_rset.%GetData(1))
while rset.%Next(){
$$$LOGINFO("Print "_rset.%Print("^|^"))
$$$LOGINFO("ODS "_rset.ods)
$$$LOGINFO("GETDATA "_rset.%GetData(1))
}
here is the SQL runner results :
.png)
.png)