New post
AllAccepted answers
Robert Cemper · Dec 27, 2017

My sample took somewhat longer to prepare then Edward's smiley and Gerd's

#1) to evaluate SQL statements use MgmtPortal/ Explorer/SQL and check the generated query plan.

#2) if you don't use special attributes to SELECT clause or sub-queries all SQL statements are strictly worked from left to right.
 your first statement is ok for SQL your 2nd is just a fragment that I interpreted as to be a sub-query

Summary: they are not identical.

Now the example transformed for namespace SAMPLES to have 3 tables as A, B, and C:

select A.id,B.id,c.Id from
    sample.person A left join 
    sample.employee B on A.id = B.spouse 
    inner join sample.company C on B.company = C.id 
 

Row count: 100

Query Plan:

Relative cost = 4200
Read extent bitmap Sample.Employee.$Employee, looping on ID.
For each row:
     Read master map Sample.Employee.IDKEY, using the given idkey value.
    Read extent bitmap Sample.Person.$Person, using the given ID.
 For each row:
     Read master map Sample.Company.IDKEY, using the given idkey value.
 Output the row.
 

transformed to sub-queries:

select aid,bid,cid from
(select A.id aid, b.id bid, b.company cid 
    from sample.person A 
    left join sample.employee B
    on A.id = B.spouse)
 inner join sample.company C 
 on cid = C.id 

it is obviously identic ! just more expressive.

Row count: 100

Query Plan:

Relative cost = 4200
Read extent bitmap Sample.Employee.$Employee, looping on ID.
For each row:
     Read master map Sample.Employee.IDKEY, using the given idkey value.
    Read extent bitmap Sample.Person.$Person, using the given ID.
 For each row:
     Read master map Sample.Company.IDKEY, using the given idkey value.
 Output the row.

the next  LEFT_JOIN(A,INNER_JOIN(B.C))  requires immediate transformation

select A.id,bid,cid from
sample.person A left join
(select b.id as bid,c.id cid, b.spouse bsp 
 from sample.employee B
 inner join sample.company C on B.company = C.id) 
 on A.id = bsp

Row count:  237  (!!!!)

Query Plan:

Relative cost = 32408
Read extent bitmap Sample.Person.$Person, looping on ID.
For each row:
 Call module E.
 Read temp-file A, using the given VIEW column #3, and looping on VIEW counter.
 For each row:
 Generate a row padded with NULL for the view if no row qualified.
 Output the row.
module E
Read extent bitmap Sample.Employee.$Employee, looping on ID.
For each row:
 Read master map Sample.Employee.IDKEY, using the given idkey value.
 Read master map Sample.Company.IDKEY, using the given idkey value.
 Increment view row counter.
 Add a row to temp-file A, subscripted by VIEW column #3 and VIEW counter,
 with node data of VIEW column #1 and VIEW column #2.

So both variants are possible though the result is different

HTH,

Robert Cemper · Dec 20, 2017

Because it's so easy I've added a solution for those readers that don't refuse to work with Caché Objects & Classes .

Class Definition

Class DC.productListing Extends (%RegisteredObject, %XML.Adaptor)                            
{                                                                                                                                                                          
Property product As %String(CONTENT = "MIXED", XMLPROJECTION = "CONTENT");
}                                                                                                                                                                          

Simple display method

ClassMethod Show(xml As %String(MAXLEN=""))
{
 set rd=##class(%XML.Reader).%New()
    ,sc=rd.OpenString(xml)
 do rd.Correlate("product","DC.productListing")
 while rd.Next(.obj) {
    write !,"######"_$i(cnt)_"#########",$replace(obj.product,$c(10),$C(13,10))
 }
}

Extended test data

USER>write xml
<productListing title="ABC Products">
 <product>
   <name>Product One</name>
   <description>Product One is an exciting new widget that will simplify your life.</description>
   <cost>$19.95</cost>
   <shipping>$2.95</shipping>
 </product>
 <product>
   <name>Product 2</name>
   <description>Product 2 is an exciti</description>
   <cost>$19.95</cost>
   <shipping>$2.95</shipping>
 </product>
 <product>
   <name>Product 3</name>
   <description>Product 3 is simplify your life.</description>
   <cost>$19.95</cost>
   <shipping>$2.95</shipping>
 </product>
