problem in union all

Posted by Navalemanoj0405 under Sql Server on 3/28/2011 | Points: 10 | Views : 1853 | Status : [Member] | Replies : 1
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
WHERE t1.FK_UserId = 217041685 AND t1.IsApproved = 0




Responses

Posted by: Dhirendra on: 8/11/2011 [Member] Starter | Points: 25

Up
0
Down
you canont use begin end statement within union clause. Use case statement if you dont want to return results from 5th sub query and use 1=2 in where clause.

just like pscudo code

check your condition at the top and keep variable
1 union
second union
5 union
use case statement and if the condition is true then return records and if condition is false then use 1=2 in where clause..

Navalemanoj0405, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response