New post
AllAccepted answers
Robert Cemper · Nov 19, 2017

A Total different idea.

if Transaction.Account is defined as Account object (could also be calculated)
     [ Property Account as Account ;   ]

then you may use implicit join for your query. 
it looks like this:

select Account.Name, Account.State, Transaction.Amt, Transaction.Date, Transaction.Service
from Transaction 
left join Account 
    on Account.Id = Transaction.Account  
where Transaction.Account in ( 
    Select Account.Id
     from Account
     where Transaction.Account ->Type is not null
     and Transaction.Account->Id>123456789        
     and Transaction.Account->Id <=323456789     
  )   
and Transaction.Date >= ?
and Transaction.Date <= ?

 
Robert Cemper · Nov 19, 2017

OK, that looks better smiley

now go to Mgmt Portal /SQL and verify for both tables that you see values in column Selectivity (marked)

IF THERE IS NO SELECTIVITY ANY QUERY PLAN IS JUST GUESSWORK.

if this is empty Query Generator just can guess and do a lot of unnecessary extra work.

so got Tune Table click it

and this you get there information that the Query Generator allows to make useful optimizations (marked)

Next enter your specific  query and click to "Show Plan"
that marked information tells you what is happening and Relative Cost qualifies the expected performance. 

This query plan tells you what is really happening.

Robert Cemper · Nov 19, 2017

At  first sight I'd say the Query Generator is right as your Sub Select Just adds some more WHERE conditions.
Your range on Transction.Date  with related index might be much more limiting than your range on Account.Id.
A index on Account.Type might also speed up your query.
 

where Account.Id in (
    Select Account.Name
     from Account

?? Is Account.Name the same as Account.Id  ???  
IN ( ) expects EXACT VALUES !

Suggestion if no done yet: Run tune Table for both tables

Next: publish the generated Query plan.
 

Robert Cemper · Nov 16, 2017

I assume you know where you get your email address from.
The rest is straight COS and your code my look like this


set myEmail=.......      ;wherever you get it from your PID-13-4
set msg=..CreateTextMessage(myEmail)   
ClassMethod CreateTextMessage(toMail) As %Net.MailMessage
{
 Set msg = ##class(%Net.MailMessage).%New()
 Set msg.From = "test@test.com"
 Do msg.To.Insert(toMail)
 Do msg.Cc.Insert("yyy@yyy.com")
 Do msg.Bcc.Insert("zzz@zzz.com")
 Set msg.Subject="subject line here"
 Set msg.IsBinary=0
 Set msg.IsHTML=0
 Do msg.TextData.Write("This is the message.") 
 Quit msg
}
Robert Cemper · Nov 15, 2017

A dirty hack:

- make a new class extending %SerialObject with  VALIDIFNULL  set.
- export your serial classes (to XML)
- replace %SerialObject by your MySerialObject
- reload the changed classes.

Not so nice but I had the same issue with some 100 Serial Objects.

The more sophisticated way would be do it programatically over %Dictionary.DefinedProperty  ..... 
Very interesting and very time consuming  

Robert Cemper · Nov 15, 2017

Kevin,

there a bunch of excellent examples in standard classes e.g
%Regex.Matcher
http://docs.intersystems.com/latest/csp/documatic/%25CSP.Documatic.cls?P...

%Net.HttpRequest
http://docs.intersystems.com/latest/csp/documatic/%25CSP.Documatic.cls?P...

%Net.SMTP
http://docs.intersystems.com/latest/csp/documatic/%25CSP.Documatic.cls?P...

And they are also in source on your system.
So it's easy to see how special Tags are used.

Robert Cemper · Nov 15, 2017

Welcome back!

infile  ; simple file read
  set filename="C\mydir\myfile.txt"
  set $ZTRAP="end"
  open filename:("R"):0 else  Write "file error"
  for line=1:1 use filename read text use 0 write text,! 
end
 close filename
  set $ZTRAP=""
  use 0 write "Done",
  quit

it's not so sophisticated and I used the end-of-file error to exit

This is also available in class %Library.File with lot more comfort 
http://docs.intersystems.com/latest/csp/documatic/%25CSP.Documatic.cls?P...

HTH
 

Robert Cemper · Nov 15, 2017

If you don't want / need the content as object and just want to convert XML2JSON
why wasting time and energy to re-invent the wheel an not just using any of the many downloadable tools
and call them over $ZF(-2)  and consume the result ?
Google gave my some thousand hits of tested solutions e.g. https://github.com/sinelaw/xml-to-json

I mean it's doable with Caché but file_in => file_out is not more than a nice exercise for training.