与声明语句合并在一起的问题 [英] 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屋！