January 16, 2008 at 9:45 am
When I try to run the following code I get an error message:
" Server: Msg 8122, Level 16, State 1, Only the first query in a UNION statement can have a SELECT with an assignment"
PROCEDURE [dbo].[KB_Search] @userid varchar(30), @groups varchar(250),
@opco varchar(50), @SearchText varchar(8000),
@TYPE varchar(50) output, @TITLE varchar(30) output,
@BRIEF varchar(50) output,@RANK varchar(10) output AS
--BEGIN
DECLARE @SelStatement varchar(8000)
DECLARE @SearchWord2 varchar(255)
DECLARE @SEARCHWORD varchar(255)
SET @SEARCHWORD = RTRIM(ltrim(@SearchText))
------------------------------------------------------------------------------
---- this section will is for sending back data to search screen
------------------------------------------------------------------------------
BEGIN
-- this will select on the Key words FTS field
SELECT @TYPE = [T334].C777700001, @TITLE =[T334].C536870929,
@BRIEF = [T334].C777700100, @RANK= K.[RANK]
FROM ARSystem.dbo.T334 [T334], ARSystem.dbo.T338 [T338],
FREETEXTTABLE(ARSystem.dbo.T334, C536870929, @SEARCHWORD) K
WHERE [T334].C1 = K.
and [T334].C1 = [T338].C1
and (@opco = T338.C536870913 or @groups = T338.C536870913)
UNION ALL
-- this will select on the Type field
SELECT @TYPE = [T334].C777700001, @TITLE =[T334].C536870929,
@BRIEF = [T334].C777700100, @RANK= Y.[RANK]
FROM ARSystem.dbo.T334 [T334], ARSystem.dbo.T338 [T338],
FREETEXTTABLE(ARSystem.dbo.T334, C777700001, @SEARCHWORD) Y
WHERE [T334].C1 = Y.
and (@opco = T338.C536870913 or @groups = T338.C536870913)
UNION ALL
-- this will select on the brief description field
SELECT @TYPE = [T334].C777700001, @TITLE =[T334].C536870929,
@BRIEF = [T334].C777700100, @RANK= B.[RANK]
FROM ARSystem.dbo.T334 [T334], ARSystem.dbo.T338 [T338],
FREETEXTTABLE(ARSystem.dbo.T334, C777700003, @SEARCHWORD) B
WHERE [T334].C1 = B.
and (@opco = T338.C536870913 or @groups = T338.C536870913)
END
When I have only one select it works fine. This error message does not show up to frequently on the web so can anybody seen this or knows how to solve it I would appriecate your help. Thanks, Jerry
January 16, 2008 at 11:07 am
Hello,
I would encapsulate the union to a table.
The issue is that the variable can only have one value, and sql server can't decide if the values comes from the second,... select and what value it should have when there are multiple possible values (select2,select3)
SELECT @..=..,@...=..
FROM (
SELECT ...
FROM TABLE1
WHERE ...
UNION ALL
SELECT ...
FROM TABLE2
WHERE ...
UNION ALL
...
) AS MyUnionQuery
January 16, 2008 at 8:43 pm
The error occurs because a variable can contain only one value... the unions return more than 1 value.
The option of using a table variable is good.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 17, 2008 at 1:22 pm
I think that is going to be the only way to get this done so I code it so insert into a table and
and get the following error
Incorrect syntax near the keyword 'SELECT'.
BEGIN
DECLARE @SelStatement varchar(8000)
DECLARE @SearchWord2 varchar(255)
DECLARE @SEARCHWORD varchar(255)
DECLARE @Timeran varchar(6)
DECLARE @TimeranH varchar(2)
DECLARE @TimeranM varchar(2)
DECLARE @TimeranS varchar(2)
DECLARE @TimeranN varchar(3)
DECLARE @user1 varchar(50)
-- ***** for testing in SQL comment out when running in TSQL *******
Declare @searchtext varchar(50)
DECLARE @opco varchar(50)
DECLARE @groups varchar(250)
Declare @userid varchar(50)
set @opco = 'us foodservice' --@opco
set @groups = 'us foodservice' --@groups
set @searchText = 'laptop'
SET @SEARCHWORD = RTRIM(ltrim(@SearchText))
SET @Timeran = datepart(Hh, current_timestamp)+datepart(m, current_timestamp)+datepart(s, current_timestamp)
set @user1 = Rtrim(Ltrim(@userid))
SET @TimeranH = datepart(Hh, current_timestamp)
SET @TimeranM = datepart(mi, current_timestamp)
SET @TimeranS = datepart(s, current_timestamp)
SET @TimeranN = datepart(ms, current_timestamp)
CREATE TABLE #ALLQ
(
id varchar (500),
user_id varchar (10),
title varchar (100),
rank int,
)
insert into #ALLQ
(
SELECT 'T'+ T334.C2 + RIGHT(T334.C1,7) -- error message point to here
+ @TimeranH +@TimeranM +@TimeranS +@TimeranN,
@user1,C536870929, T.[RANK]
FROM ARSystem.dbo.T334 [T334], ARSystem.dbo.T338 [T338],
FREETEXTTABLE(ARSystem.dbo.T334, C536870929, @SEARCHWORD) T
WHERE [T334].C1 = T.
and [T334].C1 = [T338].C1
and (@opco = T338.C536870913 or @groups = T338.C536870913)
)
January 17, 2008 at 2:08 pm
insert into #ALLQ
select * --below will be treated as a "table"
from
(
SELECT 'T'+ T334.C2 + RIGHT(T334.C1,7) -- error message point to here
+ @TimeranH +@TimeranM +@TimeranS +@TimeranN,
@user1,C536870929, T.[RANK]
FROM ARSystem.dbo.T334 [T334], ARSystem.dbo.T338 [T338],
FREETEXTTABLE(ARSystem.dbo.T334, C536870929, @SEARCHWORD) T
WHERE [T334].C1 = T.
and [T334].C1 = [T338].C1
and (@opco = T338.C536870913 or @groups = T338.C536870913)
) as MYSELECTION --necessary to give it a name
January 17, 2008 at 2:29 pm
You don't need parentheses around the select in the insert statement at the end of your script.
insert into #ALLQ
SELECT 'T'+ T334.C2 + RIGHT(T334.C1,7) -- error message point to here
+ @TimeranH +@TimeranM +@TimeranS +@TimeranN,
@user1,C536870929, T.[RANK]
FROM ARSystem.dbo.T334 [T334], ARSystem.dbo.T338 [T338],
FREETEXTTABLE(ARSystem.dbo.T334, C536870929, @SEARCHWORD) T
WHERE [T334].C1 = T.
and [T334].C1 = [T338].C1
and (@opco = T338.C536870913 or @groups = T338.C536870913)
I would also recommend explicity listing the columns to insert into:
insert into #ALLQ (id, user_id, title, rank)
SELECT 'T'+ T334.C2 + RIGHT(T334.C1,7) -- error message point to here
+ @TimeranH +@TimeranM +@TimeranS +@TimeranN,
@user1,C536870929, T.[RANK]
FROM ARSystem.dbo.T334 [T334], ARSystem.dbo.T338 [T338],
FREETEXTTABLE(ARSystem.dbo.T334, C536870929, @SEARCHWORD) T
WHERE [T334].C1 = T.
and [T334].C1 = [T338].C1
and (@opco = T338.C536870913 or @groups = T338.C536870913)
But that's not necessary, it's just good form and helps in debugging. The syntax error was because of the parentheses.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply