getting multivalue from a function

• ashkan sirous

  SSCrazy

  Points: 2733

  More actions

  November 29, 2011 at 9:03 pm

  #1414422

  I have Changed my query to avoid cross apply because of the efficiency and I want my query to be like this:

  select ... from #The Huge Query#

  left join (select top 1 PurposeTrans.POIDescription ,PurposeTrans.POIid ,

  ReqTrans.RequestCode, ReqTrans.RequestNo,

  ReqTrans.CreateDate ,reqDocListTrans.DoclistID , reqDocListTrans.DocRevID

  from DMSRequestDocListTbl reqDocListTrans

  inner join DMSRequestTbl ReqTrans on reqDocListTrans.RequestCode = ReqTrans.RequestCode

  and ReqTrans.RequestTypeID in ( 3 ,6)

  inner join DMSPurposeOfIssueTbl PurposeTrans on reqDocListTrans.POIid = PurposeTrans.POIid

  where reqDocListTrans.DocRevID = DocRevTbl.DocRevID

  order by ReqTrans.RequestCode desc) Trans on Trans.DocRevID = DocRevTbl.DocRevID

  The problem is " It cant Define the DocRevTbl.DocRevID which is define in The Huge Query...what should I do? 🙁 any Idea?

  Best Regards,
  Ashkan

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  November 30, 2011 at 2:09 am

  #1414491

  ashkan siroos (11/29/2011)

  ---

  I have Changed my query to avoid cross apply because of the efficiency ...

  CROSS/OUTER APPLY can be surprisingly efficient - read the excellent articles written by Paul White, linked in my sig.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden