A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

[Database Administrator]

Hi Folks,

I'm trying to pull data from multiple tables into one report, where
case_id is the only constant. only I've run into a few problems, I
need to use a variable to create a CSV string and call the results of
the variable with other columns form other tables.

Here is the syntax.
--------------------------------------------------------------------------------------------------------------------------
DECLARE
@ClassList char(134)

SELECT @ClassList = COALESCE(@ClassList + ',', '') +
CAST(CLASS.CLASS AS varchar(2))
FROM CLASS
WHERE CLASS.CASE_ID = LOG_JOB_DETAILS.CASE_ID

SELECT @ClassList,
CLASS.CLASS,
LOG_JOB_DETAILS.RENEWAL_JOB_ID,
LOG_JOB_DETAILS.CASE_ID
FROM LOG_JOB_DETAILS ,
LOG_JOB,
CLASS
WHERE ( LOG_JOB.JOB_ID = LOG_JOB_DETAILS.JOB_ID )
AND ( CLASS.CASE_ID = LOG_JOB_DETAILS.CASE_ID )
---------------------------------------------------------------------------------------------------------------------------
The above syntax produces an error
"A SELECT statement that assigns a value to a variable must not be
combined with data-retrieval operations."

I know from similar posts that this syntax is incorrect, but I can't
work out how to link the variable's where clause to the data
retrieval's where clause and how to pull the results from the variable
into a data retrieval select statement

I'd be extremely grateful for someone to point out my flaws and steer
me in the right direction.

Please?
Thanks in advance
Dan

[Database Administrator]

On Jul 25, 9:59 am, Dan Bridgland wrote:
> Hi Folks,
>
> I'm trying to pull data from multiple tables into one report, where
> case_id is the only constant. only I've run into a few problems, I
> need to use a variable to create a CSV string and call the results of
> the variable with other columns form other tables.
>
> Here is the syntax.
> --------------------------------------------------------------------------------------------------------------------------
> DECLARE
> @ClassList char(134)
>
> SELECT @ClassList = COALESCE(@ClassList + ',', '') +
> CAST(CLASS.CLASS AS varchar(2))
> FROM CLASS
> WHERE CLASS.CASE_ID = LOG_JOB_DETAILS.CASE_ID

> ---------------------------------------------------------------------------------------------------------------------------
http://milambda.blogspot.com/2005/07...-as-array.html
http://milambda.blogspot.com/2006/07...-sql-2005.html

[Database Administrator]

You are using an unsupported syntax. For some obvious failures, see:
http://tinyurl.com/dpdej
http://tinyurl.com/apuns

For some alternatives, see: www.projectdmx.com/tsql/rowconcatenate.aspx

--
Anith

[Database Administrator]

Thanks Ross and Anith, But I'm not all to familar with this kind of
advanced sql, I can follow basic instructions but joining one feature
to another has caught me out this time. I need to see where i'm going
wrong. can anyone point me in the right direction?

Dan

[Database Administrator]

>> I need to see where i'm going wrong. can anyone point me in the right
>> direction?

You are wrong in using the following statement:

SELECT @ClassList = COALESCE(@ClassList + ',', '') +
CAST(CLASS.CLASS AS varchar(2))
FROM CLASS
WHERE CLASS.CASE_ID = LOG_JOB_DETAILS.CASE_ID

You seem to be missing the table LOG_JOB_DETAILS in your from clause. The
other problem is the use of aggregate concatenation that is addressed in the
previous posts.

--
Anith

[shijobaby]

Hi

Actually this in the group of small errors consuming time

Just have aook on my blog

http://sqlerrormessages.blogspot.com...t-assigns.html


Happy Programming

[tsqldeveloper]

Hello all,

Here is a sample which utilizes such a t-sql select method.
What is the main difference in here is the aggregated variable type is nvarchar(max) not a limited char type.


DECLARE @ClassList nvarchar(max)

SELECT @ClassList = COALESCE(@ClassList + ',', '') + name
FROM Humanresources.Department
order by DepartmentID

select @ClassList


I hope this helps,
Eralper