</productListing>
USER>

Result

USER>do ##class(DC.productListing).Show(xml)
 
######1#########
  <name>Product One</name>
  <description>Product One is an exciting new widget that will simplify your life.</description>
  <cost>$19.95</cost>
  <shipping>$2.95</shipping>
 
######2#########
  <name>Product 2</name>
  <description>Product 2 is an exciti</description>
  <cost>$19.95</cost>
  <shipping>$2.95</shipping>
 
######3#########
  <name>Product 3</name>
  <description>Product 3 is simplify your life.</description>
  <cost>$19.95</cost>
  <shipping>$2.95</shipping>
 
USER>
Robert Cemper · Dec 15, 2017

An alternate approach to SOAP:

a) you run your query over  JDBC on your DataServer
     so you can query what ever you like

b) slightly more sophisticated
    you create a ClassMethod with [SqlProc] parameter and also call it over JDBC
    like this.  SELECT Pkg.Class_ClassMethod()  
    It's totally up to you what the content of your return value is.   

I personally prefer JDBC as it's platform independent

in fact b) is pretty much the same as SOAP just with a different transport that avoids %CSP and its side effects.......

Robert Cemper · Dec 13, 2017

Proposal:

Store your JSON String as %String(MAXLEN="")  like a log file

for the typical components  type, age, firstname, lastname, ...
create calculated properties that find its content by $piece,  $find or some JSON specific stuff.
setup an  index on those calculated properties to find your record.

With this approach you keep the origin as you get it and get te indices you may require.
It's pretty similar to what I did to mimic an XML DB with Caché

so 1) =NO  2) =see above 3) =YES 4) = it's just another challenge

Robert Cemper · Dec 8, 2017

check side conditions of the related column.

from docs:

property onclick as %ZEN.Datatype.eventHandler;
onclick event handler: This event is fired when the mouse is clicked within a cell in this column. If the column does not have data associated with it you will have to set the linkCaption property.
Note that this callback is called before the new row in the table is selected so you will not have the current selectedIndex for the table when this callback is fired. If you need that, use the tablePane's onselectrow callback.
Robert Cemper · Dec 8, 2017

See details here:

http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

especially:

Sample Global Names and Their Uses

The following are examples of the various kinds of global names and how each is used:
your case
2) is a broken call for a label in a routine and has nothing to to with globals
probably        do a^myroutine
3) looks like a  broken condition on variable a also no globals around
a|  could be the beginning of a OR ....
Robert Cemper · Nov 30, 2017

You may take this approach:

create an object based on Schema1: My.Schema1
create an object based on Schema2: My.Schema2

HowTo: => XML Schema Wizzard 
http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

Then you may use this method.

Method GetCustomerInfo(schema1 as My.Schema1) As My.Schema2 [WebMethod]


My.Schema1 is checked as input parameter according tothe generated class
inside your Method you transform it to My.Schema2
and return it. 

The most tricky thing might be how to reply to a request that doesn't match Schema1.

Robert Cemper · Nov 27, 2017

I'd suggest to use $LB() if you are not hit by the LongString limit.

why: all packaging, selection,.... is already done and "hard wired" in C++ 
with $C() you may re-invent $LB() or $Piece() or similar. And you have to do it in COS.
Same applies to local array where  you may iterate over the structure in COS again.

Nothing against COS but C++ (in COS Functions) IS faster
 

Robert Cemper · Nov 26, 2017

A few recommendations:
#2) save you original Global as you do otherwise.  [Just to be Save]
#1)  In Studio Copy class has a checkbox to copy Storage Definition (=Globals) . Set it.
  In the new class add ClassParameter MANAGEDEXTENT=0 ;

