CODEWITHSUNDEEP
sql-server-2008
user2084564
user2084564

Reputation:

Calling a function(on other db) from a view

Hi I am trying to execute the following query, with no luck:

SELECT AA.Id, AA.ObjId, AA.NsId, AA.statusId, AA.LCID, AA.Title, AA.MessageBody_ClearText, AA.MessageBody_HTMLText, 
               AA.File1, AA.File2, AA.File3, AA.Status, AA.name, AA.createdate, AA.updatedate, AA.boneid, AA.main, AA.Sort, 
               BB.ID AS Expr1, BB.MainIDNum, BB.MessageID, BB.Message1, BB.Message2, BB.Message3, BB.MessageDate, 
               BB.AttachmentGUID1, BB.AttachmentGUID2, CC.MessageID AS Expr2, CC.Identification, CC.Viewed, CC.Deleted, CC.Id AS Expr3, 
               AA.FileName1, AA.FileName2, AA.FileName3
FROM  dbo.gnvw_ObjectsBones AS AA INNER JOIN
              --- XXX.dbo.MESSAGES AS BB ON BB.MessageID = AA.ObjId LEFT OUTER JOIN ---
               dbo.PersonalMessages AS CC ON CC.MessageID = BB.MessageID AND CC.Identification = BB.MainIDNum

The marked line need to be change to a function call, I have a function called 'GetMessages' that takes an int 'input' and it''s has to be that one: 'CC.Identification', so I tried to replace it to be like this:

SELECT AA.Id, AA.ObjId, AA.NsId, AA.statusId, AA.LCID, AA.Title, AA.MessageBody_ClearText, AA.MessageBody_HTMLText, 
               AA.File1, AA.File2, AA.File3, AA.Status, AA.name, AA.createdate, AA.updatedate, AA.boneid, AA.main, AA.Sort, 
               BB.ID AS Expr1, BB.MainIDNum, BB.MessageID, BB.Message1, BB.Message2, BB.Message3, BB.MessageDate, 
               BB.AttachmentGUID1, BB.AttachmentGUID2, CC.MessageID AS Expr2, CC.Identification, CC.Viewed, CC.Deleted, CC.Id AS Expr3, 
               AA.FileName1, AA.FileName2, AA.FileName3
FROM  dbo.gnvw_Data_PersonalMessages_ObjectsBones AS AA INNER JOIN
               XXX.dbo.GetMessages(CC.Identification) AS BB ON BB.MessageID = AA.ObjId LEFT OUTER JOIN
               dbo.PersonalMessages AS CC ON CC.MessageID = BB.MessageID AND CC.Identification = BB.MainIDNum

I also tried other solutions but none of them worked, Does somebody here see the problem? This is the errors that i get from running that 'solution':

'The multi-part identifier "CC.Identification" could not be bound.'

and,

'Invalid column name 'ID'.' SQL is not my strong side, I'll be happy to hear any solutions, Hope I explained it right.

Upvotes: 0

Views: 79

Answers (1)

a1ex07
a1ex07

Reputation: 37374

When you need to join table-valued function , use CROSS APPLY/OUTER APPLY instead of INNER/LEFT joins respectively. 

...
FROM  dbo.gnvw_Data_PersonalMessages_ObjectsBones AS AA 

CROSS APPLY XXX.dbo.GetMessages(CC.Identification) AS BB
....
WHERE BB.MessageID = AA.ObjID

Update I just noticed that join condition for dbo.AgamUsersPersonalMessages AS CC refers only to BB, and join condition for BB depends only on CC. That's incorrect, and you will still get the error. Without knowing what you want to get, I cannot give you a solution; you need to figure out join conditions yourself...

Upvotes: 1

Related Questions