Concatenating quandary!

Question

Post reply

Concatenating quandary!

bwillyerd

Right there with Babe

Points: 730
More actions
April 19, 2004 at 9:10 am

#108011

Hello,
I have 2 tables of data, an access profile and a navigation menu.
shortened AccessProfile tbl:
(EmpNo int;
s1 tinyint;
...
s300 tinyint)
shortened NavigationMenu tbl:
(PO_Value char(3);
...
SwitchNo smallint)
In the AccessProfile tbl the s1 - s300 fields are set 0 or 1. Yes, No. Simple enough eh. In the NavigationMenu table the SwitchNo relates back to the s1 - s300 fields. The s prefix was added as you can not have a field as numreic.

Rec from AccessProfile
EmpNo = 3567, S1 = 1, S2 = 0, S3 = 1, S4 = 1...
Rec from NavigationMenu
PO_Value = 'IA', SwitchNo = 1
PO_Value = 'CC', SwitchNo = 2
PO_Value = 'BC', SwitchNo = 3
....

I need to get the Sn value for an EmpNo based on the PO_Value from the NavigationMenu.
I have tried:
DECLARE @SwitchNo varchar(5)
SET @SwitchNo = 'S' + CAST((SELECT SwitchNo FROM NavigationMenu WHERE PO_Value = 'IA') AS VARCHAR(3))
SELECT @SwitchNo FROM AccessProfile WHERE EmpNo = 3567
The return is not what I expected. In Query Analyzer I get:
(No Column Name)
S1

Can someone pleas show me where I have gone awry?
Thanks,
Bill

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

So what is the 'expected' result?

The example seems to return '1' - hence the 'S' + '1' produces 'S1'

/Kenneth

roberthamilton Ten Centuries Points: 1002 More actions · Answer 2

Bill

Using a dynamic SQL string should solve this.

Because you don't know the column name until you've done the lookup, you can't right a non-dynamic SQL statement. In your code, you're selecting the value of the @switchno variable, rather than the value in the column with that name.

declare @sqlstring varchar(255)

select @sqlstring = 'select ' + @switchno + ' from accessprofile where empno = 3567'

exec(@sqlstring)

Regards

Rob

bwillyerd Right there with Babe Points: 730 More actions · Answer 3

Thank you Kenneth for your response, and Rob for your solution. In an attempt to understand where I went wrong, why didn’t the very similar select statement not work until it was encapsulated within another variable to be executed?

Thanks for the solution, and other information too.

Bill

roberthamilton Ten Centuries Points: 1002 More actions · Answer 4

Bill

Your original code :

SELECT @SwitchNo FROM AccessProfile WHERE EmpNo = 3567

will always return the value 'S1', since this is what the @switchno variable is set to earlier. Incidentally, if you remove the WHERE clause, then your result will be 'S1' repeated as many times as you have rows in the table.

The point is that you're select a literal value ('S1') rather than the value in the column called S1 eg.

SELECT 'ROB' FROM AccessProfile WHERE EmpNo = 3567

will return the string 'ROB'

I hope this helps

Rob

bwillyerd Right there with Babe Points: 730 More actions · Answer 5

Yes it does. I walked away for a little bit and could see things better from the sidewalk. Kinda funny isn't it. But I can see your explanation and its effects.

Thanks

Bill

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

I don't like this. The use of dynamic sql is too easily advised. You should read http://www.sommarskog.se/dynamic_sql.html to see if that is the way you want to go.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

roberthamilton Ten Centuries Points: 1002 More actions · Answer 7

I agree that dynamic SQL has its problems, and is something I would always try to avoid, but the alternative in this situation of 300 CASE clauses seems a bit excessive.

I'd be more likely to re-design the underlying tables, but perhaps in this case that isn't an option.

roberthamilton Ten Centuries Points: 1002 More actions · Answer 8

It occurred that a re-designed example might be useful.

Change the AccessProfile table to :

create table AccessProfile (

empno int,

switchno smallint,

switchvalue tinyint )

Sample data :

empno switchno switchvalue

----- -------- -----------

123 1 0

123 2 1

123 3 1

:

123 299 1

123 300 0

You have the choice of storing 300 rows per user, or storing only those values set to '1', and then using isnull() and an outer join to find out the '0' values. This will depend on your requirements.

Your query can now be expressed as a straightforward join eg :

SELECT

a.switchvalue

FROM

AccessProfile as a

join NavigationMenu as b on b.switchno = a.switchno

WHERE

a.empno = 123 and

b.po_value = 'IA'

or, if you're only storing the '1' values :

SELECT

isnull(b.switchvalue, 0)

FROM

NavigationMenu as a

left join AccessProfile as b on b.switchno = a.switchno and b.empno = 123

WHERE

a.po_value = 'IA'

Hope this is helpful

Regards

Rob