Click here to Skip to main content
15,902,777 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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
Posted
Updated 28-Mar-11 1:02am
v2

1 solution

Change section 5. UNION accepts only SELECT statements.

1. Create a function to mimic section 5

2. Or change query like this
SQL
---------------------------------------------------------------------------------------------------------------------
-- 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>
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900