/// The <var>MANAGEDEXTENT</var> parameter can be set to 0 (zero) to cause the Extent Manager
/// to ignore this class. If set to 1 then the Extent Manager will register globals used by
/// the class and detect collisions. Unmanaged extents (MANAGEDEXTENT = 0) are not checked.
/// Currently, only classes using default storage (%Library.CacheStorage) can be managed.
Parameter MANAGEDEXTENT As INTEGER [ Constraint = "0,1", Flags = ENUM ] = 1;
 

So the old and the new class use the same Globals.
Now go on with #3  to #6

If you miss something in refactoring the damage should be limited as you just change names not the Storage in Globals.
Assuming the change of ClassName is all you do.

Robert Cemper · Nov 24, 2017

For %BuildIndices you may provide a list  or use default. it doesn't affect sequence

example from Sample.Person.1.Int

%BuildIndices(pIndexList="",pAutoPurge=1,pLockFlag=0,pJournalFlag=1,pStartID="",pEndID="",pIgnoreIndexList="") public {
if ($listlength(pIndexList)=1)&&($listget(pIndexList,1)="") { return 1 }
Set class=$classname(),tBuildFlags=1,tBuildFlags(class)=$c(0,0,0,0,0,0)
Set tPtr=0,tStatus=1,pJournalFlag=''pJournalFlag
While $listnext(pIndexList,tPtr,tIndex) {
continue:tIndex=""
If '$d(^oddCOM(class,"i",tIndex)) {
Set tStatus=$select(+tStatus:$$Error^%apiOBJ(5066,class_"::"_tIndex),1:$$AppendStatus^%occSystem(tStatus,$$Error^%apiOBJ(5066,class_"::"_tIndex)))
}
}
If ('tStatus) RETURN tStatus
if $listfind(pIndexList,"$Person") { set $Extract(tBuildFlags(class),1)=$c(1) }
if ((pIndexList="")||($listfind(pIndexList,"FCOL"))),'$listfind(pIgnoreIndexList,"FCOL") { set $Extract(tBuildFlags(class),2)=$c(1) }
if ((pIndexList="")||($listfind(pIndexList,"NameIDX"))),'$listfind(pIgnoreIndexList,"NameIDX") { set $Extract(tBuildFlags(class),3)=$c(1) }
if ((pIndexList="")||($listfind(pIndexList,"SSNKey"))),'$listfind(pIgnoreIndexList,"SSNKey") { set $Extract(tBuildFlags(class),4)=$c(1) }
if ((pIndexList="")||($listfind(pIndexList,"ZipCode"))),'$listfind(pIgnoreIndexList,"ZipCode") { set $Extract(tBuildFlags(class),5)=$c(1) }
if ((pIndexList="")||($listfind(pIndexList,"dobx"))),'$listfind(pIgnoreIndexList,"dobx") { set $Extract(tBuildFlags(class),6)=$c(1) }
If ((pIndexList="")||($Ascii(tBuildFlags(class),5))) && '$listfind(pIgnoreIndexList,"$Person") { set $extract(tBuildFlags(class),1)=$c($select((pStartID'="")||(pEndID'=""):1,1:2)) }
Quit ..%BuildIndexInternal(.pLockFlag,.pAutoPurge,.pStartID,.pEndID,.pIndexList,.tBuildFlags,"^Sample.PersonD",1,.pJournalFlag,0) }
 
 

%Save does it straight by ASSCII sorting of index names (lower after Upper case)

See %SaveData(id)  in the generated .int 

Robert Cemper · Nov 23, 2017

as documented in
http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

DATEPART delivers a %Integer value.
Differently to %Date which has different display, logical and ODBC presentation %Integer does not follow any specific presentation but is just Integer and nothing else.
And your input to DATEPART function is in any example the same: the logical value of TimeCreated.

To  cover your expectations you would need to adapt your input yourself. eg by %ODBCOUT() function.
or the related LogicalToDisplay function.

Your expectation that a data type %Integer does a revers research how it was calculated is just un-realistic.

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.