I have two sub-queries to get visit date and visit type that I combined into one outer apply. The sub-queries for visit date and visit type give me blanks when there is no visit, which is what I want. The outer apply gives me blanks for visit type but for date give me 01/01/1900.

I can solve the issue with a where fldvisitdate <> '19000101' but I'm wondering why the outer apply is working this way. here is the code of my two sub-queries.

  (SELECT TOP 1 fldVisitDate FROM tblVisit
inner join tblschedule on tblSchedule.fldScheduleVisitID =       tblvisit.fldvisitid
     WHERE fldVisitPrerepID = tblRunnerPrerepIDs.fldPrerepID
   and tblSchedule.fldScheduleVisitID = tblvisit.fldvisitid
    ORDER BY fldVisitDate)
      AS 'First Visit',

      (SELECT TOP 1
     FROM tblVisitTypes
     inner join tblVisit  ON tblVisit.fldVisitTypeID =    tblVisitTypes.fldVisitTypeID
     inner join tblschedule on tblSchedule.fldScheduleVisitID =    tblvisit.fldvisitid
     WHERE fldVisitPrerepIDID = tblRunnerPrerepIDs.fldPrerepID
    and tblSchedule.fldScheduleVisitID = tblpatientvisit.fldvisitid
  ORDER BY fldVisitDate)
   as 'First Visit Type',

       tblVisit.fldVisitDate, tblVisit.fldVisitDate,     tblVisitTypes.fldVisitTypeName
      FROM tblVisit
      inner join tblVisitTypes  ON tblVisit.fldVisitTypeID = tblVisitTypes.fldVisitTypeID
      inner join tblSchedule on tblSchedule.fldScheduleVisitID = tblVisit.fldvisitid
     WHERE fldVisitPrerepID = tblRunnerPrereps.fldPrerepID
     and tblSchedule.fldScheduleVisitID = tblVisit.fldvisitid
    ORDER BY fldVisitDate) as FirstVisitData

Of course earlier in the query I request a display of

      FirstVisitData.fldvisitdate as ‘First Visit’
      FirstVisitData.fldVisitTypeName as ‘First Visit Type’
  • First, the 2 original subqueries have different correlation conditions. The first one has and tblSchedule.fldScheduleVisitID = tblvisit.fldvisitid (which is a redundant repeat of the ON condition) but the 2nd has and tblSchedule.fldScheduleVisitID = tblpatientvisit.fldvisitid which is a correlated condition to the main query.

    So, the two subqueries may have been giving you NULL in some results but they are incorrect (one of them!) if they are supposed to give you related results.

  • Second and based on the (broken) results from the OUTER APPLY where the conditions from the first subquery is used, we can deduct that the 2nd subquery has the correct/wanted conditions.

  • Other points of trouble is the random formatting of the code, the mixing of upper and lower case SQL keywords and the lack of aliases which makes the code rather unreadable. The more the code in nicely formatted, the easier would be to catch similar errors.

  • Columns and table aliases should be quoted (if needed) with double quotes ("some alias") or square brackets ([some alias]) and not with single quotes. While this is allowed for column aliases, it is very confusing and error-prone, as single quotes are used for string literals.

  • Columns should always (unless in ORDER BY) be prefixed with the table name or alias. This is mostly fine in the code, except for one miss.

Based on the above, I'd rewrite the OUTER APPLY like this:

        v  . fldVisitDate, 
        vt . fldVisitTypeName
    FROM tblVisit AS v
        LEFT JOIN tblVisitTypes AS vt
            ON  v.fldVisitTypeID = vt.fldVisitTypeID
        INNER JOIN tblSchedule AS s
            ON  s.fldScheduleVisitID = v.fldvisitid
        -- replace "xxx" with table name or alias
        xxx.fldVisitPrerepID = tblRunnerPrereps.fldPrerepID
        s.fldScheduleVisitID = tblPatientVisit.fldvisitid
    AS FirstVisitData

and then double and triple check that the joining conditions are correct and matching business logic and the intention of the query.

All the above may still not resolve the weird results (1900-01-01) you get. It may be that they are caused due to the changed conditions (in which case, the rewrite will resolve it) or as @Duffy has commented they may due to some implicit conversion. The empty string '' when converted to DATE becomes by default 1900-Jan-01. A UNION can have this effect. Try this for example and you'll get the 2nd row as 1900-Jan-01:

SELECT getdate() AS SomeDate 

Without knowing what transformations are happening in the external query, we are not sure about that either nor why this happens only with the OUTER APPLY but not with the subqueries method.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.