Beginners Question ! local vars and CASE

[KCcasey]

Hi all,

I have question (surprise!). I am trying to populate a local variable with concatenated strings based on a comparison. Logically what I want to do is something like


if table1.field1 is like 'x' THEN (@localVar = 'yyy '),
if table1.field2 is like 'x' THEN (@localVar = (@localVar + 'yyy ')),


The best effort I could make was something like:


SELECT
@notesString =
(CASE WHEN tblExtra.Extraheader='4wd' THEN (@notesString ='4WD ')END)
(CASE WHEN tblExtra.Extraheader='Aachk' THEN (@notesString = (@notesString + 'aa '))END)
,
...

And I'm getting this error:

Server: Msg 141, Level 15, State 1, Procedure casey_qryCROSSTAB_emulator_30-10-2003, Line 7
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

I'm very new to T-SQL and not familiar with its limitations, any help offered would be gratefully recieved!

Thanks in advance for your valuable time!

KC.

 

[SonOfEmidec1100]

Not sure if this exactly what you want but maybe it will help get you going.

SELECT @notesstring =
CASE tblExtra.Extraheader
WHEN '4wd' THEN '4WD '
WHEN 'Aachk' THEN tblExtra.Extraheader + 'aa '
ELSE tblExtra.Extraheader
END,

 

[KCcasey]

Thnaks for your input SonOfEmidec1100,

what I'm after is something like:

SELECT @notesString =
CASE tblExtra.Extraheader
WHEN '4wd' THEN (@notesString = '4WD ')
WHEN 'Aachk' THEN (@notesString (@notesString + 'aa '))
END

The syntax checker is returning Incorrect syntax near '=', so I'm not sure if CASE is usable in this way.

Any further pointers about this would be great!!

KC.

 

[nigelrivett]

Close

SELECT @notesString =
CASE tblExtra.Extraheader
WHEN '4wd' THEN '4WD '
WHEN 'Aachk' THEN @notesString + 'aa '
ELSE @notesString
END

I assume you need an else otherwise it would end up as null if Extraheader was neither of those values.
The case statement just returns one of a selection of values - you cannot include any commands within it.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[KCcasey]

Thanks for your reply!

Hmmmm. Perhaps the best way to get help with this problem is to describe exactly what I want to do. I need to test each of a number of fields for having a particular value and output a string based on this.

eg. Say a vehicle is a 4WD (4wd) and had Air Conditioning (ac)

populate a local variable or a field with a concatenated string
eg

@notes / notes= '4wd ac'

I've got no idea how to do this, other than using a sub query to return the results the field I want.

What I have at the moment is:

notes =
(
SELECT
CASE WHEN tblExtra.ExtraHeader = '4WD' THEN '4WD ' END,
CASE WHEN tblExtra.ExtraHeader = 'Aachk' THEN 'aa ' END,
CASE WHEN tblExtra.ExtraHeader = 'Aircon' OR tblExtra.ExtraHeader = 'DualAircon' OR tblExtra.ExtraHeader = 'ClimateAirCon' THEN 'ac ' END,
CASE WHEN tblExtra.ExtraHeader = 'ABS' THEN 'abs ' END,
CASE WHEN tblExtra.ExtraHeader = 'AlloyWheel' OR tblExtra.ExtraHeader = 'FactoryAlloyWheel ' THEN 'aw ' END,
CASE WHEN tblExtra.ExtraHeader = 'SRSAirBag' OR tblExtra.ExtraHeader = 'DualSRSAirBag' THEN 'ab ' END,
CASE WHEN tblExtra.ExtraHeader = 'CD' OR tblExtra.ExtraHeader = 'CDShuttle' THEN 'cd ' END

FROM tblExtra
INNER JOIN tblStockExtra ON tblExtra.ExtraID = tblStockExtra.ExtraID
INNER JOIN tblStock ON tblStockExtra.StockID = tblStock.StockID
)
...
]
Only this is returning the following error message:

Server: Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.


This is really grinding me down! Am I attacking this in the right manner? Is there a better way to solve this problem?

Many Thanks,

KC.

 

[nigelrivett]

