与声明语句合并在一起的问题 [英] Problem in union all with declare statement
本文介绍了与声明语句合并在一起的问题的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
亲爱的所有人,
我正在使用UNION ALL的查询中进行工作.在此查询中,第5个子查询具有声明和设置语句,因为我遇到了错误.
那么,你能告诉我是什么问题吗?有什么解决方案吗??
这些是错误:
DEAR ALL,
I am working on a query in which I am using UNION ALL.In this query there is 5th Sub query has declare and set statement because of that I am getting error.
so, would you tell me what is the problem? is there any solution????
THESE ARE ERRORS:
Msg 156, Level 15, State 1, Line 92
Incorrect syntax near the keyword ''BEGIN''.
Msg 156, Level 15, State 1, Line 120
Incorrect syntax near the keyword ''UNION''
SELECT t2.InvitedByID AS Id,
t1.FirstName + '' '' + t1.LastName AS JoinedMember,
'''' AS Title,
t1.ProfilePhotograph AS PhotoGraph,
t1.DateOfJoining AS DateTime,
''InvitedbyfriendsANDAccepted''AS FeatureName,
'''' AS cnt
FROM uvw_UserCompleteInformation t1 INNER JOIN tblInvitedUsers t2
ON t1.UserID=t2.UserID
WHERE t1.UserId IN(
--This Query is for all "invited user" invited by your friends AND ,member in common netwok
SELECT UserId
FROM tblInvitedUsers
WHERE InvitedByID = 217041685
)-- ORDER BY Col5 DESC
UNION ALL
---------------------------------------------------------------------------------------------------------------------
-- 4. MEMBER GET BAZAAR RESPONSE
--------------------------------
SELECT cast(OfferId as nvarchar(20)) AS Id,
PostedTOFirstName +'' ''+ PostedTolastName AS ResponsePostedTo,
PostedByFirstName + '''' + PostedByLastName AS ResponsePostedBy,
ImagePath AS PhotoGraph,
PostedDateTime AS DateTime,
''BazaarOfferResponse'' AS FeatureName,
'''' AS cnt
FROM uvw_BazaarOfferResponses
WHERE PostedToID=217041685--) --as t where [DateTime]>''2011-03-01 12:44:13.027'' ORDER BY dateTime DESC
UNION ALL
---------------------------------------------------------------------------------------------------------------------
-- 5. WHEN ORGANIZATION INVITES MEMBER TO JOIN ITS NETWORK, AND NETWORK INVITATION GET ACCEPTED
--------------------------------------------------------------------------------------
BEGIN
DECLARE @IsNetwork BIT
SET @IsNetwork = (SELECT IsActive FROM tblNetworkMaster WHERE FK_UserID=217041685)
IF @IsNetwork = 1
BEGIN
SELECT t1.PK_NetWorkMemberID AS ID,
t2.FirstName + t2.LastName As InvitationAcceptedUser,
(SELECT t2.FirstName + t2.LastName AS InvitationSentUser
FROM tblNetworkmaster t1
INNER JOIN tblPersonalInformation t2
ON t1.fk_UserID = t2.UserID
WHERE t1.pk_networkid= fk_networkid) AS InvitationSentUser,
t2.ProfilePhotograph AS PhotoGraph,
t1.ApproveDateTime AS DateTime,
''NetworkInvitationgetaccepted'' AS FeatureName,
'''' AS cnt
FROM tblNetworkMembers t1 INNER JOIN tblPersonalInformation t2
ON t1.FK_UserID = t2.UserID
WHERE FK_NetWorkId IN (SELECT PK_NetWorkId
FROM tblNetworkMaster
WHERE FK_UserID = 217041685
)AND t1.IsApproved=1 AND t1.IsOwner = 0 order by t1.ApproveDateTime
END
ELSE
BEGIN
PRINT ''Network not present''
END
END
UNION ALL
---------------------------------------------------------------------------------------------------------------------
-- 6. WHEN ORGANIZATION RECIEVES INVITATION TO JOIN NETWORK
--------------------------------------------------------------------------------------
SELECT t1.PK_NetworkMemberID AS ID,
t2.FirstName + t2.LastName As InvitationSentBy,
'''' AS Title,
t2.ProfilePhotograph As PhotoGraph,
t1.RequestDateTime AS RequestedDateTime,
''Org n ReceivesInvitation'' AS FeatureName,
'''' AS cnt
FROM tblNetworkMembers t1 INNER JOIN tblPersonalInformation t2
ON t1.FK_UserId = t2.UserID<code></code>
WHERE t1.FK_UserId = 217041685 AND t1.IsApproved = 0
推荐答案
更改第5部分.UNION仅接受SELECT语句.
1.创建一个模仿第5节的函数
2.或像这样更改查询
Change section 5. UNION accepts only SELECT statements.
1. Create a function to mimic section 5
2. Or change query like this
---------------------------------------------------------------------------------------------------------------------
-- 5. WHEN ORGANIZATION INVITES MEMBER TO JOIN ITS NETWORK, AND NETWORK INVITATION GET ACCEPTED
--------------------------------------------------------------------------------------
SELECT t1.PK_NetWorkMemberID AS ID,
t2.FirstName + t2.LastName As InvitationAcceptedUser,
(SELECT t2.FirstName + t2.LastName AS InvitationSentUser
FROM tblNetworkmaster t1
JOIN tblPersonalInformation t2
ON t1.fk_UserID = t2.UserID
WHERE t1.pk_networkid= fk_networkid) AS InvitationSentUser,
t2.ProfilePhotograph AS PhotoGraph,
t1.ApproveDateTime AS DateTime,
'NetworkInvitationgetaccepted' AS FeatureName,
'' AS cnt
FROM tblNetworkMembers t1
INNER
JOIN tblPersonalInformation t2
ON t1.FK_UserID = t2.UserID
WHERE FK_NetWorkId IN (
SELECT PK_NetWorkId
FROM tblNetworkMaster
WHERE FK_UserID = 217041685
)
AND
t1.IsApproved=1
AND
t1.IsOwner = 0
-- <changed> from the @IsNetwork var
AND
EXISTS (
SELECT 1
FROM tblNetworkMaster
WHERE IsActive =1
AND
FK_UserID=217041685
)
--order by t1.ApproveDateTime
-- </changed>
这篇关于与声明语句合并在一起的问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文