Intersystems Cache coding query

Question

SELECT Distinct visitid As Visit_ID, 
       AreaId->FacilityID As Facility_ID, 
       visitid-PatientSecondaryNumber As Patient_MRN, 
       visitid->PatientName As Patient_Name, 
       visitid-statustext As visit_Status, 
       visitid->LastVisitTypeID->shortname As visit_Type,
       visitid-LastVisitActivationTime As Last_Visit_Activation,
       (SELECT VisitConversionID->VisitTypeID-shortname 
        FROM qcpr_arf_OC.VisitActivationTime 
        WHERE visitid = qcpr_arf_RG.AreaBedHistoryEventTime.visitid AND 
              VisitConversionID->VisitTypeID-shortname LIKE 'Emergency%' ) AS Last_Visit FROM qcpr_arf_rg.AreaBed INNER JOIN qcpr_arf_RG.AreaBedHistoryEventTime ON     
       qcpr_arf_rg.AreaBed.AreaBedID = qcpr_arf_RG.AreaBedHistoryEventTime.AreaBedID 
WHERE AreaBedHistoryEventTimeSubID LIKE 'Ç910%' AND visitid <> ''

Hi the above query Have been retain by a previous employee and I'm trying to figure out what "->" means could anyone please help me out.

Answer 1

To expand on @Ben's answer, and to include more information in this thread rather than just the external link he supplied.

-> syntax is a Cache SQL shorthand that represents an implicit LEFT OUTER JOIN in cases where a property is a reference to another table.

As an example your SQL query includes the following column in the SELECT clause: AreaId->FacilityID As Facility_ID This expression is equivalent to a LEFT OUTER JOIN with the table that AreaId references using ON {table.ROWID} = AreaID, and returning that FacilityID if such an AreaId exists, or NULL if it does not.

At first glance, the syntax may not make much sense, but it can reduce the amount of SQL in a query. That said, this query might be easier to follow if you made the JOIN on visitid explicit.

I am including @Ben's link to the InterSystems documentation: http://docs.intersystems.com/cache20141/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_specialfeatures#GSQL_specialfeatures_impjoin The focus of the documentation describes the behaviour of the feature from a more OO perspective, as well as giving some basic query rewrites illustrating the feature.

Answer 2

The -> syntax is an implicit join. See http://docs.intersystems.com/cache20141/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_specialfeatures#GSQL_specialfeatures_impjoin for a full explanation.