It's because you are trying ot put multiple values into a single string. You need to concatenate them.
Actually do you have a single row here or multiple rows.
You should put the translations (i.e. 'Aachk - aa, Aircon - ac, ...) into a table or table variable so that you can join to it rather than coding these in the statement.
then

select @notes = coalesce(@notes + ', ','') + #a.value
FROM tblExtra
INNER JOIN tblStockExtra ON tblExtra.ExtraID = tblStockExtra.ExtraID
INNER JOIN tblStock ON tblStockExtra.StockID = tblStock.StockID
inner join #a
on #a.description = tblExtra.ExtraHeader

This will concatenate all the values together so you get a string

'aa, ac, ...'


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[JEFFREYCRUZANA]

SELECT Extraheader =
CASE tblExtra.Extraheader
WHEN '4wd' THEN '4WD'
WHEN 'Aachk' THEN @notesString + 'aa '
END
FROM tblExtra

 

[KCcasey]

Hi guys,

Jeff: Thanks for your input, however that solution doesn't work as there can potentially be many true conditions for the CASE statement you posed.

Nigel: :
If I understand "Actually do you have a single row here or multiple rows." correctly, you're asking whether there are mutiple '@notes'; Yes, each stock record needs its own collection of 'notes'

Heres a mock table structure and sample data.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblExtra]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblExtra]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblStock]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblStock]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblStockExtra]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblStockExtra]
GO

CREATE TABLE [dbo].[tblExtra] (
[ExtraID] [int] NOT NULL ,
[ExtraDesc] [nvarchar] (255) NULL ,
[ExtraAbbrv] [nvarchar] (255) NULL ,
[ExtraHeader] [nvarchar] (50) NULL ,
[Group] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblStock] (
[StockID] [int] NOT NULL ,
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblStockExtra] (
[StockExtraID] [int] IDENTITY (1, 1) NOT NULL ,
[StockID] [int] NULL ,
[ExtraID] [int] NULL
) ON [PRIMARY]
SET IDENTITY_INSERT [dbo].[tblStockExtra] ON
GO

ALTER TABLE [dbo].[tblStockExtra] WITH NOCHECK ADD
CONSTRAINT [PK_tblStockExtra] PRIMARY KEY NONCLUSTERED
(
[StockExtraID]
) ON [PRIMARY]
GO

INSERT INTO tblStock
(StockID)
VALUES (64);
INSERT
INTO tblStock(StockID)
VALUES (65);
INSERT
INTO tblStock(StockID)
VALUES (467);
INSERT
INTO tblStock(StockID)
VALUES (487);
INSERT
INTO tblStock(StockID)
VALUES (944);

--4wd
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (41,'4 wheel drive', '4WD', '4WD', 14)

--aachk
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (52, 'AA check', 'AA', 'Aachk', 17)

--aircon
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (8, 'air-conditioning', 'AC', 'AirCon', 2)
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (9, 'climate air-conditioning', 'AAC', 'ClimateAirCon', 2)
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (10, 'dual air condtioning', 'DAC', 'DualAirCon', 2)

--abs
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (39, 'antilock braking system', 'ABS', 'ABS', 12)

--alloys
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (27, 'alloy wheels', 'AW', 'AlloyWheel', 9)
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (28, 'factory alloy wheels', 'FAW', 'FactoryAlloyWheel', 9)

--airbags
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (25, 'SRS airbag', 'AB', 'SRSAirBag', 8)
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (26, 'dual SRS airbag', 'DAB', 'DualSRSAirBag', 8)

--CD
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (13, 'CD', 'CD', 'CD', 3)
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (14, 'CD shuttle', 'CDS', 'CDShuttle', 3)

INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1112,64,8)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1113,64,12)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1115,64,23)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1116,64,15)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1117,64,60)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1118,64,7)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1118,64,59)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1119,65,8)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1120,65,39)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1121,65,14)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1122,65,23)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1123,65,26)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1124,65,16)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1125,65,15)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1126,65,43)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1127,65,7)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1128,65,12)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (4357,467,7)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (4358,467,8)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (4359,467,11)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (4465,487,7)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (4466,487,8)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (4467,487,11)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (4468,487,67)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9188,944,7)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9189,944,8)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9190,944,12)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9191,944,16)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9192,944,15)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9193,944,20)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9194,944,23)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9195,944,28)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9196,944,64)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9197,944,41)

Thanks Again for your time, one and all!

Sincerely(!)

KC.