Stored procedure for a select statement

Question

I got a select statement. I want to add a join to this select statement and the join which I want to add is at the bottom of this code. why am I unable to add one more left outer join for this select statement? The join which I want to add is at the bottom. I also need to write a stored procedure for the entire select statement:

SELECT
    FactId, UserType,
    wr.WorkRequestId, wr.XerisUserKey, 
    xu.CsuserUserID UserId, 
    u.fname UserFName, u.lname UserLName,
    b.PatientId, p.firstname PatFName, p.lastname PatLName,
    GroupId, HospiceGroupKey GroupKey, WR.ContactKey,
    C.ContactId, C.FirstName, C.LastName,
    Convert(datetime, (Convert(varchar, SD.Date, 101) + ' ' + ST.TimeOfDay ))  Start_dtm,
    Convert(datetime, (Convert(varchar, CD.Date, 101) + ' ' + CT.TimeOfDay ))  End_dtm,
    DATEDIFF(s,Convert(datetime,(Convert(varchar, SD.Date, 101) + ' ' + ST.TimeOfDay)),
    Convert(datetime, (Convert(varchar, CD.Date, 101) + ' ' + CT.TimeOfDay )))  WRDuration,
    (Convert(Decimal(18, 3), DATEDIFF(s, Convert(datetime,(Convert(varchar, SD.Date, 101) + ' ' + ST.TimeOfDay )),
    Convert(datetime, (Convert(varchar, CD.Date, 101) + ' ' + CT.TimeOfDay ))))) * 
                (Convert(Decimal(18,3),LineItemCount)/Convert(Decimal(18,3),PatientBucketItemCount)) Duration,
    CallBackNumber, WorkRequestType,
    B.LineItemCount, ArchiveLocation, Processed,
    ArchiveQueueType, TQA, Exclude, CallId
FROM 
    bi.dbo.FactWorkRequestTouches (NOlock) WR
INNER JOIN 
    bi.dbo.BridgePatientWorkRequest B ON B.WorkRequestId = WR.WorkRequestId
INNER JOIN 
    bi.dbo.dimPatient (NOlock) P ON B.PatientId = P.CphPatientID
INNER JOIN 
    bi.dbo.DimXerisUsers (NOlock) XU ON WR.XerisUserKey = XU.XerisUserKey
INNER JOIN 
    cdc.dbo.csuser (NOlock) U ON XU.CsuserUserID = u.user_id
INNER JOIN 
    bi.dbo.DimTimeOfDay (NOlock) ST ON WR.StartTimeOfDayKey = ST.TimeKey
INNER JOIN 
    bi.dbo.DimTimeOfDay (NOlock) CT ON WR.CompletedTimeOfDayKey = CT.TimeKey
INNER JOIN 
    bi.dbo.DimDate (NOlock) SD ON WR.StartDateKey = SD.DateKey
INNER JOIN 
    bi.dbo.DimDate (NOlock) CD ON WR.CompletedDateKey = CD.DateKey
LEFT OUTER JOIN 
    bi.dbo.DimContact (Nolock) C ON WR.ContactKey = C.ContactKey
WHERE 
    CompletedDateKey = '20140131'
    AND ArchiveQueueType = 0
    AND PatientBucketItemCount <> 0
    AND Exclude = 0
    AND P.ENDDate is Null

This is the join I want to add to this select statement

left outer join 
    ssdba.excelleRx_WebFOCUS.dbo.DimHospiceHiearchy (nolock) h on b.groupid = h.group_id

Edit your post to show us what the query looks like when you add the `JOIN` and tell us what error you get. — RBarryYoung, Mar 01 '14 at 20:36
I am getting an error Msg 4122, Level 16, State 1, Line 34 Remote table-valued function calls are not allowed. — user3369060, Mar 01 '14 at 20:44
Msg 4122, Level 16, State 1, Line 34 Remote table-valued function calls are not allowed. — user3369060, Mar 01 '14 at 20:45
@RBarryYoung The join which I added is at the bottom and I added it before where statement — user3369060, Mar 01 '14 at 20:48

score 0 · Answer 1 · edited May 23 '17 at 10:32

0

You are not allowed to call a table valued function remotely (ie across servers).

There is a workaround here: Workaround for calling table-valued function remotely in SQL Server has even more issues

And more info here:

http://social.msdn.microsoft.com/Forums/en-US/1f0d2885-faa2-496a-b010-edc441260138/remote-tablevalued-function-calls-are-not-allowed?forum=transactsql

edited May 23 '17 at 10:32

Community

1
1

answered Mar 03 '14 at 11:40

Julian Joseph

174
6

Actually there is error with nolock. Got solution. Thanks for spending time to help – user3369060 Mar 03 '14 at 16:15

Stored procedure for a select statement

1 Answers1